Thursday, 15 April 2010

Modify local security policy to grant service rights

Specific rights must be granted to user accounts to allow the account to be used to run as a service. There are a number of possible methods.
Method 1: Grant rights using Group Policy
This can be achieved using group policy: http://support.microsoft.com/kb/256345/
Method 2: Grant rights using Security templates
Similar to method 1 but using Security templates to change the permissions on system services.
   1. Click Start, click Run, and then type MMC.
   2. On the Console menu, click Add/Remove Snap-in.
   3. Click Add.
   4. Select the Security Configuration and Analysis snap-in, and then click Add.
   5. Click Close, and then click OK.
   6. In the MMC, right-click the Security Configuration and Analysis item, and then click Open Database.
   7. Give a name for the database, and then browse to where you would like to store it.
   8. When prompted, select a Security Template to import. For example, the "basicwk.inf" contains values for the standard settings found on a Windows 2000 Professional computer.
   9. In the MMC, right-click the Security Configuration and Analysis item, and then click the Analyze Computer now option. Choose a location for the log file, when prompted.
  10. After analysis is complete, configure the service permissions as follows:
         1. Double-click the System Services branch in the MMC.
         2. Right-click the service that you want to change, and then click Security.
         3. Click Edit Security.
         4. Add user accounts as required, and configure the permissions for each account. By default, the user will be granted "Start, stop and pause" permissions.
  11. To apply the new settings to the local computer, simply right-click the Security Configuration and Analysis item, and then click the Configure Computer Now option.
Method 3: Grant rights using Subinacl.exe
The final method for assigning rights to manage services is to use the Subinacl.exe utility from the Windows 2000 Resource Kit. The syntax for this is:
SUBINACL /SERVICE \\MachineName\ServiceName /GRANT=[DomainName\]UserName[=Access]
Full details from Microsoft:
http://support.microsoft.com/?kbid=288129

Wednesday, 14 April 2010

SQL 2005/2008 Ad hoc access to OLE DB provider has been denied

I have a previous post on enabling adhoc access for SQL 2000 (http://blog.bansheetechnologies.co.uk/2009/02/ad-hoc-access-to-ole-db-provider-has.html), SQL 2005 and 2008 are a little different.
SQL 2005/2008 returns the following:
SQL Server blocked access to STATEMENT 'OpenRowset/OpenDatasource' of component 'Ad Hoc Distributed Queries' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Ad Hoc Distributed Queries' by using sp_configure. For more information about enabling 'Ad Hoc Distributed Queries', see "Surface Area Configuration" in SQL Server Books Online.
To enable OpenRowset and OpenDatasource:
  • Launch the "SQL Server 2005 Surface Area Configuration" tool.
  • Click "Surface Area Configuration for Features"
  • Select "Server Name > Database Engine > Ad Hox Remote Queries"
  • Tick "Enable OPENROWSET and OPENDATASOURCE" support.
  • Click OK

After completing the above steps you might see the following error when you run the query again:

Ad hoc access to OLE DB provider 'MSDASQL' has been denied. You must access this provider through a linked server.

To resolve this:

  • Open regedit
  • Expand "HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL.1\Providers" – depending on how many instances you have installed you may have MSSQL.1, MSSQL.2 etc – the instance name is shown as the (Default) value under each branch – check this to ensure you are editing the settings for the correct instance.
  • If it isn't already present add a new DWord setting under MSDASQL with a name of DisallowAdhocAccess and value of 0.