June 21, 2016 at 1:08 pm
Hello,
We are seeing a strange issue when we connect to our SQL server using the DNS name vs the hostname or fqdn. When connected via the DNS name and running the statement, select auth_scheme from sys.dm_exec_connections where session_id=@@spid the auth_schema returns as NTLM. However that same select statement returns kerberos when we use the name of the server. Any ideas? The SPN is registered and delegation has been setup but maybe something else has to be changed when using DNS names?
Thanks for any advice.
June 23, 2016 at 5:59 am
Had similar issue, with bit of messing got it resolved
1. Found that I had to register SPN for both - servername and it's dns alias. Ensure you have FQDN registered as well.
#setspn -l sqlserviceaccount
MSSQLSvc/server123:1433
MSSQLSvc/server123.renre.com:1433
MSSQLSvc/alias123:1433
MSSQLSvc/alias123.renre.com:1433
2. Another SPN that you might need is the HOST for the alias (this solved my issue with bulk inserts from network using alias, i.e. \\alias123\share\somefile.csv):
#setspn -l server123
these 2 should be there by default when server is added to AD:
HOST/server123
HOST/server123.yourdomain.com
these 2 might need to be added:
HOST/alias123
HOST/alias123.yourdomain.com
To add alias host spn:
setspn -A host/alias123 server123
setspn -A host/alias123.yourdomain.com server123
3. Last option that I'd check is if your service account used in kerberos is trusted for Delegation (in AD users & computers, find the account and look at Delegation tab).
June 23, 2016 at 12:44 pm
Thank you!
I will give that a shot, or my SysAdmin will.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply