SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Register SPN for SQL Service account


Register SPN for SQL Service account

Author
Message
Grace09
Grace09
SSC-Enthusiastic
SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)SSC-Enthusiastic (114 reputation)

Group: General Forum Members
Points: 114 Visits: 1710
I am getting the following error message,

SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure.

To resolve the issue, how should I register SPN for the sql service account? This is a cluster server using a named instance and dynamic port. Thanks.
K. Brian Kelley
K. Brian Kelley
Keeper of the Duck
Keeper of the Duck (10K reputation)

Group: Moderators
Points: 10248 Visits: 1917
If it's SQL Server 2008 or higher, use the Instance Name for the port. A Domain Admin will need to use setspn to put in the SPN:

http://technet.microsoft.com/en-us/library/ms191153.aspx

K. Brian Kelley
@‌kbriankelley
Perry Whittle
Perry Whittle
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19980 Visits: 17243
Do you have permission within AD to create SPNs?
If not you'll need a domain admin to do this for you.


The instance name in the SPN registration is only for connections other than TCP\IP. These are shared memory and named pipes, which typically you should disable as a best practice. Also, Shared memory is a local only connection protocol anyway.

Check firstly for any duplicates using
SETSPN -X



and remove them, then register the SPN against the clustered instance virtual networkname.

For TCP\IP connections to a clustered named instance SQLCLUST01\INST1 running under account mydomain\sql-srv using TCP port 58001 you would use

setspn –A MSSQLSvc/SQLCLUST01.mydomain.com:58001 mydomain\sql-srv
setspn –A MSSQLSvc/SQLCLUST01:58001 mydomain\sql-srv



-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
Andy sql
Andy sql
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1605 Visits: 1315
The instance name in the SPN registration is only for connections other than TCP\IP. These are shared memory and named pipes.....


I've been a bit confused on this point! The guidance from Microsoft seems contradictory:

Quote 1: "The new SPN format does not require a port number. This means that a multiple-port server or a protocol that does not use port numbers can use Kerberos authentication."

Quote 2: "MSSQLSvc/fqdn:InstanceName The provider-generated, default SPN for a named instance when a protocol other than TCP is used."

To me, the first statement says it does work with TCP/IP (in the case where MSSQL is listening on multiple ports). But the second statement says it only works for non TCP connections.

Can you help clarify?
Perry Whittle
Perry Whittle
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19980 Visits: 17243
the first question is, which protocol are you using to accept connections to your sql server, which protocols do you have bound?
from memory IPX\SPX would not be able to provide Kerberos auth, unless you use the new SPN format which allows the instance name.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
Andy sql
Andy sql
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1605 Visits: 1315
You've got a long memory! I think the last time I used the IPX/SPX protocol was playing Red Alert on a small private network.

I don't have a particular server I need to configure, was more a general question about SPNs. The guidance suggests that an SPN with an Instance Name can be used for TCP/IP connections; the SPN can alternatively be configured with a Port Number. Just wondering which is the better option?
Perry Whittle
Perry Whittle
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19980 Visits: 17243
No, I think the SPN formats section is getting people confused, it's an either\or situation. If you check the material it's detailed that when using TCP an SPN is always created as follows for both named and default instances

MSSQLSvc/FQDN:[port]


Named pipes and shared memory use

MSSQLSvc/FQDN:instancename is used for a named instance
MSSQLSvc/FQDN is used for the default instance.



The following details the above

http://technet.microsoft.com/en-us/library/ms191153.aspx
MSSQLSvc/fqdn:port
The provider-generated, default SPN when TCP is used. port is a TCP port number.

MSSQLSvc/fqdn
The provider-generated, default SPN for a default instance when a protocol other than TCP is used. fqdn is a fully-qualified domain name.

MSSQLSvc/fqdn:InstanceName
The provider-generated, default SPN for a named instance when a protocol other than TCP is used. InstanceName is the name of an instance of SQL Server.


-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" ;-)
Andy sql
Andy sql
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1605 Visits: 1315
Thanks for clarifying the issue, and I'm sure your correct. I was just kinda hoping that embedding the Instance Name in the SPN would be a work-around for dynamic TCP ports!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search