SPN for Always On Listener

Question

Post reply

SPN for Always On Listener

RonMexico

Hall of Fame

Points: 3474
More actions
August 28, 2020 at 7:11 pm

#3784503
Suppose I have the following two node cluster configured with an AG. All are on the same domain, use the same service account, and have named instance SQLInstance. SQLA and SQLB run on port 123 and SQLC (listener) runs on port 789
Node1 - SQLA
Nodel2 - SQLB
AG Name - TestAG
AG Listener - SQLC
Here are the results of the query below connecting to the nodes and listener
```
select auth_scheme from sys.dm_exec_connections where session_id=@@spid
```
SQLA\SQLInstance - KERBEROS
SQLB\SQLInstance - KERBEROS
SQLC\SQLInstance - NTLM
I get a list of all SPNs registered to the service account by running
```
setspn -L someDomain\ServiceAccount
```
which shows
MSSQLSvc/SQLA.someDomain.com:123
MSSQLSvc/SQLA.someDomain.com:SQLInstance
MSSQLSvc/SQLB.someDomain.com:123
MSSQLSvc/SQLB.someDomain.com:SQLInstance
MSSQLSvc/SQLC.someDomain.com:789
MSSQLSvc/SQLC.someDomain.com:SQLInstance
Why is SQLC not using Kerberos?

Viewing 15 posts - 1 through 14 (of 14 total)

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

Site Owners SSC Guru Points: 80330 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

Alejandro Santana SSCommitted Points: 1838 More actions · Answer 2

when you connect through the listener, try doing klist.exe tickets and try and find your ticket in cmd.

find the one that belongs to the listener and tell us what it says.

List all the spns configurations regarding to the service user you used, if you have more than 2 entries one with the port and one without if you have more than those it might fail, see if you have mispelled/duplicates of the listener.

RonMexico Hall of Fame Points: 3474 More actions · Answer 3

I'm not a domain admin so I don't think I can do the klist that you are talking about. Would it be possible that the listener uses a different user other than the SQL service account? The service account only lists two entries - one with the port and one with the named instance.

Alejandro Santana SSCommitted Points: 1838 More actions · Answer 4

Klist.exe tickets

doesn't require domain admin privileges, open cmd as admin, these are the tickets the AD assigned to your user.

If you can't see the Listener entry when listing all the SPNs with

setspn -L "domain\user"

Doesn't look like it's registered, try to register it with the SQL Server Service Account used in the nodes.

All nodes and listener must have the same service account otherwise connections will fail, register the listener WITH and WITHOUT the port like shown as below.

setspn -A MSSQLSvc/AGListener.domain.com domain\serviceaccount

setspn -A MSSQLSvc/AGListener.domain.com:1433 domain\serviceaccount   (or whatever port you used for the listener)

Quoting Microsoft BOL:

If you want an availability group to work with Kerberos:
All server instances that host an availability replica for the availability group must use the same SQL Server service account.
The domain administrator needs to manually register a Service Principal Name (SPN) with Active Directory on the SQL Server service account for the virtual network name (VNN) of the availability group listener. If the SPN is registered on an account other than the SQL Server service account, authentication will fail.

RonMexico Hall of Fame Points: 3474 More actions · Answer 5

I'm a quite confused about klist.exe tickets because when I run it, it returns all tickets assigned to my login - not the SQL Service account. I do see a ticket listed for the listener under my login but not sure exactly what you are looking for.

The listener entry is being returned by running

setspn -L someDomain\ServiceAccount

which shows as

MSSQLSvc/SQLC.someDomain.com:789

MSSQLSvc/SQLC.someDomain.com:SQLInstance

These SPNs were created using the commands

setspn -A MSSQLSvc/SQLC.someDomain.com:789 someDomain\ServiceAccount
setspn -A MSSQLSvc/SQLC.someDomain.com:SQLInstance someDomain\ServiceAccount

Is that correct?

Alejandro Santana SSCommitted Points: 1838 More actions · Answer 6

When you run the klist.exe tickets its not supposed to return the service account, its gonna return the ticket from the server if its using kerberos, if you connect to a SQL Server using kerberos it will generate a ticket and put it on your account.

When i connect to one of my instances which is using kerberos it automatically generates a ticket from that service

klist.exe

what i am trying to ask you if when using the listener its generating a ticket with the listener, if its not generating a ticket it's not using kerberos.

The commands you sent have an error.

this is how you are supposed to add the listener spn.

setspn -A MSSQLSvc/SQLC.someDomain.com:789 someDomain\ServiceAccount
setspn -A MSSQLSvc/SQLC.someDomain.com someDomain\ServiceAccount

you don't need to put the :SQLC at the end of the second command.

you must delete that SPN and register the new one without the :SQLC at the end of the listener name.

if you dont delete the existing one with the issue kerberos will never work.

RonMexico Hall of Fame Points: 3474 More actions · Answer 7

Now I'm following along. Thanks for the clarification.

I do not see a ticket for the listener, but rather for the primary node. This doesn't really surprise me given that when I run

select auth_scheme from sys.dm_exec_connections where session_id=@@spid

on each node it returns KERBEROS but when I run it connected to the listener it returns NTLM.

Perhaps the reason is the incorrect commands used to generate the SPN. I am using a named instanced which is why I included :SQLInstance at the end of the second one. To confirm, can that be excluded when using named instances? If so, I can do some testing.

Alejandro Santana SSCommitted Points: 1838 More actions · Answer 8

Named instance? we are talking about the listener, not a named instance.

Let's get something clear xD

You have 2 nodes and 1 listener right?

Node 1
Node 2
Listener

Nodes = SQL Server Instances

Listener is not a server, the listener is an object created in the AD, not a SQL Server instance so i don't think a Listener can be named like SQL Server instances.

RonMexico Hall of Fame Points: 3474 More actions · Answer 9

Got it. SQL automatically created SPNs for the nodes using the example I gave above so I was trying to follow that for the listener. I'll remove the reference to the "named instance" for the listener and add it the correct way.

Alejandro Santana SSCommitted Points: 1838 More actions · Answer 10

Every time SQL Server starts it will try to register the SPNs and every time you shut down the instance it will remove the SPNs.

Excelent. Make sure to remove the Listener SPN with the named Instance or kerberos won't work.

Technically speaking you must have the next architecture.

Node 1 - SQL Server Instance - Named Instance

setspn -A MSSQLSvc/SQLA.someDomain.com:789 someDomain\ServiceAccount
setspn -A MSSQLSvc/SQLA.someDomain.com:SQLInstance someDomain\ServiceAccount

Node 2 - SQL Server Instance - Named Instance

setspn -A MSSQLSvc/SQLB.someDomain.com:789 someDomain\ServiceAccount
setspn -A MSSQLSvc/SQLB.someDomain.com:SQLInstance someDomain\ServiceAccount

Listener

setspn -A MSSQLSvc/SQLC.someDomain.com:123 someDomain\ServiceAccount
setspn -A MSSQLSvc/SQLC.someDomain.com someDomain\ServiceAccount

Greetings.

RonMexico Hall of Fame Points: 3474 More actions · Answer 11

The changes were put in as described and I still cannot get SQLC (listener) to use Kerberos. I should also mention that this is in effort to be able to bulk import from a file share without getting the access denied error. Hopefully that doesn't change the course of action.

Alejandro Santana SSCommitted Points: 1838 More actions · Answer 12

Try using Microsoft tool, Kerberos Configuration Manager, and connect to the Node 1 using your account and check the SPN tab and check the box of SQL AG Listeners, check if it have issues, to fix them you will need to use a domain admin account.

The tool will tell you if everything is fine or if you have errors and will help you fix them.

Microsoft Kerberos Configuration Manager for SQL Server

Regards,

RonMexico Hall of Fame Points: 3474 More actions · Answer 13

I downloaded the Kerberos Configuration Manager and the results were interesting. Either my set up is a little strange or my understanding of it is off. The SPNs it said it was missing for the listener are

setspn -s MSSQLSvc/SQLC.someDomain.com:123 someDomain\ServiceAccount
setspn -s MSSQLSvc/SQLC.someDomain.com:SQLInstance someDomain\ServiceAccount

So it uses the port which SQL is installed on and not what the listener was configured to and it also specifies the instance name. I'll give this a try and see if that finally solves it.

RonMexico Hall of Fame Points: 3474 More actions · Answer 14

Creating those SPNs worked and now the listener returns kerberos. I'm still getting the Operating system error code 5(Access is denied.) error when trying to bulk import so that is going to need another look.