Insufficient data from Andrew Fryer

The place where I page to when my brain is full up of stuff about the Microsoft platform

SQL Server Reporting Services

Reporting Services can be a bit of a pain to configure so here’s a top tip from Saleem Hakani who often sees this issue with his customers:

Did you know that when SQL Server service account is configured with a Windows NT account, SQL Server sets various Windows user rights and permissions on several files, folders, and several registry keys. However, when you use SERVICES from CONTROL PANEL to set SQL Server Service account these permissions are not set and you may run into serious issues due to lack of proper security on SQL Server files, folders, registry keys & Windows user rights.

Therefore, it’s strongly recommended that you use “SQL Server Configuration Manager” and not SERVICES from the CONTROL PANEL when changing SQL Server or SQL Server Agent service account.

However, if you’ve already made changes to the account using SERVICES from CONTROL PANEL then you may want to follow below steps to fix this:

Step1: Apply FULL Permissions on the following registry keys and its subkeys.

  • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90
  • HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<MSSQL.x>

Step2: Set Full Control for the startup account for the MSSQLServer service and the SQLServerAgent service (either a local Windows NT account, or a domain Windows NT account) on these NTFS folders.

  • <Drive>:\Program Files\Microsoft SQL Server\<MSSQL.1>\MSSQL

However, instead of making these manual changes it’s recommended that you use SQL Server Configuration Manager for making changes to SQL Server/Agent service accounts.