My problem started shortly after I switched the service account that powered the SQL Services. On the initial install I used my domain login during the install. Then I received the good old change password nag and changed my password three or four days later. Life went on just peachy until I had to reboot. So in order to avoid this again I decided to use a different domain account. Using SQL Server Configuration Manager I changed all the services to use the new domain account and began the process of starting each service leaving the agent as the last. To my surprise I received a nice little error:
The request failed or the service did not respond in a timely fashion. Consult the event log or other applicable error logs for details.
At this point I thought a reboot might resolve this which it didn’t. So I decided to listen to the error message and search through the error logs. In the Windows Event Viewer I found the following:
Login failed for user ‘xxxxx\sqlservice’. Reason: Failed to open the explicitly specified database. [CLIENT: <local machine>]
After reading this I thought the SQL permissions were incorrect so I double checked those and they turned up fine. So I proceeded on. This time to the SQL error logs in the instance directory. In my case “C:\Program Files\Microsoft SQL Server\MSSQL10_50.DEV08R2\MSSQL\Log”.
I opened the ERRORLOG with notepad and began reviewing the contents and I found some interesting information. Essentially it stated:
Error: 18456, Severity: 14, State: 38.
Login failed for user ‘XXXXX\sqlservice’
Now I am thinking perhaps I mistyped the password which seemed like a viable action seeing the rest of the services were working fine. So I visit the Configuration Manager again and attempt to redo the password. For Schnitz and Giggles I decided to deliberately enter in the wrong password and surprisingly I received a nice error:
The specified network password is not correct.
Now I am puzzled. The password was right all along yet the previous error is saying that the login failed. WTF? To rule out if this is a Windows permissions or SQL Server permissions issue I added the service account to the local Administrators group and suddenly I was able to start the service. At the very least I now know that this is a Windows permission issue. Now I just need to figure out where this permissions issue resides.
I started to review the accounts and remembered during the installation that SQL Server creates Windows groups. So I hone in on the SQLServerMSSQLUser$XXXXXX$DEV08R2 group and find that the service account is not listed. So I add the service account to this group and kick off the Agent service and boom… it works! I tried changing the service account on a SQL Server 2005 instance and I noticed that the Windows group was updated so I am not sure why SQL Server 2008 R2 didn’t behave the same way.
The actual permissions are required within the instance directory. Here is where I found it.
And the required permissions are as illustrated.
Once you switch the service account make sure you take a look at the owner for the databases and jobs. In my case I wanted all the databases and jobs to have the owner of the sqlservice so I ran the following query to address the database owner.
SELECT name ,'USE [' + name + ']; EXEC sp_changedbowner ''xxxxxx\sqlservice''' 'cmd' ,SUSER_SNAME(owner_sid) 'owner' FROM sys.databases WHERE (SUSER_SNAME(owner_sid) != 'xxxxxx\sqlservice') OR (SUSER_SNAME(owner_sid) IS NULL)
Then I copied the contents from the ‘cmd’ column and executed them to make the change.
Note: you cannot change the owner of the master, model, msdb and tempdb databases. If you attempt to change the owner of the system databases you will be greeted with an error.
To circumvent this you can add an additional condition to the WHERE clause as followed and this will skip the system databases.
SELECT name ,'USE [' + name + ']; EXEC sp_changedbowner ''xxxxxx\sqlservice''' 'cmd' ,SUSER_SNAME(owner_sid) 'owner' FROM sys.databases WHERE (SUSER_SNAME(owner_sid) != 'xxxxxx\sqlservice') OR (SUSER_SNAME(owner_sid) IS NULL) AND (database_id NOT IN (1,2,3,4))
The stored procedure sp_changedbowner will be removed in a future version of SQL Server. Which means you need to learn how to use ALTER AUTHORIZATION. Which I picked up from Jes’s article Changing a SQL Server Database. Here is the revised script you would use to generate the ALTER AUTHORIZATION statement.
SELECT name ,SUSER_SNAME(owner_sid) 'owner' ,'ALTER AUTHORIZATION ON DATABASE::' + name + ' TO [sa]' 'cmd' FROM sys.databases WHERE (SUSER_SNAME(owner_sid) != 'xxxxxx\sqlservice') OR (SUSER_SNAME(owner_sid) IS NULL)
Lastly I ran the following query to generate the ‘cmd’ statement which changed the job owner for all the jobs I had set up. Keep in mind that if you have any backup jobs that are backing up to a network share you will need to update the share permissions and add the service account to permit writing to that location.
SELECT name ,'EXEC msdb..sp_update_job @job_name = ''' + name + ''', ''@owner_login_name = xxxxxx\sqlservice''' 'cmd' ,SUSER_SNAME(owner_sid) 'owner' FROM msdb..sysjobs WHERE (SUSER_SNAME(owner_sid) != 'xxxxxx\sqlservice') OR (SUSER_SNAME(owner_sid) IS NULL)