I think I made this way too difficult. The problem first started when one of our Windows 2003 servers crapped out. Total hardware failure. The fix was to replace the server and re-configure. After the new server was in place and working fine, the SQL backup job started failing. The SQL Server was a separate server using a share on the file server via UNC. For the article I will call the File Server FILESERVER1 and the SQL Server SQLSERVER1.
I noticed that the SQL Server Service was using LocalSystem to start the service. The odd thing is that the SQL Server backup job was working fine before the FILESERVER1 failed using the LocalSystem account. I thought this was odd because I have read several articles stating that if you want to backup to a UNC the SQL Server Service must be a domain user and have the appopriate permissions to the share. So I thought, no sweat. I will just change the SQL Server Service to use a domain user with privledges and away we go. I changed the service and got approved for a quick Service Restart. The Service started fine and I could browse the server fine locally using Enterprise Manager.
I then try to hit the server from my laptop using Windows Authentication from Enterprise Manager and received the following error: Cannot generate SSPI context error. I quickly just changed the service back to LocalSystem so I could do some testing on a test box, trying to reproduce the error.
Found this article from Microsoft: http://support.microsoft.com/kb/811889. This is a very good description of the problem and some possible solutions. The article links off to this article which contained the solution to the problem: http://msdn.microsoft.com/en-us/library/aa905162%28SQL.80%29.aspx
Using the setspn resource tool was an interesting adventure. First off I am not a Domain Admin so I had to have some assistance from our Server team. Had a Domain Admin run the following command
setspn -A MSSQLSvc/SQLSERVER1.domain.com sqlaccount
You will need to use the Fully Qualified Name here. You will need to run this for every server you would like to start using a Domain User account. The sqlaccount is the actual Domain User account you want to use. Refer to the Security Account Delegation Article for more details such as what to do if the domains do not match, etc. One gotcha is that it took a little while for the setspn command to make the change to the domain user. I assume it was some kind of Synchronization delay.
While browsing around for the error I sumbled on the ADSI Edit MSC. This is helpful to look at to review the setspn change. You can find the user in the domain and view the property. If you are a Domain Admin you can actually make the change from the ADSIEdit console. see below for screenshots from ADSIEdit:
So after the user was given the attribute for SQLSERVER1, I then hoped back on the server and restarted the SQL Service. Service Started fine, I was able to browse to it from Enterprise Manager on my laptop, and the UNC backup to FILESERVER1 started working again.
Here is another good Microsoft KB that shows how to setup Windows Services Accounts for SQL Server: http://msdn.microsoft.com/en-us/library/aa176564%28SQL.80%29.aspx