November 16, 2011 at 8:13 am
Im using the following command on my sql server.
setspn -A MSSQLSvc\Srv1:instance1 DOMAIN\ServiceAccount1
when i run this i get a message telling me:
MSSQLSvc/Srv1:instance1 Updated Object
Yet, when i query the SPN's for that server, it does not show up.
I query all SPN's on the server via:
setspn -L Srv1
Does it take time for an SPN to actually show up after creation? If so, is there any way i can speed this up?
November 16, 2011 at 8:22 am
It does potentially take time. It gets written to one DC and then must be replicated. If the DC you wrote to and the DC you query aren't the same, you will see a discrepancy until the replication happens. Typically, for intrasite DCs, this should be on the order of seconds at most. However, this is a question to ask your AD admins.
K. Brian Kelley
@kbriankelley
November 16, 2011 at 8:23 am
Ah, also, you're querying the wrong info. You want to do a -L against the service account. The SPN will belong under the service account.
K. Brian Kelley
@kbriankelley
November 16, 2011 at 8:29 am
K. Brian Kelley (11/16/2011)
Ah, also, you're querying the wrong info. You want to do a -L against the service account. The SPN will belong under the service account.
cool, i see it now. thanks for that. ive restarted the sql server service, but im still only getting an NTLM connection when i connect. are there any other steps i need to perform?
November 16, 2011 at 8:35 am
If it's a cluster and you're connecting from a cluster node, only expect NTLM.
Otherwise, make sure that your clients are connecting to SQL Server based on the name you specified for the server in the SPN. Make sure they're coming in from a trusted domain with transitive trusts within the same forest or across forests where a proper forest-level trust is in place (obviously, if users, workstations, and SQL Server are all in the same domain, you meet this requirement). Also make sure they are connecting via TCP/IP.
K. Brian Kelley
@kbriankelley
November 16, 2011 at 9:00 am
Its not a cluster, its a standalone pc.
Connecting from my desktop pc, which is on the same domain.
My account is a domain account.
I have checked the connection and it is created via TCP ( ive also disabled all other protocols to be sure).
I thought maybe i created the SPN wrong and so tried the trick of assigning the sql server service account to the Domain Admins group and restarting the SQL Service ( not very security savy, but its just a quick test). still getting NTLM over TCP/IP.
I ended up logging out of my pc and relogging in, and when i re-connected it was kerberos.
i wonder if i done something wrong in the SPN setup. Thanks for the help.
November 16, 2011 at 9:05 am
It might just have taken a bit of time for it to replicate. We used to wait about 15 minutes after the SPN was put in. Since we're on 2008 AD with the faster intrasite replication, we don't wait as long now.
K. Brian Kelley
@kbriankelley
November 16, 2011 at 9:15 am
just observed something odd.
When i created the SPN it was created like this:
MSSQLSvc/Srv1:Instance1
When I added the sql account to the domain admins and restarted it, it created the SPN like this:
MSSQLSvc/Srv1.domain.ie:27754
Why wouldnt it use the actual instance name?
If i restart SQL Server and a new port is assigned to the instance will Kerberos break?
November 16, 2011 at 9:20 am
winston Smith (11/16/2011)
just observed something odd.When i created the SPN it was created like this:
MSSQLSvc/Srv1:Instance1
When I added the sql account to the domain admins and restarted it, it created the SPN like this:
MSSQLSvc/Srv1.domain.ie:27754
Why wouldnt it use the actual instance name?
If i restart SQL Server and a new port is assigned to the instance will Kerberos break?
If you drop the service account out of domain admins, yes. You want to drop the service account out of domain admins.
The reason that it's doing by port is because that's probably a hold over in code from earlier versions. Prior to 2008, you had to specify the port as the instance didn't work. I typically will statically set the port on a named instance and then manually use the port to create the SPN, even if I do it for a SQL 2008 or 2008R2 box. For me it's about consistency in the environment.
K. Brian Kelley
@kbriankelley
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply