Hi all,

I use DNS alias to access my database server:

server name is -> SRV100

DNS Alias is -> SQLPROD

I've noticed that, using Windows authentication, if I connect to the server using its server name, the DB Engine uses Kerberos authentication scheme (as it is supposed to do) but if I use Kerberos authentication, I see that the DB Engine uses the NTLM authentication scheme

select client_net_address,auth_scheme from sys.dm_exec_connections 

I need to use DNS alias to connect to my server and I want to use Kerberso auth scheme.

"but if I use Kerberos authentication, I see that the DB Engine uses the NTLM authentication scheme" - I assume you mean when you use an Alias...

if that is the case: When you setup your DNS Alias,  it must be a CNAME record so it can still use Kerberos authentication. If you set it up as ANAME record Kerberos  wont work.

In your case it would be as follows: SQLPROD (CNAME) -> points to SRV100 (ANAME) -> points to IP Address.

Let me know if that helps or if you already have it setup this way, so we can troubleshoot further.

Hello SQLGru,

yes I typed the wrong thing, I mean "When I use DNS Alias".

My DNS Alias is a CNAME record that points to the A record of the listener (I use Availability Groups) and Kerberos doesn't work.

I have tried:

1) Using a DNS A record for the alias -> Kerberos doens't work

2) Using a DNS CNAME record for the alis that point to the A record of the server (not to the name of the listener) -> kerberos doesn't work

3) Using a DNS A record for the alias that point to the A record of the server (not to the name of the listener) -> kerberos doesn't work

4) I have registerd an SPN for the user used to run the SQL Server service pointing to the alias created and kerberos still doesn't works

I've found!

using adsiedit I noticed that a SPN has been created for my server's name but there are no SPNs for my listeners, and listeners are computer object in active directory.

I've created 2SPN for each of my listeners

On the client machine, at command prompt, I used KLIST PURGE

And.... now if I connect to the DNS Alias I see from sys.dm_exec_connections that authentication scheme is KERBEROS!

• Marked as answer by Andrea Caldarone 20 hours 2 minutes ago

Glad to hear it, SPNs was going to be the next step...also didnt know you were using AG :)