Friday, November 04, 2005

SQL Server "Cannot generate SSPI context" Solution for When Not Connected to Domain

SQL Protocols : "Cannot generate SSPI context" error message, when connect to local SQL Server outside domain

"...
[MDAC] “Cannot generate SSPI context”;

It can happen when all of followings 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.

...

In very rare case, however, if you really in need of TCP/IP connection, the option is to use TCP/IP loop-back address, i.e. “127.0.0.1”, as your . For example, if your connection string has form of “\” and is not prefixed with “tcp”, without modifying the connection string, you can configure an alias with alias name as \, protocol as TCP/IP, server as “127.0.0.1\” or “127.0.0.1,”. Remember that the “Cannot Generate SSPI context” problem described in this post only happens when connecting to a local server; thus, the “127.0.0.1” is applicable. ..."


This information helped me solve some recent "Cannot generate SSPI context" problems so I wanted to make sure I shared (and captured it here in case I need it again in the future)

I develop on my notebook and in many cases in remote locations (i.e. home). I recently moved to Windows XP and that's when the problems started. If I were to VPN into work, everything would work fine. But I'd rather not have to VPN in just to development and run my projects...

This evening I had ENOUGH! It was fix it or else... Luckily it ended up being a easy fix. The above post's suggestion to use the loop-back IP address did the trick for me.

Now I'm happily testing my apps without being connected to the domain/network. (Yeah I know... working/coding/testing on a Friday night... I so need a life. But I've heard they are over rated ;)

2 comments:

Anonymous said...

THANK YOU, THANK YOU! In my case, I have 2000 and 2005 running on my laptop, so based on your tip, I used 127.0.0.1,3333 where 3333 is the port I'm using.

Anonymous said...

Perfect! The simple change from 'localhost' to '127.0.0.1' solved the issue... Thanks!