Post reply

Do you need seperate SPN entries for multiple SQL Server ports?

  • August 28, 2018 at 12:21 pm

    #362274

    I added a port 48000 to SQL server configuration manager to allow SQL to respond to both 1433 and 48000 (same instance). After I configured it everything works fine when connecting to 1433. When I connect using servername,48000 I fall back to NTLM instead of using Kerberos. We used the Kerberos Configuration manual to fix the SPN which looks like this now

    MSSQLSvc/ServerName.domain.com:1433,48000

    I have never seen a SPN with 2 portnames like that. Do I need a separate SPN entry just for 48000 so Kerberos will work?

  • August 28, 2018 at 12:43 pm

    #2003696

    i believe you will need to specify both ports

    MSSQLSvc/ServerName.domain.com:1433 domain\account
    MSSQLSvc/ServerName.domain.com:48000 domain\account

  • August 30, 2018 at 6:45 am

    #2003985

    Thank you, so I tried removing the spn with spn and it stated it was unable to locate account "NT Service\MSSQLServer". Now I thought that these virtual accounts could register/maintain themselves.

    The syntax I used was

    setspn -d "MSSQLSvc/hostname.domain.com:1433,48888" "NT Service\MSSQLSERVER"

    I am trying to delete above and register this only under 48888

  • August 30, 2018 at 7:01 am

    #2003990

    SQL Server must be running under a domain account, NT Service isn't one.

    Asides that, for Kerberos there must be a separate SPN if you want it to work on port 48000. Domain accounts can (if properly configured) maintain SPN entries themselves, however configuration has do be done in Active Directory.

  • August 30, 2018 at 7:20 am

    #2003994

    DinoRS - Thursday, August 30, 2018 7:01 AM

    SQL Server must be running under a domain account, NT Service isn't one.

    Asides that, for Kerberos there must be a separate SPN if you want it to work on port 48000. Domain accounts can (if properly configured) maintain SPN entries themselves, however configuration has do be done in Active Directory.

    Right but if I connect to port 1433 Kerberos is working, sql is running under NT Service\MSSQLSERVER

  • August 30, 2018 at 9:22 am

    #2004021

    So I finally got it...

    The Kerberos Configuration Manager is stupid in the fact that it will take whatever SQL server configuration manager has for the ports. In my case I wanted SQL to respond to 1433, and 48000. It was listed like this 1433,48000.
    So "fixing" it Kerberos Configuration Manager caused an erroneous entry, the one I wanted to delete.

    So in trying to delete this thing, the service account "nt service\mssqlserver" is considered a local account. This translates into the SPN being stored in the Computer Account in AD, not the service account nt service\mssqlserver (how would ad know about that anyway, right?).

    So to delete the bad SPN, I used the computer name at the end of setspn...
    setspn -d "MSSQLSvc/hostname.domain.com:1433,48000" "hostname"

    Then I added it the same way with only the 48000 port and the computer name. Now I got kerberos! Microsoft should fix their tool to handle these situations. Phew...

  • October 18, 2019 at 2:09 pm

    #3690148

    Hi @jcourtjrSQL

    I have SQL Server 2017 on Azure VM with Windows 2016.  Have two ports opened for SQL Server and the kerberos config tool is suggesting the same incorrect syntax posted here earlier.

    I have not found any Microsoft documentation that says it is necessary to register additional port.  However, like the scenario here, I am also seeing NTLM being used and would like to try this solution.  I hope you have not found any other adverse effects after SPN registration.

     

    • This reply was modified 5 years, 1 month ago by  jaincs.

  • October 21, 2019 at 6:33 pm

    #3690714

    nevermind, i registered using the same way as jcourtsql and no issues found.  thanks for this post.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply