SQL Server Error – Cannot Generate SSPI Context Solutions

authenticationconnectivityerrorssql serversql-server-2008

When someone tries to connect to a SQL Server instance, the error shows up:

It's not possible do generate SSPI context.

Yesterday we had a blackout (don't know how to say this expression in English) and I had to shut down our servers.

Looking for answers, I found this:

SQL Server 2008 connectivity issue : cannot generate SSPI context

But it doesn't help me, because they're working fine until yesterday. I don't want to change anything. But if a have to, I will change it.

Obs: I can't restart the server now.

Edit: Since I my answer, we haven't had any errors.

Best Answer

'Cannot generate SSPI context' is a generic error. It can be caused by many issues, like an outaded password, clock drift, Active Directory access permissions, failure to register an SPN and so on and so forth.

There is no solution to this problem. The only 'solution' is to investigate the cause, as per KB811889 and/or Troubleshooting Kerberos Errors. Applying one solution or another from random Internet resources, w/o understanding the cause, may or may not solve the issue, may or may not cause frustration, may or may not cause irrevocable damage.

Related Solutions

Sql-server – Receiving error Error: 18059, Severity: 20, State: 1 in the SQL Logs

The post that Roni Vered referred to in 2013 did not say simply say: "change the connection pooling in the application's connection string."

The text said to that there was a problem with using "an Application Role ... with connection pooling." The answer was: Turn Off Connection Pooling.

This could be done in the application's connection string by including "; Pooling=False".

This worked back in the mid-2000's. Likely it will still work for you if you are still using SQL Server 2008.

The no longer correct path to the original post was: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=473777&SiteID=1

A suggested workaround from 2007: On each call if the approle is already in use, then skip the setting of the approle again. Sample code:

IF NOT EXISTS (SELECT * FROM sysusers
    WHERE name = USER_NAME()
     AND isapprole = 1)
 EXEC sp_setapprole ...

Sql-server – How does MS SQL Server authenticate MSDTC connections when using SSPI

There are two Distributed Transaction Standards Supported by the DTC. XA Transactions and OLE Transactions and recovery is implemented differently.

XA Transaction Recovery Process use xa_recover_entry of GetXaSwitch to get a list of xid from the transaction resource manager (SQL Server is a resource manager) that it then uses to rollback or commit the prepared transactions. I assume that running as NETWORKSERVICE is enough to do that based on what is documented in Disabling TIP, LU and XA Transactions

In Windows XP , Windows Server 2003, and subsequent versions you can disable XA transactions to help prevent the security risk that arises when a user-specified DLL, used by the DTC to communicate with the XA partner's transaction manager, is loaded directly into the DTC process. This situation exposes a resource manager's databases to serious data corruption and can cause Denial of Service (DOS) attacks. The disabling of XA transactions helps to protect the DTC from this DLL attack.

The recovery process for OLE is implemented a bit differently. The resource manager has a list of prepared transactions and is responsible for reenlisting them in the DTC. Have a look at the Performing Recovery section of Implementing DTC Interfaces.

_{Not sure I actually provided a definitive answer for you here but I thought this was too much for a comment.}

Best Answer

Related Solutions

Sql-server – Receiving error Error: 18059, Severity: 20, State: 1 in the SQL Logs

Sql-server – How does MS SQL Server authenticate MSDTC connections when using SSPI

Related Question