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