• 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