Friday 1 May 2009

Using Sql Server Management Studio Offline

First post, a tip I desperately needed today.

I have been using my laptop offline and occasionally find myself faced with a "cannot generate sspi context" error message.

Working offline I have found that I was unable to connect to my local instance (SQL Server 2005) using the server name so I was having to connect as localhost and for most local SQL / T-SQL purposes that was fine. However, any attempt to access DTS was met by the aforementioned problem.

The solution turned out to be simple - open SQL Server Configuration Manager (under the Microsoft SQL Server 2005 program group in Configuration Tools) and goto SQL Native Client Configuration then Aliases. Right click on your instance name and goto Properties. I found that the protocol was set to TCP which is not fully functioning without my work network so changing to Named Pipes means the whole communication is internal to the laptop.

Now I can logon to the expected instance name and work with no problems. 5 minute fix.

Click here for a discussion of the SSPI problem on SQL Server Central