[Part-1]Let’s drill why you “Cannot generate SSPI context”   5 comments


When you see error “Cannot generate SSPI context” what is the first thing comes to your mind, SPN problem isn’t it?

There are many such errors which lead us to assume that we have problems with SPN. Although, it could be a correct approach, do we really try to understand how does this authentication work? Why it fails and how can we resolve such errors?

Well, I have also come across these errors numerous times and hence researched a bit deeper to find out some internals, how it works behind the scenes etc. And that calls for this two-series of blog post which will give us an insight of windows authentication process between an application and SQL Server, the related errors and their troubleshooting approach.

 As stated above, this is a two-series blog which will cover the following:

Part -1: We will discuss about how windows authentication process works in SQL Server using SSPI

Part-2: I will try to explain why this authentication fails and how to troubleshoot these issues.

Part-1: How windows authentication process works in SQL Server using SSPI

 When a user logs in to application using a windows user instead of SQL Login and tries to establish a connection to SQL Server that process is called integrated security (SSPI). It is the most recommended and secure authentication process.

 SSPI stands for “Security Support Provider Interface”. SSPI is a set of Win32 API’s used to delegate security, encrypt/decrypt data, and perform mutual authentication.

 The SQL Server driver on a client computer uses integrated security to use the Windows security token of the user account to successfully connect to a computer that is running SQL Server. SQL Server provides following configuration packages for authentications:

i.              NTLM over Named Pipes (not using Security Support Provider Interface [SSPI])

ii.             NTLM over TCP/IP sockets with SSPI

iii.            Kerberos over TCP/IP sockets with SSPI

 Before we go further, let’s get familiar with few terms which we are going to use in subsequent sections.

 NTLM - Only allows a single hop directly from client to SQL Server. This is sufficient in a lot of scenarios and is easier to configure.

 Kerberos – Allows a double hop from client to middle machine and then to end machine with the same credentials. For example, browser -> IIS -> SQL Server or client -> SQL Server -> SQL Server (Using linked server).

 SPN – An SPN is a domain unique identifier of a specific instance of a network server resource. You can have an SPN for a web service, a SQL service, an SNTP service, etc. SQL Server can have multiple service instances on the same physical computer; each will have its own unique SPN (each having a different port).

 Now let’s try to understand how SSPI is used and how authentication works with SQL Server.

 This whole authentication process takes place with the help of 2 windows API’s.

                1) InitializeSecurityContext: Used on client side to generate a raw stream of bytes from the user token.

                2) AcceptSecurityContext :    Used on the server side (used by SQL Server) to read the raw stream of bytes sent over from the client.

 The diagram below depicts how these API’s are used to perform successful login.

 SSPI

In the above diagram, user tries to connect to application from client machine. Client machine driver performs forward and reverse lookup of the server name to obtain the IP address and then the FQDN of the SQL Server using GetHostByName.

 Once it is able to get the FQDN, client driver then constructs an SPN by adding  MSSQLSvc/ before FQDN followed by Port seprated by “:”

e.g.

                FQDN -> sqlinstance.manish.sqlactions.com

                SPN -> MSSQLSvc/sqlinstance.manish.sqlactions.com:1433

 Client driver now calls a windows API called InitializeSecurityContext by passing parameter SPN and bit flags (ISC_REQ_DELEGATE|ISC_REQ_MUTUAL_AUTH) that indicates requests for the context.

 Now this API (InitializeSecurityContext) contacts the Domain Controller to get the token which can then be sent to SQL Server. The Domain Controller looks up the SPN in Active Directory and checks for following conditions.

           If (SPN => does not exist)
                   {
                      NTLM authentication will be used.
                   }
          Else If (SPN => exist)
                   {
                        If (SPN exist => configured properly on single object
                         {
Kerberos will be used (the Domain Controller encrypts the token with a hash based on the account that 
the SPN is associated with. This should be the account that the SQL Server service is executing under. 
This allows only that user account to decrypt the token.)
                         }
                        If (SPN Exist => not configured properly or duplicate spn)
                          {
                             ERROR: InitializeSecurityContext returns an error which will abort the entire 
                             authentication process. It is not possible to drop down to NTLM at this point
                           }
                   }

If the above condition is successful, InitializeSecurityContext returns success and the encrypted token to the client through the out buffer parameter. This can be an NTLM or Kerberos ticket based on the above conditions.

 Client now forwards the contents of this buffer through a TDS packet to the SQL Server. (At this stage it does not interpret the contents of this buffer and therefore does not know if we are using NTLM or Kerberos).

 Once SQL Server receives this buffer, it calls another API i.e. AcceptSecurityContext and passes the buffer from the client though this API. The API AcceptSecurityContext decrypts the token and contacts the Domain Controller to check existence of SPN. Now, there could be two cases:

 1.       AcceptSecurityContext fails – In this case, SQL Server sends a failure message to the client similar to below message: Login failed for user ‘(null)’

 2.       If AcceptSecurityContext succeeds, depending upon certain factors, it may return either of the following return parameters:

a.       SEC_E_OK – The security context received from the client was accepted. Authentication process completed. No further action required.

b.      SEC_I_CONTINUE_NEEDED – The server must send the output token back to the client and wait for a returned token. Authentication process completes once       returned token is received from the client.

 At this stage authentication process will complete either with error or Success!!!

 As you can see there are a lot many things which take place during authentication and there can be many reasons where it gets fail.

 In the second part of the blog, I will try to cover few scenarios which includes why this fails and how can we troubleshoot such issues.

 Stay tuned!

References:
——————————
SSPI
http://msdn.microsoft.com/en-us/librarY/aa380493(v=VS.85).aspx
Get “Cannot generate SSPI context” error
http://connect.microsoft.com/SQLServer/feedback/details/388712/get-cannot-generate-sspi-context-error
How to troubleshoot the “Cannot generate SSPI context” error message
http://support.microsoft.com/kb/811889
Using Kerberos with SQL Server
http://blogs.msdn.com/b/sql_protocols/archive/2005/10/12/479871.aspx
 
 

5 responses to “[Part-1]Let’s drill why you “Cannot generate SSPI context”

Subscribe to comments with RSS.

  1. Good one

  2. Excellent Manish. Keep up the good blogging.

    Regards,
    Gopalakrishnan Arthanarisamy

  3. Pingback: We didn’t know that… | SQLactions.com

Leave a Comment

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 974 other followers

%d bloggers like this: