2

Requesting some inputs..

We have a 2 node SQL 2016 Availability group configured and apps currently use primary replica for connection. We are now planning to use listener instead. Questions is, how to make sure we are using kerberos authentication for application connectiions?. Is it mandatory to manually register SPN for the listener? or is it automatically created?. is this a one time activity?. How to make sure SPN is registered automatically for the listener when the AG restarts or failover to second node?. Appreciate all the responses.

1 Answer 1

1

If you configure the listerner with a domain admin account it should be automatic but usually it is not enough.

It's better to have full control of what you are doing. First thing to remember is that an spn is a registration done on the domanin compunter account or domain user account that is running sql server service. While it is the same for both nodes, you have to do only one registration.

Check existing spn for your system:

setspn -L domain\sqlserverservicelogin

or

setspn -L domain\clusterservercumputeraccount$

Then you can register a listener in this way:

setspn -S MSSQLSvc/listener_name:port domain\sqlserverservicelogin
setspn -S MSSQLSvc/listener_name.domain.local:port domain\sqlserverservicelogin

It's better to register both listener name and listener FQDN to be sure to match less precise application configurations.

The same commands with computer accounts:

setspn -S MSSQLSvc/listener_name:port domain\clusterservercumputeraccount$
setspn -S MSSQLSvc/listener_name.domain.local:port domain\clusterservercumputeraccount$

To register spn you have to be domain admin.

A Microsoft tool that can help you if Microsoft Kerberos Configuration Manager for SQL Server:

https://www.microsoft.com/en-us/download/details.aspx?id=39046

Once everything is on place you don't have to do anyting else unless you change something like serfice users, listener names and/or ports

2
  • Thank you for the explanation. we are using a GMSA account for SQL service. Is the procedure same for how we set SPN for the listener as per your above explanation?
    – kevin
    Mar 15, 2021 at 4:05
  • 2
    GMSA is a domain account, so it needs the SPN registered for it. The default virtual service account or network service will use the computer account. Jun 13, 2022 at 16:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.