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


I've started this blog as a convenient location to store useful SQL Server scripts that I come across.

I've been working with all SQL Server versions since SQL Server 2000 using Standard, Enterprise and Developer Editions.

I will credit the authors where the scripts were not home grown and link to source sites where possible as others might find the information useful.