Dealing with the "Cannot generate SSPI context" error message

One of our sales engineers came up to me with with a problem that I had not come across before.  He was getting the error “Cannot generate SSPI context” when he tried to back up a database. Before getting too deep into the problem, I’ll lay out the background of how the problem occurred.

Our applications work with SQL Server 2000 and 2005.  Our desktop applications have the ability to back up the SQL Server database and store the backup on the local machine.  The backup command is issued to the database server, typically on it’s own server.  The account that SQL Server runs under typically can only access the local file systems.  You can get around that by running SQL Server under an account with network access, but as a shrink wrapped application, we want to under the default installation of SQL Server.

To get around the file system access, I wrote a win32 service that runs on the same machine as SQL Server.  Our applications back up the database through my agent service.  When the agent receives a backup request from a client, it does the following:

  1. Performs some preventative maintenance on the database
  2. Defragments the log file
  3. Backs up the database to local path
  4. Compresses the database backup to a .zip file
  5. Sends the compressed backup to the client
  6. Deletes the backup and compressed backup from the server.

It does a few other things, but those steps are the highlights of the backup process.  Our engineer was getting the “Cannot generate SSPI context” error right at step one.  I have never come across that error so it was time to fire up Google and go searching.  One of the top hits for goggling that error message was a KB article, 811889.  It was informative, but not especially helpful for me.  The top hit was much more helpful, “Cannot generate SSPI context” error message, when connect to local SQL Server outside domain, on the SQL Protocols blog.  Who knew that SQL Protocols had it’s own blog.  This post had all of the good details of what was happening and suggestions on how to resolve it.  I like that.

In short that error can occur when all of the following are true:

  1. The hosting machine of SQL Server is connected to a network, including home network or dialup connection, but it is disconnected from its domain.
  2. The OS of the hosting machine is Windows XP or 2000. Not windows 2003.
  3. The connection is to a local SQL Server.
  4. Connection configuration causes network library to choose TCP/IP provider.

The root cause is that agent service is using integrated security to connect to the local server over TCP/IP.  The SSPI in the error message stands for Security Support Provider Interface.  SSPI is a set of Windows API that handle delegation and authentication over data transport layers (TCP, Named Pipes, etc).  With TCP/IP and SSPI, the Kerberos protocol is used to authenticate the user account.  This will attempt to access the Active Directory services of the domain that the user is logged into.  If that domain is not accessible, the authentication attempt will fail.  This check will only occur if SSPI detects that it is on a network.  If it’s not on a network, it will use NTLM, which for our situation will work just fine.

In our case, the engineer has a laptop and he logs into it with a domain account.  If he’s demoing the products at a clients site, he may have a network connection, but not be connected to our domain.  The immediate work around was for him to close his network connection and do his backup.  Literally all he needed to do was to press a button on his laptop to turn off his wireless adapter.

The long term solution will be for me to change conditions #3 or #4.  The code is currently hard coded to connect to a sever named “(local)”, I may try replacing that with the TCP/IP loopback address 127.0.0.1.  If that doesn’t work, I add a setting that allows the agent service to connect with the Shared Memory or Named Pipes providers.

Looking forward to stackoverflow

Not an actual stack overflow, but the new site, stackoverflow.com, run by Jeff Atwood and Joel Spolsky.  Their aim is to provide some sort of programming Q&A site.  Accurate and up to date information that’s easy to find by having programmers ask questions and other programmers answer them.  I hope that they can pull it off.  My usual method of learning something programming related is by what Joel referred to as “page-fault” learning.  I start out with a Google or forum search, get the basics, and start implementing until I hit an error or a mental block.  Then I jump back on the Intertubes to research a little deeper.

I’m so tired of hitting Experts-exchange when I google a topic.  So much that I’ll specifically exclude it from my Google searches by tacking on  “-site:www.experts-exchange.com” to the search string.  There’s something about that site that just rubs me the wrong way.  it’s like “We’ll tease you with the question, now pay up for the answer”.  And you’ll have no way of knowing if the answer is correct or applicable to your need until after you have paid them.  No sir, I don’t like it.

Right now, the only thing up on the site is a podcast of a conversation between Jeff and Joel.  I’ve been listening to it and it’s pretty good.  I can’t wait for the site to take form and I hope to be able to contribute to it in some small form.

There’s no privacy with infant video monitors

We still have a video baby monitor on our youngest, Laura.  She’s 5, but we never got around to taking it down.  At bedtime, Laura and her older sister Kathryn will perform for each other in front of the camera while the other one is watching the monitor.

Last night when the girls went to bed, I turned on the monitor.  All I could see was static and some form of interference. The camera to monitor connection is wireless.  You can pick one of two channels and fiddle around with the antenna, but that’s about it.  It’s very much like watching TV on a B&W portable, circa 1976.

As I fiddled around with the antenna, the view on Laura’s room disappeared and I could see an infant lying in a bed with a pacifier in his mouth.  I called out to my wife, “Look, they canceled the Laura Show and replaced it with a new one.”  We think this baby is our next door neighbor’s baby.  They just had a baby boy a couple of months ago.  I’m going to over and talk to them on the weekend and let him know that we are picking up their video signal.

For now, we have stopped using the monitor.  Laura is will past the age of needing a monitor, and it feels too much like voyeurism to be watching and listening to someone else’s baby.