set up SPN in sql 2005

Question

set up SPN in sql 2005

Mike Levan

SSChampion

Points: 14201
More actions
December 15, 2008 at 2:17 pm

#126535

I am using this for creating SPN for sql server 2005.
setspn -A MSSQLSvc/hq-dbadmin01.mydomain.com:1433 MYDOMAIN\sql.service.
is there any change in creating spn for sql server 2000 and 2005.
thanks

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Kendal Van Dyke SSCertifiable Points: 5972 More actions · Answer 1

Mike,

Check out these two blog posts for more info about SPNs. You run setspn on the command line and typically your domain admin does it (or you, if you've got domain admin rights)

http://kendalvandyke.blogspot.com/2008/11/delegation-what-it-is-and-how-to-set-it.html

http://kendalvandyke.blogspot.com/2008/11/delegation-more-on-service-principal.html

Kendal Van Dyke
http://kendalvandyke.blogspot.com/[/url]

Kendal Van Dyke SSCertifiable Points: 5972 More actions · Answer 2

Oh and the syntax is the same for SQL2000 as it is for SQL2005; a SPN is an AD thing so it's not specific to a SQL version.

Kendal Van Dyke
http://kendalvandyke.blogspot.com/[/url]

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 3

You also probably want to create an SPN without the port for a default instance on SQL Server 2000/2005. In SQL Server 2008, this is most certainly the case if you want to support other network libraries such as Named Pipes. SQL Server 2008 will support Kerberos across more than just TCP/IP.

And just one small point of clarification. Technically an SPN is a Kerberos thing. AD uses Kerberos and stores the SPNs, but SPNs are part of the Kerberos protocol. 🙂

K. Brian Kelley
@kbriankelley

Kendal Van Dyke SSCertifiable Points: 5972 More actions · Answer 4

I guess what I meant to say was that SPNs are a "something outside of SQL" thing. Thanks for keeping me honest Brian.

Kendal Van Dyke
http://kendalvandyke.blogspot.com/[/url]

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 5

kendal.vandyke (12/16/2008)
I guess what I meant to say was that SPNs are a "something outside of SQL" thing. Thanks for keeping me honest Brian.

I guess I'm a little touchy about it because about a year ago we had developers complaining about all this Kerberos stuff that "Microsoft came up with." It took a while to get them to understand that Microsoft didn't come up with Kerberos, MIT did, and that it wasn't just in Active Directory (google on heimdal for those who weren't aware of this).

K. Brian Kelley
@kbriankelley

Mike Levan SSChampion Points: 14201 More actions · Answer 6

Guys.. its driving nuts for me, though i setup authentication to KERBEROS still i cant run query from from servrB to servrC when logged into servrA

Here is my setup

Logged in ServrA and ran this query connecting to all 3 servers
select net_transport, auth_scheme from sys.dm_exec_connections where session_id=@@spid
Query from ServrA
net_transport=Shared memory, auth_scheme=NTLM
Query from ServrB
net_transport=TCP , auth_scheme=KERBEROS
Query from ServrC
net_transport=TCP , auth_scheme=KERBEROS

Ita the same thing when connected all the 3 servers. Logged into ServrA and connecting to ServrA gives NTLM when connected ot other 2 gives KERBEROS.

I am just wondering if this is the cause for failing my cross server queries.

Kendal Van Dyke SSCertifiable Points: 5972 More actions · Answer 7

Have you:

- Checked delegation settings in AD for Server B and the domain account SQL is running as on Server B?

- Created SPNs for Server B and Server C?

- Restarted the SQL Service on Server B and Server C after creating the SPNs?

Kendal Van Dyke
http://kendalvandyke.blogspot.com/[/url]

Mike Levan SSChampion Points: 14201 More actions · Answer 8

I checked delegation on all 3 servers it returns "1" to me that means it is active.

When checked all the login sessions i see that they still use NTLM except my domain username which uses KERBEROS. Also restarted sql service as and when i changed settings

what shud i do now.

Kendal Van Dyke SSCertifiable Points: 5972 More actions · Answer 9

By checked delegation do you mean you went into the AD Users and Computers console?

And you used the setspn utility to create the SPN, right?

(Sorry, just trying to be clear to understand where the problem might be)

Kendal Van Dyke
http://kendalvandyke.blogspot.com/[/url]

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 10

Are you using the FQDN in all connection strings? I know - stupid question, but have to ask it anyways.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

Mike Levan SSChampion Points: 14201 More actions · Answer 11

For delegation i checked with this query

select uses_self_credential as delegation

from sys.linked_logins as L, sys.servers as S

where S.server_id=L.server_id and S.name=N'LinkSErver'

Yes i did setup spn with setspn utility from cmdline.

Kendal Van Dyke SSCertifiable Points: 5972 More actions · Answer 12

I would double check that delegation is set up for the servers in AD using the AD Users and Computers snapin.

Kendal Van Dyke
http://kendalvandyke.blogspot.com/[/url]

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 13

Is everything (SQL Servers and user accounts) all in the same domain?

K. Brian Kelley
@kbriankelley

Mike Levan SSChampion Points: 14201 More actions · Answer 14

Mike Levan

SSChampion

Points: 14201

December 19, 2008 at 11:39 am

#915524

Yes all are in the same domain.