Author: Jugal Shah

The Cannot generate SSPI context issue is described by http://support.microsoft.com/?id=811889 in general. In this article we will discuss one daunting case of “Cannot generate SSPI context” error message when failing to connect to SQL server.

In most related cases, customers report this issue as “They are not able to connect to their local SQL Server, but once they connect to my network, they can’t connection to my local SQL Server”.

Such issue is reported against MSDE and SQLExpress versions. But actually, it can happen with any version/edition of SQL Server, including SQL Server 2000 and SQL Server 2005 that support NT integrated authentication.

The error message for the failed connection is
[SNAC] “[SQL Native Client]SQL Network Interfaces: The Local Security Authority cannot be contacted.[SQL Native Client]Cannot generate SSPI context”

[MDAC] “Cannot generate SSPI context”;

Failed System.Data.SqlClient.SqlException: Cannot generate SSPI context

It can happen when all of followings are true:
(1) The hosting machine of SQL Server is connected to a network, including home network or dialup connection, but it is disconnected from its domain.
(2) The OS of the hosting machine is Windows XP or 2000. Not windows 2003.
(3) The connection is to a local SQL Server.
(4) Connection configuration causes network library to choose TCP/IP provider.

A scenario that meets all of (1) (2) and (3) looks like an extreme corner case. But the reality is that it is quite often if the hosting machine is a laptop computer.

To avoid condition (1) by connecting to your corporate domain through VPN or disconnecting from network completely.

From user’s perspective, however, in many cases, either connecting over VPN or disconnecting from network might prevent you from accessing some valuable resources, so I want to discuss solutions that do not depend on (1) first.

In most cases, users do not explicitly require TCP/IP as the connection provider. For example connection strings in form of “.\”, “(local)\”, “\” are among them. We might wonder why network library chooses TCP/IP provider instead of Shared Memory provider, if the connection string is not prefixed with “tcp” and the server is local.

Answer is that it can happen if the TCP/IP provider is in front of other providers in the client protocol order list, or/and the local server is not listening on Share Memory and Name Pipe.

As described above, only TCP/IP provider has the issue; hence, configuring network library not to choose TCP/IP is a solution. To do that, first, on the server side, make sure your server is listening on Shared Memory or/and Named Pipe connection requests; then, on the client side, change the protocol order list such that Shared Memory and/or Named Pipe are in front of TCP/IP, or prefixing your connection strings with “lpc” or “np” to force Shared Memory or Named Pipe, or using alias that prefix Named Pipe in connection strings, whichever you feel most comfortable with. Note that certain SKUs of SQL Server have named pipe connection turned off by default.

If You really want the TCP/IP connection, the option is to use TCP/IP loop-back address, i.e. “127.0.0.1”, as your . You can also add an entry into the /etc/host file as well.

For example, if your connection string has form of “\” and is not prefixed with “tcp”, without modifying the connection string, you can configure an alias with alias name as \, protocol as TCP/IP, server as “127.0.0.1\” or “127.0.0.1,”.

Remember that the “Cannot Generate SSPI context” problem described in this post only happens when connecting to a local server; thus, the “127.0.0.1” is applicable.

Be aware that only TCP/IP provider can provides the benefits of Kerberos authentication as discussed in http://blogs.msdn.com/sql_protocols/archive/2005/10/12/479871.aspx

From the error message reported by SNAC ODBC/OLEDB, you can differentiated the issue described by this post from another case of “Cannot generate SSPI context”, in which the root cause is because, in Active Directory, the Service Principle Name (SPN) of SQL Server is registered for a domain account different from the SQL Server is actually running under.

Make sure service account has enough permission in AD to register the SPN. You can check the below parameters for SPN as well.

SET SPN-L Service Account. You can get more information for the SPN from this post.
http://sqldbpool.com/2009/11/26/service-principle-name/

Check that SPN is registered, if registered there shouldn’t be duplicate entry, should be match with the service account, Service account should have enough permission, PORT number is correct.

For the connection failure…
You can also check Resolving Connection failure article.

Troubleshooting this in the AI era

Years later, my approach to this error has a new step. I still run the deterministic checks first — DNS, port, clock skew, SPNs, the actual auth scheme — because those are facts, not guesses. Then I paste the output into an AI assistant and let it rank the likely causes. The script does the facts; the AI does the reasoning. Here is the script:

<#
.SYNOPSIS
    Troubleshoot "Cannot generate SSPI context" — SQL Server Kerberos checklist.
.DESCRIPTION
    Run from the CLIENT machine. Checks DNS, port reachability, time skew,
    SPN registration, and the actual auth scheme of a live connection.
    Paste the full output into an AI assistant with the prompt printed at the end.
.PARAMETER ServerInstance
    e.g. "SQL01" or "SQL01\INST1,1433"
.PARAMETER ServiceAccount
    The domain account running SQL Server, e.g. "CONTOSO\sqlsvc".
    Needed for the SPN check.
.EXAMPLE
    .\Test-SspiContext.ps1 -ServerInstance "SQL01" -ServiceAccount "CONTOSO\sqlsvc"
#>
[CmdletBinding()]
param(
    [Parameter(Mandatory = $true)][string]$ServerInstance,
    [string]$ServiceAccount
)
$targetHost = ($ServerInstance -split '[\\,]')[0]
Write-Host "=== SSPI / Kerberos diagnostics for $ServerInstance ===" -ForegroundColor Cyan
# 1. DNS — Kerberos needs the *name*, not the IP, to match the SPN
Write-Host "`n[1] DNS resolution"
try {
    $dns = Resolve-DnsName $targetHost -ErrorAction Stop | Select-Object -First 1
    Write-Host "  OK: $targetHost -> $($dns.IPAddress)"
} catch { Write-Host "  FAIL: cannot resolve $targetHost" -ForegroundColor Red }
# 2. Port reachability
Write-Host "`n[2] TCP 1433 reachability"
$t = Test-NetConnection -ComputerName $targetHost -Port 1433 -WarningAction SilentlyContinue
if ($t.TcpTestSucceeded) { Write-Host "  OK: port 1433 open" }
else { Write-Host "  FAIL: port 1433 unreachable" -ForegroundColor Red }
# 3. Time skew — Kerberos dies beyond ~5 minutes
Write-Host "`n[3] Time skew (Kerberos tolerance: 5 min)"
Write-Host "  Local time (UTC): $([DateTime]::UtcNow.ToString('u'))"
Write-Host "  If the server clock differs by more than 5 minutes, Kerberos auth fails."
# 4. SPN registration for the service account
if ($ServiceAccount) {
    Write-Host "`n[4] SPNs registered for $ServiceAccount"
    $spns = setspn -L $ServiceAccount 2>&1
    $mssql = $spns | Select-String 'MSSQLSvc'
    if ($mssql) { $mssql | ForEach-Object { Write-Host "  $_" } }
    else { Write-Host "  FAIL: no MSSQLSvc SPN found — the classic SSPI cause" -ForegroundColor Red }
} else {
    Write-Host "`n[4] SPN check skipped (pass -ServiceAccount to enable)"
}
# 5. Live connection: what auth scheme did we actually negotiate?
Write-Host "`n[5] Live connection auth scheme"
try {
    $cs = "Server=$ServerInstance;Integrated Security=SSPI;Connection Timeout=10;"
    $con = New-Object System.Data.SqlClient.SqlConnection($cs)
    $con.Open()
    $cmd = $con.CreateCommand()
    $cmd.CommandText = "SELECT auth_scheme FROM sys.dm_exec_connections WHERE session_id = @@SPID"
    $scheme = $cmd.ExecuteScalar()
    $con.Close()
    if ($scheme -eq 'KERBEROS') { Write-Host "  OK: connected via KERBEROS" -ForegroundColor Green }
    else { Write-Host "  WARN: connected via $scheme (not Kerberos) — delegation / double-hop will fail" -ForegroundColor Yellow }
} catch {
    Write-Host "  FAIL: $($_.Exception.Message)" -ForegroundColor Red
}
Write-Host "`n=== Now paste everything above into your AI assistant with this prompt: ===" -ForegroundColor Cyan
@'
I'm getting "Cannot generate SSPI context" connecting to SQL Server from a domain-joined client.
Below is my diagnostic output. Tell me the single most likely root cause and the exact fix,
ordered by likelihood. Be specific — SPN commands, account names, exact syntax.
'@

Run it as .\Test-SspiContext.ps1 -ServerInstance “SQL01” -ServiceAccount “CONTOSO\sqlsvc”, then paste the full output into your AI assistant with the prompt the script prints. The script gathers the evidence; the AI ranks the suspects.

I’m getting “Cannot generate SSPI context” connecting to SQL Server from a domain-joined client. Below is my diagnostic output. Tell me the single most likely root cause and the exact fix, ordered by likelihood. Be specific — SPN commands, account names, exact syntax.