SSPI Errors for SQL Server Authentication?!

Question

We have several old ASP and PHP web applications which use SQL Server Authentication. Periodically, all the applications lose the ability to connect to our SQL Server 2000 database server, getting access denied.

Corresponding to roughly the same times, we are getting

1115 Cannot generate SSPI Context SQLSTATE HY000

errors on the SQLServer 2000 server.

And here's the weird part - rebooting the web server fixes the problem. Rebooting the database server has no effect.

This makes no sense to me - I didn't think SSPI was in any way involved with SQL Server Authentication.

Any ideas?

Edit:

Some additional details:

The web server is in the DMZ. The hosts file on the web server has an entry for the database server (and the ip address is correct), so the web server (theoretically at least) shouldn't even be going to DNS to connect to the database server.

It does not appear to be a firewall issue.

Answer 1

I think you are right, that error is not associated with SQL authentication in my experience. Is the web server behind a firewall of any sort? Have any changes been made to the "hosts" file on either server? Have you tried pinging the SQL Server from the web server when you have these issues to make sure the web server is able to resolve the name of the SQL Server? I would check the Activity Monitor in Enterprise Manager to be sure that the web apps are definitely connecting using SQL authentication. One possibility is some kind of network or domain controller problem - that would explain the dropped connections from the web server and the SSPI context errors on the SQL Server, but not sure why restarting the web server would resolve it..

Answer 2

Have you read this article yet?

Answer 3

We had this between our main servers and some clients in other countries.

The underlying Kerberos loses the plot (technical term :-) if there is too much delay (eg firewalls) between here and there.

We fixed it by specifying the port. Also, I'd suggest using the FQDN of the server. So we now use: "SQLServer.domain.tld\InstanceName,1234"