Cannot generate SSPI context error

swrobel

I'm getting a cannot generate SSPI context error message in my SQL Prompt log. This is probably because my SQL Server doesn't have TCP/IP connections turned on, but uses Named Pipes instead. I have my connection in SQL Management Studio set up right and it works just fine, but for some reason SQL Prompt can't use the connection. It worked fine in 3.1...

Brian Donahue

Hi Stefan,

Can you please tell us when this happens? The most common cause of this has nothing to do with the connection and more to do with the RedGate licensing components. Because there is encrypted information in the licence file, you may get this error at startup because SQL Prompt cannot decrypt the licence.

The reason for this is that the Microsoft CryptoAPI is not returning a crypto key, either because of access permissions or because your user account has a mandatory roaming profile type. In this case, the crypto key, which is in the user profile, may have been deleted.

swrobel

This happens whenever I try to refresh my cache. It is not getting any of my database objects.

Brian Donahue

Did you connect to the database using SQL authentication? If so, the CryptoAPI issue could be happening because it's unable to decrypt the password saved in the cache file for the database.

Maybe deleting this particular database from the cache and reconnecting will fix it?

swrobel

I'm using Windows Authentication.

Oh yes, and my cache is empty because it hasn't even been able to get into the database in the first place.

Brian Donahue

Are there details in the error message (a stack trace)? This should tell where the error is occurring.

Based on your information it sounds like maybe Prompt is trying to create a cache file, but can't because it can't encrypt the connection details because Microsoft CryptoAPI is broken.

swrobel

03 Aug 2007 16:13:10,969 [1] INFO  RedGate.SqlPrompt.Engine.LogService - Starting new SQL Prompt editor session
03 Aug 2007 16:19:12,698 [1] WARN  _28 - SQL exception occurred retrieving connection properties for: Data Source=<edited>;Initial Catalog=<edited>;Integrated Security=True;Persist Security Info=False;Pooling=False;Application Name="Red Gate Software Ltd SQL Prompt 3.5";User Instance=False
System.Data.SqlClient.SqlException: Cannot generate SSPI context.
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
   at System.Data.SqlClient.TdsParser.SSPIError(String error, String procedure)
   at System.Data.SqlClient.TdsParser.SNISSPIData(Byte[] receivedBuff, UInt32 receivedLength, Byte[] sendBuff, UInt32& sendLength)
   at System.Data.SqlClient.TdsParser.ProcessSSPI(Int32 receivedLength)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
   at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
   at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
   at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
   at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
   at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
   at System.Data.SqlClient.SqlConnection.Open()
   at _162._1(Boolean )
   at _162._1(ConnectionProperties , Boolean )
   at _28._1(ConnectionProperties , Boolean )
   at _28..ctor(ConnectionProperties conn, Boolean forceRefresh, Boolean caseSensitiveFiltering)

Brian Donahue

Based on the stack trace, it sounds like a problem with the Kerberos authentication on the server. The SQL Service account may have been changed, but there is a Kerberos ticket retained for the old account.

There are some suggestions as to how to fix this on MSDN here:

http://blogs.msdn.com/sql_protocols/arc ... 81297.aspx

and in more detail here:

http://blogs.msdn.com/sql_protocols/arc ... 82782.aspx

swrobel

The issue is that it works fine on my laptop using 3.1, but not on my new desktop using 3.5. Both are configured the same...

I'd like to just force the connection to use Named Pipes, if possible? It seemed that 3.1 was automatically using Named Pipes, since that's what I had SQL Management Studio configured to use, but I think 3.5 is trying to use TCP/IP.

Brian Donahue

I think you can do this. In the connection manager, you can set up connection properties for this server. In the server box, use
np:servername

The np prefix should force the connection to use named pipes.

swrobel

That didn't seem to work. The connection doesn't appear to be using named pipes:

06 Aug 2007 20:32:30,221 [1] WARN  _28 - SQL exception occurred retrieving connection properties for: Data Source=dc-sql-srvr;Initial Catalog=Invhub;Integrated Security=True;Persist Security Info=False;Pooling=False;Application Name="Red Gate Software Ltd SQL Prompt 3.5";User Instance=False

Brian Donahue

I had a feeling that wouldn't help. I suppose there may be a simpler explanation. If you ping the server, does it resolve to the fully-qualified domain name of the server? Also, are you logged in with cached information? Try opening a network share that you normally have access to and see if you are prompted for a username/password.

swrobel

Pinging does resolve to the fully-qualified domain name. I'm never prompted for username/password information on local network shares. How do I know if I'm logged in with cached information?

Brian Donahue

You would be if you're prompted for a password for a share you have access to. Unless you've saved the password in your password list.

swrobel

Is there a config file I can copy over from the old machine? I upgraded it to 3.5 and it still works just fine without any connections defined.

Brian Donahue

SQL Prompt saves your connection settings in the cached copy of the database that's saved on disk. It may help to copy that over, but if the cache needs to be refreshed, this will probably happen again. Usernames and passwords for Windows accounts are not saved. With Windows Authentication, the operating system performs all of the authentication using the credentials created when you logged on to the computer.

swrobel

I really don't understand why this works on my old machine that was upgraded from 3.1 to 3.5 then, because it didn't have any special configurations. Where do we go from there? I currently have a non-functional product.

Bart Read

I'd try checking the surface area configuration of your SQL Server instance to see if there's something that would allow connections from the one machine and not the other. It's odd that you can connect from SSMS though and not SQL Prompt.

swrobel

I still feel like it would solve the problem if I could just get the connection to use named pipes. The np: trick that Brian gave to me did not work.

Bart Read

Hi there,


The whole reason we don't force a protocol is so that SQL Prompt will connect using whatever protocol is right for the server (actually this is all wrapped up in ADO.NET), so for a server that only allows named pipe connections, SQL Prompt will connect using named pipes without the need to force the protocol.

For sanity's sake I've just verified that this is the case using two separate servers on different machines, one a SQL Server 2000 instance, the other a 2005 instance. In both cases SQL Prompt had no difficulty connecting when both instances were set up to use only named pipe connections.

We also haven't changed the code that actually creates the connection since before SQL Prompt 3.0 was released except to flick the "user instance" flag on when working with an attached MDF file in Visual Studio 2005, so I have to come back to the conclusion that there's some difference between the client machines that's causing this problem, whether it's network setup, Kerberos (as Brian suggested), firewalling, or some other issue. Actually I'm not so sure about the Kerberos problem because that should only generate the SSPI error when connecting over TCP/IP.

Hope that helps.


Thanks,
Bart

Bart Read

Some additional information:

http://support.microsoft.com/kb/811889

Have a look at the "Why Security Support Provider Interface chooses NTLM or Kerberos" as it could be some sort of DNS or Active Directory issue.


Thanks,
Bart

swrobel

I just set up a SQL authentication account and set the connection to use that. I don't get why windows auth wasn't behaving, but I was getting frustrated trying to make it work and this (semi-)solved the problem.

swrobel

JACKPOT! I fixed it. It turns out I had to add an alias to the SQL Native Client Configuration in Administrative Tools -> Computer Management. The alias forces the connection to my server to always use Named Pipes. I hope this helps someone else avoid this headache...

Bart Read

Thanks for letting me know. That's a relief. In theory the way it should work is that the SSPI problem should only manifest itself if neither authentication method is successful, so if NTLM/named pipes is specified it should "just work" (as I saw here), however I was starting to wonder so I appreciate you letting us know.


Cheers,
Bart