SQL SERVER – Login Failed. The Login is From an Untrusted Domain and Cannot be Used with Windows Authentication

Many people ask me how is the life as a freelancer, as a consultant? My answer is “life is good, but to get the complete picture, you have to experience it”. There are enough challenges which are thrown at a consultant everyday and resolving them at a rapid pace is the key. Every day is like a closed door where I can’t see what’s going to happen. But trust me… it’s FUN! This blog is an outcome of such “challenge” which was thrown while performing final checks after deployment for a client. We ran into an issue where the SQL cluster was not able to failover to another node. Let us learn about how to fix the error login failed.

As usual, I took a deep breath and look at the logs.

In the event log, I saw below message.

The program sqlservr.exe, with the assigned process ID 6261, could not authenticate locally by using the target name MSSQLSvc/SQLNODE001.mydomain.com:1433 SQLAUTH\svc_sqlserver. The target name used is not valid. A target name should refer to one of the local computer names, for example, the DNS host name.
Try a different target name.

This message sounds familiar as talk about target name, which is related to SPN. Then I looked into the cluster log to check errors appearing at the same time when the SQL clustered resource was failing to come online.

INFO [RES] SQL Server : [sqsrvres] Service is started. SQL Server pid is 19224
INFO [RES] SQL Server : [sqsrvres] Connect to SQL Server …
ERR [RES] SQL Server : [sqsrvres] ODBC Error: [28000] [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. (18452)
INFO [RES] SQL Server : [sqsrvres] Could not connect to SQL Server (rc -1)
INFO [RES] SQL Server : [sqsrvres] SQLDisconnect returns following information
ERR [RES] SQL Server : [sqsrvres] ODBC Error: [08003] [Microsoft][ODBC Driver Manager] Connection not open (0)
INFO [RES] Network Name: Agent: Sending request Netname/RecheckConfig to NN:24be86d5-8e7f-4e97-8cf5-c7a78a6bc1a8:Netbios

WORKAROUND/SOLUTION

Both pointers from the logs were pointing to SPN issue. In the past I have used SETSPN.exe tool to configure SPN and always had to struggle to get right command and parameter. For a TCP/IP connection the SPN is registered in the format of MSSQLSvc/<FQDN>:<tcpport>. We should remember that both named instances and the default instance, are registered as MSSQLSvc, but <tcpport> value to would be different for instances

While searching for SETSPN.exe command I came across this nice tool which can help a lot of pain like syntax error etc. is called as “Microsoft Kerberos Configuration Manager for SQL Server” which can be downloaded from

The best piece about this tool is that it can help in finding missing SPN and provide script to run or fix it directly, if you have permission. Basically, it can

• Gather information on OS and Microsoft SQL Server instances installed on a server.
• Report on all SPN and delegation configurations on the server.
• Identify potential problems in SPNs and delegations.
• Fix potential SPN problems.

Once SPN was created, we were able to fix the issue.

Reference: Pinal Dave (https://blog.sqlauthority.com)