locked
Cannot Generate SSPI Context RRS feed

  • Question

  • Hi,

    Our web application (hosted on Computer 'WebApp') is accessing a MS SQL 2008 server hosted on another computer 'DB1' in the same domain. I tried to setup a vpn connection. I therefore added a "Active Directory Certificates Service" role. As it didn't work, I removed the role.

    Since then I get a "Cannot Generate SSPI Context". The current authentication is 'Windows authentication'.  When I try to connect to the DB from the computer 'WebApp' using the IP address of computer 'DB1', it works. How to restore the access to the DB using the SQL Server Name instead of the SQL Server IP address ?

    Already looked at KB article 881889 but not useful. Looked also articles about the SETSPN command unsuccessful.

    Since the problem is arrived, I changed our application to use SQL server IP address and SQL Server authentication. Our application runs jerkily:The same operation can be fast at a time and very slow to another.

    I fixed the issue temporarily by doing 2 things:

    1. I ran this command from DB1 to reset the SPNs
      setspn -r WEBAPP
    2. I delegated the "Validated write to service principle name" permission to the some account.

    However today I got the "Cannot Generate SSPI Context" again.
    I also ran a "ping DB1" and got normal result.
    My .NET web application is sometimes very slow and sometimes fast.
    Is this related to the SPNs ?

    Here the output of the 'SETSPN -L WEBAPP' command ran from the DB1 machine. Could you check if it is correct ?
    Registered ServicePrincipalNames for CN=WEBAPP,CN=Computers,DC=MyDomain,DC=com:
        MSSQLSvc/DB1
        HOST/WEBAPP$
        HOST/WEBAPP$.MyDomain
        MSSQLSvc/DB1\SQLEXPRESS:1433
        MSSQLSvc/DB1\SQLEXPRESS
        MSSQLSvc/DB1:SQLEXPRESS
        MSSQLSvc/DB1:1433
        MSSQLSvc/WEBAPP.MyDomain.com:1433
        MSSQLSvc/WEBAPP.MyDomain.com
        WSMAN/WEBAPP
        WSMAN/WEBAPP.MyDomain.com
        TERMSRV/WEBAPP
        TERMSRV/WEBAPP.MyDomain.com
        HOST/WEBAPP
        HOST/WEBAPP.MyDomain.com

    • WEBAPP is the machine where my .NET application is installed (running W2k8)
    • DB1 is the machine where the SQL Server is installed (running W2k3) as well as the domain controller
    • MyDomain is the domain name

    The web application opens also at any time some files located on the DB1 machine. Of course the file name is based on the IP address now. Using \\DB1\aDir\aFile.ext is not working while it should.
    As I wrote above I delegated the "Validated write to service principle name" permission to some "instances" I forgotten. Do you think it was a good idea. If yes to which "instances" should I give this permission ?

    Thanks a lot

    Monday, June 3, 2013 12:54 PM

All replies

  • See if one of the links below can help you:

    How to troubleshoot the "Cannot generate SSPI context" error message:

    http://support.microsoft.com/kb/811889?wa=wsignin1.0

    Or

    http://blogs.technet.com/b/mdegre/archive/2011/01/13/sql-server-2008-connectivity-issue-cannot-generate-sspi-context.aspx

    Regards,


    André CR / Helped? If the answer is yes mark! If the answer is no, wait a little bit because i'll back! Visit my blog! sqlmagu.blogspot.com.br

    Monday, June 3, 2013 2:25 PM
  • I already read and read again kb811889 but it is too general to address my issue.

    I read with interest the 2nd article and then checked the MaxTokenSize register parameter on both machines. I already increased this value to 64K on 'DB1'. As the article recommends changing this value on all the machines involved, I took a look on 'WEBAPP'. I was surprised by the huge value of that parameter (>1M). Could this be an issue ?

    Tuesday, June 4, 2013 8:39 AM
  • If after read the articles the only thing you found strange is that value, you can try to investigate more, see this:

    http://www.mssqltips.com/sqlservertip/1557/configure-maxtokensize-for-sql-server-authentication/

    Regards,


    André CR / Helped? If the answer is yes mark! If the answer is no, wait a little bit because i'll back! Visit my blog! sqlmagu.blogspot.com.br

    Wednesday, June 5, 2013 11:37 AM
  • Hi,

    Our web application (hosted on Computer 'WebApp') is accessing a MS SQL 2008 server hosted on another computer 'DB1' in the same domain. I tried to setup a vpn connection. I therefore added a "Active Directory Certificates Service" role. As it didn't work, I removed the role.

    Since then I get a "Cannot Generate SSPI Context". The current authentication is 'Windows authentication'.  When I try to connect to the DB from the computer 'WebApp' using the IP address of computer 'DB1', it works. How to restore the access to the DB using the SQL Server Name instead of the SQL Server IP address ?

    Already looked at KB article 881889 but not useful. Looked also articles about the SETSPN command unsuccessful.

    Since the problem is arrived, I changed our application to use SQL server IP address and SQL Server authentication. Our application runs jerkily:The same operation can be fast at a time and very slow to another.

    I fixed the issue temporarily by doing 2 things:

    1. I ran this command from DB1 to reset the SPNs
      setspn -r WEBAPP
    2. I delegated the "Validated write to service principle name" permission to the some account.

    However today I got the "Cannot Generate SSPI Context" again.
    I also ran a "ping DB1" and got normal result.
    My .NET web application is sometimes very slow and sometimes fast.
    Is this related to the SPNs ?

    Here the output of the 'SETSPN -L WEBAPP' command ran from the DB1 machine. Could you check if it is correct ?
    Registered ServicePrincipalNames for CN=WEBAPP,CN=Computers,DC=MyDomain,DC=com:
        MSSQLSvc/DB1
        HOST/WEBAPP$
        HOST/WEBAPP$.MyDomain
        MSSQLSvc/DB1\SQLEXPRESS:1433
        MSSQLSvc/DB1\SQLEXPRESS
        MSSQLSvc/DB1:SQLEXPRESS
        MSSQLSvc/DB1:1433
        MSSQLSvc/WEBAPP.MyDomain.com:1433
        MSSQLSvc/WEBAPP.MyDomain.com
        WSMAN/WEBAPP
        WSMAN/WEBAPP.MyDomain.com
        TERMSRV/WEBAPP
        TERMSRV/WEBAPP.MyDomain.com
        HOST/WEBAPP
        HOST/WEBAPP.MyDomain.com

    • WEBAPP is the machine where my .NET application is installed (running W2k8)
    • DB1 is the machine where the SQL Server is installed (running W2k3) as well as the domain controller
    • MyDomain is the domain name

    The web application opens also at any time some files located on the DB1 machine. Of course the file name is based on the IP address now. Using \\DB1\aDir\aFile.ext is not working while it should.
    As I wrote above I delegated the "Validated write to service principle name" permission to some "instances" I forgotten. Do you think it was a good idea. If yes to which "instances" should I give this permission ?

    Thanks a lot

    Hi,

    If you don't get the SPN's correct, you'll get the 'cannot generate SSPI context' messages.  I can see you've got 4 registered for MSSQLSvc/DB1...  this doesn't look right to me based on the information you have provided.

    Granting the service account/machine permissions to read/write service principal names means that you don't have to do it manually yourself.  If you made a mistake doing it manually, then restarted the SQL Server services, it'll have registered new SPN's and caused duplicates, which could be the cause of your problem.

    Are the SQL Server services running under a system account, e.g. local system?  I presume so as you're listing out the SPNs registered for your WEBAPP server.  If you're using domain accounts, they get registered for the account running the SQL Server services.

    Also, you have SQL Server running on your WEBAPP server?

    If you query sys.dm_exec_connections, look at the auth_scheme column.  I take it you're not seeing any kerberos entries?

    If it's in a complete mess, it may be easier to delete all the SPN's and having given the service accounts/machine permission to read/write service principal names in AD, restart all the services.  They should sort it out automatically for you



    Thanks, Andrew

    Wednesday, June 5, 2013 12:35 PM
  • If below result is different on both the server then this kind of error is coming

    select auth_scheme from sys.dm_exec_connections where session_id=@@spid

    Resolution is set SPN on both servers and it make authentication mode same on both the server.

    Tuesday, June 11, 2013 2:01 PM
  • Hi

    Please refer :

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

    How the Cannot generate SSPI context error was fixed


    Thanks Saurabh Sinha

    http://saurabhsinhainblogs.blogspot.in/

    Please click the Mark as answer button and vote as helpful if this reply solves your problem


    Tuesday, June 11, 2013 7:09 PM