Cannot generate SSPI context error message

A client has been using an eConnect integration for months without any issues, but all of a sudden last Friday, he received this error when trying to run the integration:

"Cannot generate SSPI context"

Whenever I see a Dynamics GP or SQL Server related error with "SSPI", I instantly assume that it has something to do with Windows authentication on SQL Server. Here is a Microsoft KB article explaining more about the obscure mechanics of SSPI and the error.

To summarize that article, here are the steps that I took to try and troubleshoot the error:

1. From a workstation, open a command prompt
2. Type the command: ping sqlservername
3. Type the command: ping -a sqlserveripaddress

For #2, you should receive a response indicating "Reply from..." and the SQL server's IP address.

For #3, you should receive a similar reply, but you should also see the fully qualified name of the SQL server (i.e. gpsql.company.local)

If either one of those gives you an error or does not return the proper server IP address or name, you probably have a DNS issue that needs to be resolved.

If those steps look good, as they did for my client, try these steps.

1. On the GP SQL Server, open the Services applet (Start --> Run --> Services.msc)
2. Locate the service for the GP SQL Server instance (i.e. SQL Server (GP))
3. Look at the "Log On As" column, to see what account the service is using to run
4. If the SQL Server service is running with an account other than Local Service, it is likely that there is a problem with the account permissions, the account password, or the Domain communication.

In my case, I found that the SQL Server service had been setup to use the domain Administrator account (which is not recommended for several reasons).

And it turns out that the Administrator password was reset last Friday (one of the reasons why you should not use it for any Windows services), which was the day that the errors started occurring.

My speculation is that even though the SQL service was still running with the Administrator credentials, the password change affected Kerberos authentication for new Windows authentication connections to SQL server. And of course, because eConnect only uses Windows Authentication, and GP only uses SQL Authentication, that is why GP users didn't receive any errors, but the eConnect integration no longer worked.

So, I opened the service properties for each of the SQL Server services that used the Administrator account and updated the Administrator password for each of them. I was informed that the service would have to be restarted for the changes. After getting all users out of GP and restarting all of the SQL services (server, agent, browser, etc.), the error went away and the integration worked fine!

Of course, there is still the issue of the domain Administrator account being used for Windows services. I will be scheduling a time with the client to create a dedicated SQL services domain account, and switch the services over to use that account.

One caution about using a domain account to run SQL Server: You will need to be careful with the permissions. The account will need to have a fair amount of local access on the server so that it can manage the database files, and it will also need the ability to Log On As A Service, which is setup in the local security policy. Finally, it will need to have adequate permissions to create its own SPN for Kerberos authentication (as mentioned in the MS KB Article). So make sure to plan for some downtime and testing if you decide to switch to a domain account for SQL Server.


UPDATE: After reviewing the Google results further regarding this error, it appears that there are several other more innocuous or low level causes for the SSPI error. So if the above steps don't resolve the issue quickly, you may have a more complex issue on your hands. Here are two examples of other possible causes that are not explicitly referenced in the MS KB article:

http://blogs.msdn.com/sql_protocols/archive/2005/10/19/482782.aspx

http://sqlblogcasts.com/blogs/grumpyolddba/archive/2008/07/19/cannot-generate-sspi-context.aspx



UPDATE April 2018: Someone shared an additional angle to the SSPI context error saga. Apparently the error can occur if there is a problem with an "SPN".

https://msdn.microsoft.com/en-us/library/ms677949(v=vs.85).aspx


If, for some reason, the SPN associated with a Windows Service, such as the SQL Server service, becomes invalid, this can cause the infamous SQL Server SSPI errors.  I believe this might occur if the server name was changed.

The apparent solution for this is to inspect the SPN information for the domain account used by SQL Server and potentially delete and recreate the SPN.

You would use the "setspn" command to do this.

https://technet.microsoft.com/pt-pt/library/cc773257(v=ws.10).aspx


To view SPN information for the server, you apparently use "setspn -L"

You can use "setspn -D" to delete SPNs, then "setspn -S" to save new SPN information.

Note that there may be more than one SPN record for a given service account, so the -D and -S may need to be used more than once.

I have not performed this process before, but someone shared this example with me.  It isn't clear to me what the -L results actually show, as I don't see anything about SQL Server.

Note the -D and -S versions are called a second time with port 1433 listed.  I don't know if this is just a precautionary measure to clean up such records if they happen to exist, or if those are mandatory for SQL Server.

Steve Endow is a Dynamics GP Certified Trainer and Dynamics GP Certified IT Professional in Los Angeles.  He is also the owner of Precipio Services, which provides Dynamics GP integrations, customizations, and automation solutions.

You can also find him on Google+ and Twitter

http://www.precipioservices.com