Monday, May 4, 2009

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

2 comments:

Amsterdaz said...

Hi Steve

This described the problem I had perfecly - even down to SQL Server authentication working whilst Windows authentication didn't. The article took me through all the trouble-shooting steps and provided the fix. More than that, it pointed out the error in my ways of using the domain Administrator account for SQL services. I'll fix that when I get a minute (any advice on that?).

Thanks for a very concise, informative and useful article. How I wish MS could put their point across so well.

Darren

Steve Endow said...

Hi Darren,

Glad it was helpful and glad the solution worked for you!

I've now run into a few more weird problems trying to use domain accounts for SQL Server services, so it is definitely a common issue.

Thanks,

Steve Endow