December 15, 2008 at 8:35 am
Brian,
I run all SQL services under one domain account. When I do SETSPN -L on that account, I don't see any SPNs listed. (I have never used SETSPN to manage SPNs.)
I have SQL 2005 on a couple of different servers. When I login to SQL using Windows Auth through Management Studio over TCP/IP it works on all servers except one. This means they are defaulting to NTLM?
On the server I cannot login to, the login over TCP/IP fails with:
Cannot connect to SQL1.
Cannot generate SSPI context. (Microsoft SQL Server, Error: 0)
I can login using Named Pipes no problem. What could be wrong with this server that is preventing me from logging in over TCP/IP?
Also, if I'm using one domain account for multiple SQL Servers, can I just keep adding SPNs to that account?
Thanks!
Rob
December 15, 2008 at 9:12 am
I should've checked before I posted: I can see using the query from the article that all the logins are using NTLM.
I'm still not sure why I get the SSPI error when trying to login to that one SQL server though. Any ideas?
December 15, 2008 at 9:29 am
Rob Symonds (12/15/2008)
I should've checked before I posted: I can see using the query from the article that all the logins are using NTLM.I'm still not sure why I get the SSPI error when trying to login to that one SQL server though. Any ideas?
There are quite a few reasons. One of the most common is that an SPN did get registered, but to a different account. Here's how to look for that:
Service Logons Fail Due to Incorrectly Set SPNs
Look for the section on how to use LDP to find duplicate SPNs. A duplicate or incorrect SPN will definitely cause that failure. Otherwise, if you go to support.microsoft.com and search for "cannot generate SSPI context" you'll get a handful of articles that talk about the issues.
And with respect to one domain account for multiple SQL Servers, adding the additional SPNs is fine and everything will work.
K. Brian Kelley
@kbriankelley
December 15, 2008 at 4:00 pm
Thanks Brian. The MS docs on SPNs are confusing.
I'm looking at things with our AD admin. More investigation reveals the following:
All services for SQL Server run under the CORP\SqlServices domain account. SETSPN -L CORP\SqlServices shows no SPNs attached.
However, SETSPN -L SQL1 (where SQL1 is the name of my SQL box) shows the MSSQLSvc SPN:
HOST/SQL1$.CORP
HOST/SQL1$
MSSQLSvc/SQL1.CORP.[MYCOMPANY].COM:1433
HOST/SQL1.CORP.[MYCOMPANY].COM
HOST/SQL1
SETSPN -L SQL2, SQL3, etc (where SQL2, SQL3 are the names of other SQL boxes) shows no SPNs:
MSSQLSvc/SQL2.CORP.[MYCOMPANY].COM:1433
HOST/SQL2.CORP.[MYCOMPANY].COM
HOST/SQL2
MSSQLSvc/SQL3.CORP.[MYCOMPANY].COM:1433
HOST/SQL3.CORP.[MYCOMPANY].COM
HOST/SQL3
We tried deleting the MSSQLSvc SPN on SQL1. It returns an object updated message but SETSPN -L shows it in the list again.
--
A previous DBA installed SQL Server 2005 on SQL1. I wonder if he installed it under the default account the installer uses and only later updated the services to run under the domain account. My suspicion is that this is what happened and is somehow the cause of the problem. What do you think?
Thanks again!
December 15, 2008 at 9:00 pm
Rob Symonds (12/15/2008)
We tried deleting the MSSQLSvc SPN on SQL1. It returns an object updated message but SETSPN -L shows it in the list again.--
A previous DBA installed SQL Server 2005 on SQL1. I wonder if he installed it under the default account the installer uses and only later updated the services to run under the domain account. My suspicion is that this is what happened and is somehow the cause of the problem. What do you think?
Thanks again!
Is SQL1 running under the local System or Network Service account?
K. Brian Kelley
@kbriankelley
December 16, 2008 at 5:54 am
Is SQL1 running under the local System or Network Service account?
SQL1 is running under the domain account: CORP\SqlServices. But what I'm thinking is that when the last DBA installed it last year, he installed it (or maybe upgraded it) under a local account? I don't have any way of knowing. I wasn't here so I don't have a history on the box. I just inherited it with the problem.
Thanks.
December 16, 2008 at 7:55 am
Update: problem resolved.
We kept trying to delete that SPN but didn't have the right syntax. The AD admin got it to work though with the following:
setspn -D MSSQLSvc/SQL1.CORP.[MYCOMPANY].com:1433 SQL1
Thanks for the great article and forum replies Brian. MS has done a lame job with the documentation and tools for this. It's good to have somebody in the community like you that can help make sense of it all.
January 15, 2009 at 3:11 pm
The article is great, but I've read that the SPN needs to be re-registered when SQL is stopped and re-started.
I usually provide the service account read-write SPN privileges on AD. Is someone else doing the same? This avoids having to re-register the SPN if it un-registers during a reboot.
January 16, 2009 at 8:32 am
SA (1/15/2009)
The article is great, but I've read that the SPN needs to be re-registered when SQL is stopped and re-started.I usually provide the service account read-write SPN privileges on AD. Is someone else doing the same? This avoids having to re-register the SPN if it un-registers during a reboot.
I would be very interested in knowing more about that as well.
January 16, 2009 at 5:37 pm
The SPN does not need to be recreated when SQL Server is stopped and restarted. The only reason this would be necessary is the SQL Server is using dynamic ports and it has a port change. If SQL Server is configured to use static ports (which it should), then the SPN only has to be registered once.
K. Brian Kelley
@kbriankelley
April 29, 2009 at 4:53 pm
Hi Brian, Great article I agree.
I am having trouble getting Kerberos set up tho.
I am using:
- a Virtual Machine (VMWare)
- sql 2005 SP3
I have run the 4 steps adived:
SETSPN -A MSSQLSvc/MyDBServer MyDomain\SQLServerService
SETSPN -A MSSQLSvc/MyDBServer:1433 MyDomain\SQLServerService
SETSPN -A MSSQLSvc/MyDBServer.mydomain.com MyDomain\SQLServerService
SETSPN -A MSSQLSvc/MyDBServer.mydomain.com:1433 MyDomain\SQLServerService
now when I run SETSPN - L MyDBServer I get:
Host/MyDBServer
HOST/MyDBServer.mydomain.com
and for SETSPN - L MyDomain\SQLServerService I get:
MSSQLSvc/MyDBServer.mydomain.com
MSSQLSvc/MyDBServer.mydomain.com:1433
MSSQLSvc/MyDBServer
MSSQLSvc/MyDBServer:1433
I am a little confused, one thing I know is that the MyDomain\SQLServerService is locked down quite heavily via gpedit, it can logon as a service and lock pages in memory but not much more. I'm wondering if it needs a specific permission of some sort?
I can see that sessions are still using NTLM, any ideas as to what is nobbling me?
Thanks,
Carlton..
April 30, 2009 at 3:54 am
Carlton Leach (4/29/2009)
Hi Brian, Great article I agree.I am having trouble getting Kerberos set up tho.
I am using:
- a Virtual Machine (VMWare)
- sql 2005 SP3
I have run the 4 steps adived:
SETSPN -A MSSQLSvc/MyDBServer MyDomain\SQLServerService
SETSPN -A MSSQLSvc/MyDBServer:1433 MyDomain\SQLServerService
SETSPN -A MSSQLSvc/MyDBServer.mydomain.com MyDomain\SQLServerService
SETSPN -A MSSQLSvc/MyDBServer.mydomain.com:1433 MyDomain\SQLServerService
now when I run SETSPN - L MyDBServer I get:
Host/MyDBServer
HOST/MyDBServer.mydomain.com
and for SETSPN - L MyDomain\SQLServerService I get:
MSSQLSvc/MyDBServer.mydomain.com
MSSQLSvc/MyDBServer.mydomain.com:1433
MSSQLSvc/MyDBServer
MSSQLSvc/MyDBServer:1433
I am a little confused, one thing I know is that the MyDomain\SQLServerService is locked down quite heavily via gpedit, it can logon as a service and lock pages in memory but not much more. I'm wondering if it needs a specific permission of some sort?
I can see that sessions are still using NTLM, any ideas as to what is nobbling me?
Thanks,
Carlton..
The lockdown shouldn't affect anything. Are the clients in the same domain as the SQL Server? I'm assuming yes, but want to confirm. The reason I ask is that when you cross forests (not domains, but we'll start with the simplest model) you have to set up a forest level trust to do Kerberos authentication across.
Also, is this for a default instance listening on the default port of 1433? If so, those SPNs are correct.
K. Brian Kelley
@kbriankelley
April 30, 2009 at 2:43 pm
K. Brian Kelley (4/30/2009)
Carlton Leach (4/29/2009)
Hi Brian, Great article I agree.I am having trouble getting Kerberos set up tho.
I am using:
- a Virtual Machine (VMWare)
- sql 2005 SP3
I have run the 4 steps adived:
SETSPN -A MSSQLSvc/MyDBServer MyDomain\SQLServerService
SETSPN -A MSSQLSvc/MyDBServer:1433 MyDomain\SQLServerService
SETSPN -A MSSQLSvc/MyDBServer.mydomain.com MyDomain\SQLServerService
SETSPN -A MSSQLSvc/MyDBServer.mydomain.com:1433 MyDomain\SQLServerService
now when I run SETSPN - L MyDBServer I get:
Host/MyDBServer
HOST/MyDBServer.mydomain.com
and for SETSPN - L MyDomain\SQLServerService I get:
MSSQLSvc/MyDBServer.mydomain.com
MSSQLSvc/MyDBServer.mydomain.com:1433
MSSQLSvc/MyDBServer
MSSQLSvc/MyDBServer:1433
I am a little confused, one thing I know is that the MyDomain\SQLServerService is locked down quite heavily via gpedit, it can logon as a service and lock pages in memory but not much more. I'm wondering if it needs a specific permission of some sort?
I can see that sessions are still using NTLM, any ideas as to what is nobbling me?
Thanks,
Carlton..
The lockdown shouldn't affect anything. Are the clients in the same domain as the SQL Server? I'm assuming yes, but want to confirm. The reason I ask is that when you cross forests (not domains, but we'll start with the simplest model) you have to set up a forest level trust to do Kerberos authentication across.
Also, is this for a default instance listening on the default port of 1433? If so, those SPNs are correct.
Hi Brian,
Thanks for the swift response, Yes this is a default SQL instance listening on port 1433.
And yes the clients are in the same domain (I am one of them).
I am waiting for windows lot to come back to me with what type of domain (2k or 2k03 etc) but I don't think this is going to matter.
I have also asked if there is a specific setting (possibly group policy) that is going to prevent authentication via kerberos.
One question: Are these SPN's going to have any kind of detrimental affect on anything if I do not remove them?
Thanks again,
Carlton..
April 30, 2009 at 3:59 pm
They should not have a detrimental effect if you remove them. You might use kerbtray to see if the tickets are even being created/assigned.
K. Brian Kelley
@kbriankelley
May 19, 2009 at 3:56 pm
Great article!
Any chance of another great article for Kerberos Delegation?
Viewing 15 posts - 46 through 60 (of 89 total)
You must be logged in to reply to this topic. Login to reply