GAPTHEGURU

Geek with special skills

KERBEROS CONFIGURATION – SPN USAGE

What SPN do I use and how does it get there?

This month has turned into an Kerberos Month for me.   I thought I would share my response to the questions as it will probably be helpful for someone.  Here was the comment that started the conversation.  And, by the way, this was actually a good question.  I actually see this kind of comment a lot in regards to SPN placement.  Not necessarily the setup aspect of it, but for SPN’s in general.

“In prior versions of setup we used to be able to specify the port number for the default and Named Instance.  Now, (SQL 2008 & R2) it takes the defaults.  1433 and Dynamic for Named Instances.

If you want to use Kerberos with TCP, you need to know the port number to create the SPN.  For Default instances, if you’re using 1433 then you’re ok. But, Named Instances listen on a dynamic port by default, and since you can’t set the port number, any SPN you create will probably be wrong and Kerberos won’t work.  It would be great if we could ask the user if they want to change the port number during setup, like we did with SQL 2000.”

Let’s have a look at Books Online first.

Registering a Service Principal Name     http://msdn.microsoft.com/en-us/library/ms191153.aspx

This article goes through the different formats that are applicable to SQL 2008 (they are the same for R2 as well).  It also touches on two items that are important to understand.  1.  Automatic SPN Registration and 2. Client Connections. Here is the excerpt from the above article in regards to Automatic SPN Registration.

Automatic SPN Registration

When an instance of the SQL Server Database Engine starts, SQL Server tries to register the SPN for the SQL Server service. When the instance is stopped, SQL Server tries to unregister the SPN. For a TCP/IP connection the SPN is registered in the format MSSQLSvc/<FQDN>:<tcpport>.Both named instances and the default instance are registered as MSSQLSvc, relying on the <tcpport> value to differentiate the instances.For other connections that support Kerberos the SPN is registered in the format MSSQLSvc/<FQDN>:<instancename> for a named instance. The format for registering the default instance is MSSQLSvc/<FQDN>.

Manual intervention might be required to register or unregister the SPN if the service account lacks the permissions that are required for these actions.

What does this mean?  It means that if the SQL Service account is using Local System or Network Service as the logon account, we will have the permission necessary to register the SPN against the Domain Machine Account.  By default, the machine accounts have permission to modify themselves.  If we change this over to a Domain User Account for the SQL Service account, things change a little.  By default a Domain User does not have the permission required to create the SPN.  So, when you start SQL Server with a Domain User Account, you will see an entry in your ERRORLOG similar to the following:

2010-03-05 09:39:53.20 Server      The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x2098, state: 15. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.This permission is called “Write servicePrincipalName” and can be altered through an MMC snap in called ADSI Edit.  For instructions on how to modify this setting, refer to Step 3 in the following KB Article.  WARNING:  I do NOT recommend you do this on a Cluster.  We have seen issues with this causing connectivity issues due to Active Directory Replication issues if more than one Domain Controller is used in your environment.

How to use Kerberos authentication in SQL Server     http://support.microsoft.com/kb/319723

clip_image002

So, if I enable that permission, lets see what the SQL Service does.  I have two machines I’m going to use for this.  ASKJCTP3 (running the RC build of 2008 R2) and MySQLCluster (SQL 2008 running a Named Instance called SQL2K8).

SetSPN Details:

SPN’s with TCP and NP enabled on Default Instance:

C:\>setspn -l sqlservice      Registered ServicePrincipalNames for CN=SQL Service,OU=Services,DC=dsdnet,DC=local:               MSSQLSvc/ASKJCTP3.dsdnet.local:1433               MSSQLSvc/ASKJCTP3.dsdnet.local

SPN’s with only NP enabled on Default Instance:C:\>setspn -l sqlservice      Registered ServicePrincipalNames for CN=SQL Service,OU=Services,DC=dsdnet,DC=local:               MSSQLSvc/ASKJCTP3.dsdnet.local

SPN’s with TCP and NP enabled on Clustered Named Instance: C:\>setspn -l sqlservice      Registered ServicePrincipalNames for CN=SQL Service,OU=Services,DC=dsdnet,DC=local:               MSSQLSvc/MYSQLCLUSTER.dsdnet.local:54675               MSSQLSvc/MYSQLCLUSTER.dsdnet.local:SQL2K8

SPN’s with only NP enabled on a Clustered Named Instance:C:\>setspn -l sqlservice      Registered ServicePrincipalNames for CN=SQL Service,OU=Services,DC=dsdnet,DC=local:               MSSQLSvc/MYSQLCLUSTER.dsdnet.local:SQL2K8

Lets look at what the client will do.  When I say client, this could mean a lot of different things.  Really it means an Application trying to connect to SQL Server by way of a Provider/Driver.  NOTE:  Specifying the SPN as part of the connection is specific to SQL Native Client 10 and later.  It does not apply to SqlClient or the Provider/Driver that ships with Windows.Service Principal Name (SPN) Support in Client Connections     http://msdn.microsoft.com/en-us/library/cc280459.aspx

Based on this, if I have a straight TCP connection, the Provider/Driver will use the Port for the SPN designation.  Let’s see what happens when I try to make connections using a UDL file.  For the UDL I’m going to use the SQL Native Client 10 OleDb Provider.  Starting with SNAC10, we can specify which SPN to use for the connection.  This provides us some flexibility when we control how the application is going to connect.  Note:  This is not available with the Provider/Driver that actually ship with Windows.  I also will show what the Kerberos request looks like in the network trace.  This will show us, what SPN is actually being used.  All of these connection attempts were made using ASKJCTP3 which is a Default Instance.

Being this is a Default Instance, I added the Instance Name SPN manually.

C:\>setspn -l sqlservice      Registered ServicePrincipalNames for CN=SQL Service,OU=Services,DC=dsdnet,DC=local:               MSSQLSvc/ASKJCTP3.dsdnet.local:MSSQLSERVER               MSSQLSvc/ASKJCTP3.dsdnet.local:1433               MSSQLSvc/ASKJCTP3.dsdnet.local               MSSQLSvc/MYSQLCLUSTER.dsdnet.local:54675               MSSQLSvc/MYSQLCLUSTER.dsdnet.local:SQL2K8

Straight TCP with no SPN Specified:

clip_image002[5]

58     1.796875   {TCP:7, IPv4:5}      10.0.0.3      10.0.0.1      KerberosV5    KerberosV5:TGS Request Realm: DSDNET.LOCAL Sname: MSSQLSvc/askjctp3.dsdnet.local:1433

TCP with specifying an SPN for the connection:

clip_image004

32     1.062500   {TCP:11, IPv4:5}     10.0.0.3      10.0.0.1      KerberosV5    KerberosV5:TGS Request Realm: DSDNET.LOCAL Sname: MSSQLSvc/ASKJCTP3.dsdnet.local:MSSQLSERVER

Forcing Named Pipes with no SPN specified:

clip_image006

68     1.828125   {TCP:21, IPv4:5}     10.0.0.3      10.0.0.1      KerberosV5    KerberosV5:TGS Request Realm: DSDNET.LOCAL Sname: MSSQLSvc/askjctp3.dsdnet.local

The way the provider/driver determines which SPN to use is based on the Protocol being used.  Of note, starting in SQL 2008 we allowed for Kerberos to be used with Named Pipes.  If you have a Named Instance and you are using the Named Pipes protocol, we will look for an SPN with the Named Instance specified.  For a Default Instance and Named Pipes, we will just look for the SPN with no port or Named Instance Name specified as shown above.

With the ability to specify the SPN from the client side, you can see how you can easily manipulate, or even see how we will determine what SPN will be used.

Now that we know all of the above, lets go back to the original question.  Your company may or may not want to enable the Write permission for the Domain User Account.  If your company is not willing to open up the permission on the service account, then their only recourse will be to set a static port for the Named Instance instead of letting the Named Instance use a dynamic port.  This would also be my recommendation for Clusters.  In this case, you will need to know exactly what SPN’s are needed and create them manually using SetSPN or tool of your choice.

Even though we don’t provide the ability to set your port during setup, you can still modify the port settings for the Instance through the SQL Server Configuration Manager.  This will allow you to set your static SPN’s as well as assist you with Firewall rules.

image

image

Advertisements

12/09/2011 Posted by | Delegation, Kerberos, Security | , | Leave a comment

If you have more than 3 nodes in a Server 2008 Failover Cluster and upgrade to 2008 R2 SP1 you might get some problem. HOTFIX is available.

Validate SCSI Device Vital Product Data (VPD) test fails after you install Windows Server 2008 R2 SP1

Hotfix Download Available View and request hotfix downloads//

SYMPTOMS

//

Consider the following scenario:

  • You configure a failover cluster that has three or more nodes that are running Windows Server 2008 R2 Service Pack 1 (SP1).
  • You have cluster disks that are configured in groups other than the Available Storage group or that are used for Cluster Shared Volumes (CSV).
  • These disks are online when you run the Validate SCSI Device Vital Product Data (VPD) test or the List Potential Cluster Disks storage validation test.

In this scenario, the Validate SCSI Device Vital Product Data (VPD)test fails. Additionally, you receive an error message that resembles the following:

Failed to get SCSI page 83h VPD descriptors for cluster disk <number> from <node name> status 2

The List Potential Cluster Disksstorage validation test may display a warning message that resembles the following:

Disk with identifier <value> has a Persistent Reservation on it.  The disk might be part of some other cluster.  Removing the disk from validation set.

RESOLUTION

The following hotfix resolves an issue in which the storage test incorrectly run…

//

The following hotfix resolves an issue in which the storage test incorrectly runs on disks that are online and not in the Available Storage group.
The error and warning messages that are mentioned in the “Symptoms” section may also occur because of other issues such as storage problems or an incorrect configuration. Therefore, you should investigate other events, check the storage configuration, or contact your storage vendor if this issue still occurs after you install the following hotfix.

Hotfix information

// A supported hotfix is available from Microsoft. However, this hotfix is intended to correct only the problem that is described in this article. Apply this hotfix only to systems that are experiencing this specific problem.
If the hotfix is available for download, there is a “Hotfix download available” section at the top of this Knowledge Base article. If this section does not appear, submit a request to Microsoft Customer Service and Support to obtain the hotfix.
NoteIf additional issues occur or if any troubleshooting is required, you might have to create a separate service request. The usual support costs will apply to additional support questions and issues that do not qualify for this specific hotfix. For a complete list of Microsoft Customer Service and Support telephone numbers or to  create a separate service request, visit the following Microsoft Web site:
NoteThe “Hotfix download available” form displays the languages for which the hotfix is available. If you do not see your language, it is because a hotfix is not available for that language.

Prerequisites

// To apply this hotfix, you must be running Windows Server 2008 R2 Service Pack 1 (SP1).

Registry information

// To use the hotfix in this package, you do not have to make any changes to the registry.

Restart requirement

// You do not have to restart the computer after you apply this hotfix.

Hotfix replacement information

// This hotfix does not replace a previously released hotfix.

File information

// The global version of this hotfix installs files that have the attributes that are listed in the following tables. The dates and the times for these files are listed in Coordinated Universal Time (UTC). The dates and the times for these files on your local computer are displayed in your local time together with your current daylight saving time (DST) bias. Additionally, the dates and the times may change when you perform certain operations on the files.
Windows Server 2008 R2 file information notes
// ImportantWindows 7 hotfixes and Windows Server 2008 R2 hotfixes are included in the same packages. However, hotfixes on the Hotfix Request page are listed under both operating systems. To request the hotfix package that applies to one or both operating systems, select the hotfix that is listed under “Windows 7/Windows Server 2008 R2” on the page. Always refer to the “Applies To” section in articles to determine the actual operating system that each hotfix applies to.
  • The files that apply to a specific product, SR_Level (RTM, SPn), and service branch (LDR, GDR) can be identified by examining the file version numbers as shown in the following table.
    Collapse this tableExpand this table
    Version Product SR_Level Service branch
    6.1.760 1. 17xxx Windows Server 2008 R2 SP1 GDR
    6.1.760 1. 21xxx Windows Server 2008 R2 SP1 LDR
  • The MANIFEST files (.manifest) and the MUM files (.mum) that are installed for each environment are listed separately in the “Additional file information for Windows Server 2008 R2” section. MUM and MANIFEST files, and the associated security catalog (.cat) files, are extremely important to maintain the state of the updated components. The security catalog files, for which the attributes are not listed, are signed with a Microsoft digital signature.
For all supported x64-based versions of Windows Server 2008 R2
//
Collapse this tableExpand this table
File name File version File size Date Time Platform
Failoverclusters.agent.interop.dll 6.1.7601.17514 11,776 20-Nov-2010 13:39 x64
Failoverclusters.validation.bestpracticetests.dll 6.1.7601.21710 131,072 22-Apr-2011 19:59 x86
Failoverclusters.validation.common.dll 6.1.7601.21710 15,872 22-Apr-2011 19:59 x86
Failoverclusters.validation.generaltests.dll 6.1.7601.21710 278,528 22-Apr-2011 19:59 x86
Failoverclusters.validation.storagetests.dll 6.1.7601.21710 165,376 22-Apr-2011 19:59 x64
Failoverclusters.validation.wizard.dll 6.1.7601.21710 163,840 22-Apr-2011 19:59 x86
For all supported IA-64-based versions of Windows Server 2008 R2
//
Collapse this tableExpand this table
File name File version File size Date Time Platform
Failoverclusters.agent.interop.dll 6.1.7601.17514 11,776 20-Nov-2010 10:35 IA-64
Failoverclusters.validation.bestpracticetests.dll 6.1.7601.21710 131,072 22-Apr-2011 18:56 x86
Failoverclusters.validation.common.dll 6.1.7601.21710 15,872 22-Apr-2011 18:56 x86
Failoverclusters.validation.generaltests.dll 6.1.7601.21710 278,528 22-Apr-2011 18:56 x86
Failoverclusters.validation.storagetests.dll 6.1.7601.21710 178,176 22-Apr-2011 18:56 IA-64
Failoverclusters.validation.wizard.dll 6.1.7601.21710 163,840 22-Apr-2011 18:56 x86

Additional file information

//

Additional file information for Windows Server 2008 R2

//

Additional files for all supported x64-based versions of Windows Server 2008 R2

//

Collapse this tableExpand this table
File name Amd64_c2ba436ef182e0a5928c7085a1c15a1c_31bf3856ad364e35_6.1.7601.21710_none_9ab972a7d49f9cf5.manifest
File version Not applicable
File size 718
Date (UTC) 22-Apr-2011
Time (UTC) 22:46
File name Amd64_microsoft-windows-f..rcluster-validation_31bf3856ad364e35_6.1.7601.21710_none_8391722feb62e788.manifest
File version Not applicable
File size 6,157
Date (UTC) 22-Apr-2011
Time (UTC) 22:57
Additional files for all supported IA-64-based versions of Windows Server 2008 R2

//

Collapse this tableExpand this table
File name Ia64_10d0cccfb7f4951daa8f04e3b22712cd_31bf3856ad364e35_6.1.7601.21710_none_00d442193a2cb4e4.manifest
File version Not applicable
File size 716
Date (UTC) 22-Apr-2011
Time (UTC) 22:46
File name Ia64_microsoft-windows-f..rcluster-validation_31bf3856ad364e35_6.1.7601.21710_none_27747aa233037f4e.manifest
File version Not applicable
File size 6,155
Date (UTC) 22-Apr-2011
Time (UTC) 22:46

12/09/2011 Posted by | Uncategorized, Windows Server | Leave a comment

   

%d bloggers like this: