none
SetSPN, Kerberos and named instances

    Question

  • We are trying to troubleshoot some Kerberos issues with our SQL server cluster. We currently are having issues with queries which use BULK INSERT from clients, because the auth_scheme is showing up as NTLM.

    We have a cluster, which is running a named instance of SQL server. Let's call the cluster CLUSTER1, the instance PRODSQL1, and the FQDN CLUSTER1.DOMAIN.COM. The names of the machines in the cluster are SQLMACHINE1 and SQLMACHINE2. Sql Server is running under the user DOMAIN\!serviceSQL

    Questions:

    1) Is Kerberos the only way that we can get BULK INSERT statements working from a client connection to SQL server, if the BULK INSERT is importing files off of a separate file server? i.e. the importing from a share \\FILESERVER\Share1?

    2) The SQL instance PRODSQL1 is set to dynamic ports under the SQL Server Configuration. If we're using Kerberos in order to support BULK INSERTS, is it a better idea to set a static port for that instance?

    3) What's the difference between setting a SPN using a port vs. not specifying a port? From what I have read, it appears that if you don't specify a port when using setspn, it's the same as using the default port for that service. For example, in the case of SQL server
     setspn -A MSSQLSvc/CLUSTER1 DOMAIN\!serviceSQL
    is the same as:
     setspn -A MSSQLSvc/CLUSTER1:1433 DOMAIN\!serviceSQL

    4) Is there any reason to use dynamic ports for named instances?

    5) We also tried leaving the named instance as a dynamic port and then using the following syntax with setspn:
    setspn -A MSSQLSvc/CLUSTERNAME:PRODSQL1 DOMAIN\!serviceSQL
    according to this article:
    http://blogs.msdn.com/b/psssql/archive/2010/03/09/what-spn-do-i-use-and-how-does-it-get-there.aspx

    but it did not appear to allow us to connect using kerberos. We tested connecting to the cluster using SQL Management Studio and checking the authentication for connection. It was still NTLM.

    6) Is there anyway to change the SPN's for sql server and not have to restart the server to see the effect?

    Note that after each set of changes to the SPN's in our system, we restarted the sql server.

     

     

    Monday, March 07, 2011 9:23 PM

Answers

  • Hi,

    1) Is Kerberos the only way that we can get BULK INSERT statements working from a client connection to SQL server, if the BULK INSERT is importing files off of a separate file server? i.e. the importing from a share \\FILESERVER\Share1?

    If you log on to SQL Server using a domain account that will uses Windows Authentication Mode, therefore SQL Server process will inpersonate the logged user security context to read the files in Bulk Insert operation. Therefore, you may need to configure SQL Server cluster trusted for delegation and Kerberos authentication for SQL Server. For more information, please see: Security Account Delegation (Impersonation) http://msdn.microsoft.com/en-us/library/ms188365.aspx.

    2) The SQL instance PRODSQL1 is set to dynamic ports under the SQL Server Configuration. If we're using Kerberos in order to support BULK INSERTS, is it a better idea to set a static port for that instance?

    It is recommended to use a static port. If SQL Server is listening on dynamic TCP port, SQL Server may change its listening port after each restart, therefore we need to delete existing SPN and register a new SPN match for new port for SQL Server service account. Also, we need to reconfigure all blocked programs to allow new port.

    3) What's the difference between setting a SPN using a port vs. not specifying a port? From what I have read, it appears that if you don't specify a port when using setspn, it's the same as using the default port for that service. For example, in the case of SQL server
    setspn -A MSSQLSvc/CLUSTER1 DOMAIN\!serviceSQL
    is the same as:
    setspn -A MSSQLSvc/CLUSTER1:1433 DOMAIN\!serviceSQL

    If you register a SPN not specifying a TCP port, it only suports non TCP/IP protcols, Named Pipe or Shared Memory. If connecting to SQL Server using TCP/IP protocol, we neeed to register a SNP with port. The format should be like this:

    • MSSQLSvc/CLUSTER1.DOMAIN.COM:1433 DOMAIN\serviceSQL for TCP/IP protocol
    • MSSQLSvc/CLUSTER1.DOMAIN.COM:PRODSQL1 DOMAIN\serviceSQL for Named Pipe and Shared Memory

    For more information about registering a SPN for SQL Server, please refer to http://msdn.microsoft.com/en-us/library/ms191153.aspx.

    For other question, you could refer to Ben's reply and this reply accordingly.

    Hope this helps.


    Best Regards,
    Chunsong Feng

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Tuesday, March 08, 2011 9:08 AM
    Moderator

All replies

  • I don't have the answers to all your questions but I think you need one SPN for the NETBIOS name and one for the FQDN so in addition to what you have here you also need:

    MSSQLSvc/CLUSTER1.DOMAIN.COM:1433 DOMAIN\serviceSQL

    (if you are specifying the port)

    Also to partially answer 3), I believe that the port is necessary if your applications specify the port explicitly when they connect.

    4) Dynamic ports mean you don't have to allocate a port number that might potentially be in use by some other application on the server. SQL picks a port when it first starts and tries to use that if its available. If it isn't it tries another one. This is not possible if the port is static.

    6) I don't believe so.

    Have you actually ascertained that SQL is unable to set the SPN automatically? i.e. do you see something in the error log when it starts up saying something like 'unable to register spn, users may have to use NTLM'? If you are you can also try giving the sql service account full control permissions over the computer account for the cluster (i.e. virtual) computer account. By coincidence I had to do this today and it fixed my kerberos problem!

    HTH

    Ben

    Monday, March 07, 2011 10:42 PM
  • Hi,

    1) Is Kerberos the only way that we can get BULK INSERT statements working from a client connection to SQL server, if the BULK INSERT is importing files off of a separate file server? i.e. the importing from a share \\FILESERVER\Share1?

    If you log on to SQL Server using a domain account that will uses Windows Authentication Mode, therefore SQL Server process will inpersonate the logged user security context to read the files in Bulk Insert operation. Therefore, you may need to configure SQL Server cluster trusted for delegation and Kerberos authentication for SQL Server. For more information, please see: Security Account Delegation (Impersonation) http://msdn.microsoft.com/en-us/library/ms188365.aspx.

    2) The SQL instance PRODSQL1 is set to dynamic ports under the SQL Server Configuration. If we're using Kerberos in order to support BULK INSERTS, is it a better idea to set a static port for that instance?

    It is recommended to use a static port. If SQL Server is listening on dynamic TCP port, SQL Server may change its listening port after each restart, therefore we need to delete existing SPN and register a new SPN match for new port for SQL Server service account. Also, we need to reconfigure all blocked programs to allow new port.

    3) What's the difference between setting a SPN using a port vs. not specifying a port? From what I have read, it appears that if you don't specify a port when using setspn, it's the same as using the default port for that service. For example, in the case of SQL server
    setspn -A MSSQLSvc/CLUSTER1 DOMAIN\!serviceSQL
    is the same as:
    setspn -A MSSQLSvc/CLUSTER1:1433 DOMAIN\!serviceSQL

    If you register a SPN not specifying a TCP port, it only suports non TCP/IP protcols, Named Pipe or Shared Memory. If connecting to SQL Server using TCP/IP protocol, we neeed to register a SNP with port. The format should be like this:

    • MSSQLSvc/CLUSTER1.DOMAIN.COM:1433 DOMAIN\serviceSQL for TCP/IP protocol
    • MSSQLSvc/CLUSTER1.DOMAIN.COM:PRODSQL1 DOMAIN\serviceSQL for Named Pipe and Shared Memory

    For more information about registering a SPN for SQL Server, please refer to http://msdn.microsoft.com/en-us/library/ms191153.aspx.

    For other question, you could refer to Ben's reply and this reply accordingly.

    Hope this helps.


    Best Regards,
    Chunsong Feng

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Tuesday, March 08, 2011 9:08 AM
    Moderator