Tuesday, February 14, 2012

Cannot Generate SSPI Context

Win Server 2003
SQL Server 2000 SP4
SharePoint Server

Win Server 2000 (Domain Controller / Active Drectory)

Clients: Win XP / Access XP

I have two clients that can access the SQL Server and one that can not. All clients can access SharePoint (SLQ Server Back end) and directories controlled by AD.

The one that cannot access the SQL Server errors: "Cannot Generate SSPI Context". I have verified:
1) date and time
2) occurances of Security.DLL (win/system32; program file/common files/AOL...; Service Pack Files/i386 (x2))
3) Not using cached credentials

we recently moved our network and did not change any settings on any of the computers. I read KB811889; those suggestions did not resolve the issue.

Please assist me in touble shooting this connection problem. Also, how does changing the SQL Server Port on the server affect SharePoints connection to its database?

Hi,

When NT authentication is used to connect to SQL Server, we use a security API called SSPI (Security Support Provider Interface). In general, it's a tool that allows for a "server" to verify (usually with an AD domain) that a "client" is the same as he/she claims to be. This is done with exchanging of security package via a secure connection. More details can be found in the MSDN (see Platform SDK: Authentication).

In order SSPI to be functioning normally, you need to follow the instructions at KB 811889, please go through them once again. In addition, the following post from Nan is very useful:

http://blogs.msdn.com/sql_protocols/archive/2005/10/19/482782.aspx

Regarding changing the port that SQL Server listens on: Microsoft has been given a standard port (1433) by the internet task force and by default, a Default SQL Server instance listens on it. Given that, the client APIs when detecting you are trying to connect to a "Default" instance automatically assume the port is 1433. Unlike this, Named SQL instances by default determine port dynamically - usually select an available one and if not set otherwise, every time upon startup attempt to reuse it. However, note that the client has no means of telling what the named instance port is or a modified default instance port is. The options are:

(1) Manually specify port at client (either via alias or via data source definition like Data Source=myserver,myport)

(2) Use discovery mechanism provided by SQL Browser (SQL 2005) or a special "listener" thread (in SQL 2000). The thread is "listening" on UDP port 1434 and upon request provides information about the settings of the SQL instances installed on the machine.

Note that if you change the port of a default instance, you cannot use the SQL Browser/Listener approach, because for default instances we do not make a roundtrip to Browser - for perf reasons. You could create an alias or specify port when connecting.

If you could avoid changing the default 1433 port of SQL Server, that'd be great. BTW - changing the port may be related to your SSPI errors.

HTH,

Jivko Dobrev - MSFT
--
This posting is provided "AS IS" with no warranties, and confers no rights.

No comments:

Post a Comment