We've got a daily task scheduled in Microsoft Task Scheduler on our main data warehouse server, which runs an old VB.script ETL once every morning.
As part of our current server migration, I've got to move this task to the replacement server. The current task runs using a service account and is set up to run regardless whether a user is logged into the server or not - you don't want the task requiring someone to be logged on!
When setting up a new service account user on the new server to run the task, I came across the following notification:
When I tried scheduling the job to run on the server with no one logged in, the task failed with the following error:
The reason for this error is that the user / service account that I am using to run the task requires 'Log on as batch job' permissions.
1. To resolve this issue, go to Administrative Tools > Local Security Policy snap-in.
2. Open the Local Policies tree under Security Settings.
3. Under the User Rights Assignment tree, right click on the Log on as a batch job policy:
4. Add the user/group that you wish to provide the Log on as a batch job permissions to.
Problem solved!
The main reporting application in my business was always intended to serve it's data under the security of an SSL certificate. Unfortunately, whilst the business has purchased some quite pricey SSL certificates and configured them on all of our web servers, no one has ever taken the time to configure IIS correctly so that the use of the SSL connection is actually enforced! Thus users within our network can currently access the reporting application via HTTP unobstructed.
Determined to right this error, I have looked into how best to configure our IIS7 web servers to redirect all traffic from HTTP to HTTPS for our core reporting web application.
The simplest method that I came across was to use the Microsoft URL Rewrite Module to do all the heavy lifting:
1. First off, you will need to install the Microsoft URL Rewrite Module which is an extension for IIS7:
http://www.iis.net/downloads/microsoft/url-rewrite
2. Once this is done, all you need to do is to add the following rewrite rule to the <system.webServer> portion of your web applications web.config file:
<rewrite>
<rules>
<rule name="HTTP to HTTPS redirect" stopProcessing="true">
<match url="(.*)" />
<conditions>
<add input="{HTTPS}" pattern="off" ignoreCase="true" />
</conditions>
<action type="Redirect" redirectType="Found" url="https://{HTTP_HOST}/{URL}" />
</rule>
</rules>
</rewrite>
3. Make sure that the Require SSL setting in IIS is NOT checked under SSL Settings for the website:
Job done!
I am in the middle of migrating our data warehouse to a new server and came across this issue when migrating one of our existing SQL jobs which had been setup by our DBA (who has since left the company).
The SQL job was a simple maintenance script which ran weekly and deleted old archived CSV files from a specified folder which were over a certain age.
The script was throwing the following error:
SQL Server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'xp_cmdshell' by using sp_configure. For more information about enabling 'xp_cmdshell', search for 'xp_cmdshell' in SQL Server Books Online.
xp_cmdshell is a server configuration option introduced in SQL Server 2005 which enables system administrators to control wehter the xp_cmdshell extended stored procedure can be executed on a system. By default, it is disabled on new installations but can be enabled by running the sp_configue system stored procedure as shown in the script below:
-- To allow advanced options to be changed
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO
This has resolved the initial issue with the SQL job failing.
In addition, I had to provide SQL DB engine service account access to the archive folder where the CSV files were stored in order for the script to have sufficient access to delete the files within.
I have done some other brief research on the subject and it appears that enabling xp_cmdshell access is generally regarded as somewhat of a security risk, but only as far as access to system administrative privileges is a security risk. The risk can be mitigated by maintaining good security protocols and processes within the organisation.
