Click here to monitor SSC
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
Valued Member
Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)Valued Member (70 reputation)

Group: General Forum Members
Points: 70 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 (6.8K reputation)

Group: Moderators
Points: 6814 Visits: 1916
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
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8784 Visits: 16558
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
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1150 Visits: 1301
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
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8784 Visits: 16558
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
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1150 Visits: 1301
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
SSCrazy Eights
SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)SSCrazy Eights (8.8K reputation)

Group: General Forum Members
Points: 8784 Visits: 16558
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
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1150 Visits: 1301
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