slash.young (5/8/2014)
I know this is an old post, my apologies. I had this same issue... so I did the above suggestion and set the Logon account for the SQL Server Service in my config manager to use a domain account instead, so my sysadmin users on the sql server could use xp_cmdshell. This worked and fixed the problem, HOWEVER, in doing so it made the SQL Server instance only accessible to the local box. When trying to access through SSMS from a different box I would get a "Cant generate SSPI Context error".Any Suggestions?
Thank you in advance
It's an old topic again, but can be useful for somebody in the future.
Sometimes when you change the service account (the one who runs the service through SQL Server Configuration Manager), the new account is not able to create the SPN's for the combination Service-Service Account, so Kerberos authentication (Windows) fails
So, you can fix this by removing the old SPN's and creating new ones.
To check that's the problem:
setspn -l OldServiceAccount
setspn -l NewServiceAccount
You should see something like
MSSQLSvc/<FullQualifiedName> or MSSQLSvc/<FullQualifiedName>:InstanceName
MSSQLSvc/<FullQualifiedName>:ListeningPort
if you see then only for the old one, remove them and create new
To remove:
setspn -d MSSQLSvc/<FullQualifiedName> OldServiceAccount
setspn -d MSSQLSvc/<FullQualifiedName>:ListeningPort OldServiceAccount
To add the new one
setspn -s MSSQLSvc/<FullQualifiedName> NewServiceAccount
setspn -s MSSQLSvc/<FullQualifiedName>:ListeningPort NewServiceAccount
It's important to use -s to check for duplicates before adding new SPN's as that can make a bit of a mess.
Regards