Upgrade to WinXp sp 2 now "Cannot generate SSPI context" err

StephenMiller

I have just upgraded my client to WinXp sp 2 and I am now getting an "Cannot generate SSPI context" error whenever I try to connect to my SQL2000 Sp3 Win2000 server. I have deleted and recreated the network alias using the Client Network Utility but this has not resolved the issue.

Searching Google, this type of error typically relates to a network using a Domain controller, which I am not using.

I can connect to the server via the alias name using enterprise manager and can run queries, so I doubt it is a personal firewall issue. I have checked these settings, adding an exception for TCP 1433 and the SQL Compare programs.

What should I do?

Cheers,

Stephen

Brian Donahue

Hi Stephen,

The SSPI error that you describe applies to lots of things. At the lowest level, probably Microsoft's CryptAPI. Have you tried going into the Client Network Utility and checking the 'Force Protocol Encryption' option?

It could also be a problem accessing or generating a machine key to do the encryption or decryption with. Our customers get exactly the same error when they try to activate SQL Compare and the activation key data can't be encrypted. In that case, you might want to have a look at this KB article from Red Gate:

http://www.red-gate.com/messageboard/vi ... .php?t=501

Brian Donahue

Try it logged in as (the) local machine administrator. Something is not letting you automatically create or access the crypto key. It could be a permissions problem.

StephenMiller

That doesn't seem to make any difference. 'Everyone' has full access to the 3 files under 'C:\Documents and Settings\All Users\Application Data\Microsoft\Crypto\RSA\MachineKeys'. I have tried checking and unchecking the 'Force protocol encryption' checkbox.

Thanks,

Stephen

Brian Donahue

Hi,

Maybe your domain only allows you to authenticate with Kerberos and your computer has wisely chosen NTLM security? I saw this bit in Microsoft's knowledge base.

http://support.microsoft.com/default.as ... -us;811889

StephenMiller

I really don't understand this networking stuff and I'm getting pretty frustrated now. Looking at kb 811889, Kerberos authentication is only successful if there is valid DNS functionality. I don't have a DNS server on my simple network and just use the IP address of the SQL server to connect and therefore 'ping -a 123.123.123.123' does not resolve a DNS name.

Verify the server environment
1. Ok. Running Win2K sp4
2. Ok. Running Win2K sp4
3. Ok. Not running a cluster
4. Ok. Account running SQL Service is a member of Administrators group

Verify the client environment
1. Ok. I have checked that the registry key HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\NtLmSsp exists on the client.
2. Ok. I have restarted my machine to avoid cached credentials and I am not using a domain controller to connect.
3. Ok. Dates on both the client and server are identical.
4. This points pretty vague. It says I can't have another file called security.dll. Searching my client for 'security.dll' I have
- C:\WINDOWS\system32\security.dll
- C:\WINDOWS\ServicePackFiles\i386\security.dll
- C:\WINDOWS\SoftwareDistribution\Download\6ca7b3a8efd5a9b6f87fff395a2eb989\security.dll
Assuming the first instance is correct I have renamed the other two instances to 'security.dll.old'. Not surprisingly this had no influence on the problem.
5. Ok. Client is WinXP SP 2

Also, the values at HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\MSSQLSERVER\CLIENT\CONNECTTO are:
DSQUERY = DBNETLIB
MYSQL = DBMSSOCN,203.47.nnn.nnn (where MYSQL is the name of the SQL Server set up in Client Network Utility)

Given that the only change I have made to the client is install SP2, I have a simple network, without DNS servers etc and I can currently connect to the remote data source using both Enterprise Manager and Query Analyser using the Alias setup using the Client Network Utility, what else can I do?

Thanks,

Stephen

Brian Donahue

Hi Stephen,

Maybe this is a wierd side-effect of a security update like the IIS Lockdown tool. Can you have a look at the local security policy setting for the SQL Server's computer and check the LAN Manager Authentication Level setting? Try changing that back to the default of accepting LM and LTLM authentication, refresh your machine policy and trying the connection again.

StephenMiller

Brian,

I don't have IIS running on the SQL Server machine so I doubt the IIS Lockdown tool has been applied. I have however checked the Local Security Policy tool and cannot find a reference to 'LAN Manager Authentication Level'. I have:

Security Settings
- Account Policies
- Password Policy
- Account Lockout Policy
- Local Policies
- Audit Policy
- User Rights Assignment
- Security Options
- Public Key Policies
- Encrypted Data Recovery Agents
- IP Security Policies on Local Machine
- Client (Respond Only)
- Secure Server (Require Security)
- Server (Request Security)

Are any of these relevant?

Thanks,

Stephen

StephenMiller

How we going guys? I am still stuck with this problem.

Brian Donahue

Hi Stephen,

What's happening is most likely a problem with the CryptoAPI on the machine being able to create a ticket for Kerberos. Since none of the fixes have worked, I'd suggest contacting Microsoft to try to resolve the problem.