3

I have a domain test.local with 4 domain controller.

I have a SQL Server, sometime when rebooting one of my domain controller I got these error :

Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.".

Why authentication is not done on the other 3 DC? Normally there is load balancing when there are multiple domain controllers.

Thanks for your help

Improve this question
  • What does the SQL Server use for DNS? – joeqwerty May 31 '17 at 12:38
  • In primary it's my primary domain controller and secondary, the second one – Adeel ASIF May 31 '17 at 14:57
  • I added some details about how I fixed this issue – Adeel ASIF Jul 26 '20 at 22:38
2

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)

Improve this answer
  • You are a bit late my friend, anyway, I solved this using Kerberos authentication on SQL Server. – Adeel ASIF Jul 24 '20 at 8:47
  • Late, I don't mind :) Would be nice if you'd provide bit more info or hint for future readers.Sounds like kerbticket what you're saying or not fully/correctly configured MSSQL connection string? – Geeky Masters Jul 24 '20 at 12:50
  • Yep I added some details about how I fixed it. – Adeel ASIF Jul 26 '20 at 22:38
2
+100

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.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.