Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Register SPN for SQL Service account Expand / Collapse
Author
Message
Posted Friday, March 14, 2014 9:20 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: 2 days ago @ 8:28 AM
Points: 54, Visits: 1,294
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.
Post #1551205
Posted Tuesday, March 18, 2014 11:51 AM


Keeper of the Duck

Keeper of the Duck

Group: Moderators
Last Login: Friday, September 26, 2014 7:52 AM
Points: 6,624, Visits: 1,873
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, CISA, MCSE, Security+, MVP - SQL Server
Regular Columnist (Security), SQLServerCentral.com
Author of Introduction to SQL Server: Basic Skills for Any SQL Server User
| Professional Development blog | Technical Blog | LinkedIn | Twitter
Post #1552339
Posted Tuesday, March 25, 2014 9:26 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:26 AM
Points: 6,468, Visits: 13,922
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"
Post #1554540
Posted Wednesday, April 16, 2014 1:52 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:01 AM
Points: 1,054, Visits: 1,131
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?
Post #1562162
Posted Wednesday, April 16, 2014 6:00 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:26 AM
Points: 6,468, Visits: 13,922
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"
Post #1562249
Posted Wednesday, April 16, 2014 9:23 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:01 AM
Points: 1,054, Visits: 1,131
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?
Post #1562355
Posted Thursday, April 17, 2014 9:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 4:26 AM
Points: 6,468, Visits: 13,922
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"
Post #1562707
Posted Friday, April 18, 2014 1:45 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 3:01 AM
Points: 1,054, Visits: 1,131
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!
Post #1562932
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse