April 29, 2008 at 5:49 am
I have two new servers that I will be housing some databases on. I am getting the following SSPI error message when I try to query analyze from any another computer on the domain:
Server: Msg 11004, Level 16, State 1
[Microsoft][ODBC SQL Server Driver]Cannot generate SSPI context
Main Facts:
1. I have verified that file sharing to these two servers from any machine on the domain is possible.
2. DNS is working as they can be ping'd either by name or IP address.
3. The MSSQL Server and Agent are running under a domain user account; this same account is also running the SQL server services on other servers without issue whatsoever.
4. The two servers can connect via query analyzer to other servers with no problem.
The only workaround is to create a host entry on the client computer for the IP address that you want to connect to a.k.a add the IP and DNS name (or FQDN) of these two servers to my hosts file.
There is a fix at http://support.microsoft.com/kb/843248, but applies to servers running SQL Server 2000 SP3 - I am running SP4. I've already filled out the form to get the hotfix mentioned in the article, but I was wondering if there is anything else I can do from my end.
Any help is appreciated.
Thanks!
April 29, 2008 at 8:44 am
Hi!
Are you trying to connect using Windows Authentication or SQL?
April 29, 2008 at 8:49 am
SSPI are Windows authentication errors and, I believe, issues with the Kerberos ticket.
I've seen this act strangely in a variety of ways. Perhaps the client can't get the SPN for some reason (though why does the host entry work?). I've seen timing errors (system times) since Kerberos depends on times being very close.
There are a few troubleshooting guides, but I have found a couple places in the past where even PSS at MS couldn't determine the reason for the error. Eventually it seemed to go away.
Here are a few places to check: http://www.sqlservercentral.com/articles/Installation/cannotgeneratesspicontext/929/
http://blogs.msdn.com/sql_protocols/archive/2005/10/15/481297.aspx
April 29, 2008 at 8:57 am
Yes - Windows Authentication
April 29, 2008 at 9:01 am
Steve - Yes, the hosts file is a big mystery and has my AD Admin and SQL DBA scratching their heads as well. Timing is not a factor as all servers' clocks are centrally managed. I will take a look at the articles - thanks!
April 29, 2008 at 11:42 am
Hi, Folks!
I got the same issue once on out network and the following helped me!
ON the SQL-SERVER machine, make sure named-pipes are enabled (start --> program Files --> Microsoft SQL Server --> Server Network Utility. Check that Named Pipes exist under Enabled Protocols.
Now go to client machine from which you are trying to connect using the query analyzer.
On the client machine:
start --> program Files --> Microsoft SQL Server --> Client Network Utility.
Goto Alias TAB. Click Add button.
Select Named Pipes and give the sqlserver name on the Server alias.
Now try to connect from the machine using query analyzer.
April 29, 2008 at 6:20 pm
Steve and TALAT,
I did a little more digging around and found the following post: http://www.sqlservercentral.com/Forums/Topic303430-92-1.aspx#bm363067. If you scroll down to the bottom of the string the user named Old Hand suggested this link:
http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=60&threadid=84680&STARTPAGE=1. At this site I found the following excerpt under the posting that was made on 05/03/2007 09:31 AM:
Setspn.exe can be used to register/de-register SPNs. One can
register the same SPN for the same container more than one time.
The registration beyond the first registration may not do anything.
One de-registration will remove the SPN from Active Directory
totally. Because of this, the easiest first step to troubleshoot
“Cannot Generate SSPI Context” is to run SQL server under
Local System account and gracefully shut it down. You can then
change your service account to whatever you want. SPN will not
be registered and clients will fallback to use NTLM.
My problem was definitely related to an incorrect SPN registration. Upon following the tip above, I was able to query analyze into the problem servers without making any modifications to the hosts file, Client Network Utility, or Server Network utility. I still have the MSSQL agent and server running under the local system account on them and will change them to the domain user account I mentioned before tomorrow. I am sure it should be fine from here on in.
Thank you both for your helpful tips and attention.
John
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply