March 14, 2014 at 9:20 am
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.
March 18, 2014 at 11:51 am
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
March 25, 2014 at 9:26 am
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" 😉
April 16, 2014 at 1:52 am
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?
April 16, 2014 at 6:00 am
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" 😉
April 16, 2014 at 9:23 am
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?
April 17, 2014 at 9:09 am
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:portThe 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" 😉
April 18, 2014 at 1:45 am
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!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply