this is how I resolved this issue.
First you need to check if your SQL Server instance is running on which authentication system. Execute the following query with a Windows AD user :
select auth_scheme from sys.dm_exec_connections where session_id=@@spid
If SQL Server is using Kerberos authentication, a character string that is listed as "KERBEROS
" appears in the auth_scheme column in the result window.
If the authentication is NTLM
, then you have the issue I had.
Because if you use NTLM
, every time your client will try to connect to the SQL Server, it will query the AD to ask if you are authorized or not.
In Kerberos authentication, there is a token system. The client who's connected to the SQL database, will get an unique token with an expiration of 10 hour (default value on Windows system). Then it will expire.
If the Domain controller is not available during the token lifetime, you can still connect to your SQL Server.
(Be sure to execute commands as Domain Admin).
To configure Kerberos you need to do add an SPN like this :
SetSPN –A MSSQLSvc/<ComputerName>.<DomainName>:1433 <AccountName>
For example if my SQL Server is Server1 in redmond.microsoft.com domain running with a service account SqlSvcUsr the command will be :
SetSPN –A MSSQLSvc/Server1.redmond.microsoft.com:1433 redmond\SqlSvcUsr
If you want to add a specific instance then it will be :
SetSPN –A MSSQLSvc/Server1.redmond.microsoft.com:50203 redmond\SqlSvcUsr
SetSPN –A MSSQLSvc/Server1.redmond.microsoft.com:MyInstance redmond\SqlSvcUsr
Then restart your SQL Server service. And execute the query again :
select auth_scheme from sys.dm_exec_connections where session_id=@@spid
Kerberos should be listed.
Hope it helps.