Overview of the issue.
It is UAT server which has two SQL instances older 2008R2 and newer 2014. Application team is managing it their own. The issue is application is not connecting after migrated databases from 2008R2 to 2014, even connection string been changed.
I did basic connectivity checks, I can able to connect from other windows server where SSMS been installed. But unfortunately, I cannot test it from application server since it was UNIX server and application is JAVA based one.
I have checked SQL logs and had logon trace as well to find any incoming connection and failures etc. I could not get any, then connection string been shared and there is an explicit call to Kerberos authentication. It gave me a clue and checked the SQL 2014, SPN was not registered.
Resolution:
How to register SPN to SQL server to make a Kerberos authentication.
We need SQL service to run on domain account not in the local account.
Make sure to have static port and by default SQL named instance will use dynamic port.
Register SPN in the domain controller manually with help of domain admin
For example: Instance name is Muthu and port is 1550
SETSPN –S MSSQLSvc/YOURSERVERNAME.mydomain.com:1550 mydomain\svcAcct
SETSPN –S MSSQLSvc/YOURSERVERNAME.mydomain.com:Muthu mydomain\svcAcct
For my case: It was not run under domain account, it is NT account. Changed to domain account with static port after a restart of SQL service, SPN got auto registered.
Check SQL log, SPN got registered or not.
SP_READERRORLOG 0,1,'SPN'
How to check SQL server is using Kerberos or NTLM authentication
SELECT
S.SESSION_ID,
C.CONNECT_TIME,
S.LOGIN_TIME,
S.LOGIN_NAME,
C.PROTOCOL_TYPE,
C.AUTH_SCHEME,
S.HOST_NAME,
S.PROGRAM_NAME
FROM SYS.DM_EXEC_SESSIONS S
JOIN SYS.DM_EXEC_CONNECTIONS C
ON S.SESSION_ID = C.SESSION_ID
WHERE C.AUTH_SCHEME LIKE 'k%'
