I am following up a question from Remote backup to folder path in domain I asked earlier. I could not get my proxy SQL Server Agent user to work to back up to a remote location and have given up for the time being due to pressure to move on. However, I still want to back up to remote folders.
To that end I have created an AD user and put them into the local server which runs the SQL Server instance Admin group and given the AD user full permission to the remote folder and can browse and create a file at that location.
I then tried to change the MS Service from "Local Service" to the new AD user, using SQL Server 2008 R2 Configuration Manager this failed due to a log on error? Even after a number of tries and resetting the User password and then pasting it into the Configuration Manager this would not work.
I resorted to using the Windows Services to change the log on and all was fine. The instance worked ok and the remote backups worked as well. However, we then noticed a ODBC datasource issue from our TS into the SQL Server, it kept failing with "HY000 cannot generate sspi context", we Googled this and found a fix is to put the AD User into the Domain Administrator Group, which we did and the error disappeared. However, I have defeated my objective that was to use a standard user to run SQL Server services to reduce the security risk!
My question: is there another way to set the standard user / give it different permissions so it does not have to be a Domain Administrator so that ODBC datasource connections work? I am also wondering why I could not change the Service via SQL Server Configuration Manager
Best Answer
As you have found out the SQL Server Service runs the backup but not the user giving the backup command.
Changing the service user should be done using the SQL Server configuration manager which in the background sets correct permissions for the service account, all of which are documented on MSDN, giving the user running the service Administrative rights on the computer will make it possible to run the service but is not a good security practice.
You have to be sure to run the correct version of the Configuration manager so try running MMC.exe and adding the correct snapin for your version of SQLServer so check that first and if it failes reregister the snapin by running in a command prompt the following command
mofcomp "%programfiles(x86)%\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof"If this failes, use the information in the msdn article to manuallyl set the correct permissions.
Your SSPI context error is due to incorrect SPN for the sql server, The SQL server creates a SPN automatically when you start it and the domain admin has permissions to to that which fixes the SSPI error. After you change the service account for the server to a normal user download the Microsoft Kerberos configuration manager for SQL Server and make sure to create the correct SPN