Multiple domain controller and SQL Login Failed when reboot

Solution 1:

Seems like know issue.

But I'm wondering if this simply is be related to some network settings or connection settings (persistent?) on your MSSQL server. Means when you're restarting your DC the SQL server doesn't know about and the SQL is trying to auth and it cuts the connection or corrupt the response from DC in the middle of auth.

It's one of the suggestions from Microsoft documentation

A network-related or instance-specific error occurred while establishing a connection to SQL Server

(I know it's from the other side, but related to the linked known issue seems like it's network related)

Solution 2:

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.