SQL Server Troubleshooting: Resolving “The login is from an untrusted domain and cannot be used with Windows authentication”
Subscribe for learning SQL Server: Simple SQL Server — YouTube
Issue background
You have created a domain (e.g., test.mydom.com) as in Figure 1 below where there is a domain controller, an application server where SSMS is installed and finally, a database server where SQL server is running. You have also verified that Application server and database server are in the same domain and TCP/IP is enabled in SQL Native client configuration in “db” server. Furthermore, you have also created inbound firewall rule in “db” which allows the connection to PORT number 1433 that is being used by SQL server.
You have a domain user called TEST\login which is added as login user in SQL server.
When you login as a user “TEST\login” to the “app” and try to login SQL server in “db” with Windows Authentication in SSMS, you face following error:
In addition, you find following logs in ERRORLOG showing
Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT: 10.0.0.6]
Logon SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. In this article, I will try to summarize the possible root causes of the above error and respective solutions.
Briefly about Windows Authentication process
When an application (e.g., SSMS) opens a connection and uses Windows Authentication, by default SQL server uses NTLM for local connection and Kerberos authentication for remote connection. If Kerberos cannot be used in remote connection, then Windows will fall back to NTLM authentication.
The detailed Kerberos authentication steps can be summarized as below:
- An application (e.g., SSMS) opens a connection with Windows Authentication, and passes an SPN which is constructed based on the service name, FQDN, port number and database SQL server instance name: MSSQLSvc/<FQDN>:[<port> | <instancename>]. For example, in our case, “app” server must be passing the SPN in the form of “MSSQLSvc/db.test.mydom.com”. I am omitting port number since it is default (1433) and instance name since I am using default instance, not named instance. For further details, please refer to documentation (https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/register-a-service-principal-name-for-kerberos-connections?view=sql-server-ver16#Formats).
- The generated SPN will be submitted to Domain Controller (dc). If a valid SPN exists in dc, the token will be provided by dc and this token will be submitted to SQL Server by app server for authentication purpose.
- app server logon/access db server and passes the token to the SQL server running inside for validation
- If token is valid, Windows Authentication succeeds.
If any step above fails, Kerberos authentication fails and NTLM authentication is used instead by default. If NTLM authentication cannot also be used, you will face an error message in Figure 2.
Therefore, in order to find the root cause of the error message in Figure 2, we should investigate why both Kerberos and NTLM authentications are not working.
Possible causes of why NTLM is not working
- Please login to db server and go to Computer Configuration > Windows Settings> Security Settings > Local Policies > Security Options and check NTLM settings is restricted in your server.
Also check the same settings in your other servers including app and dc.
2. Login to db server and go to Computer Configuration > Windows Settings> Security Settings > Local Policies > User Rights Assignment and check the Windows account you are using to login is included in the highlighted policies below:
If the account is not included in the policies, please add them and run gpupdate/force in cmd to ensure that the policy update has been applied immediately.
FURTHERMORE, PLEASE NOTE THAT THE ABOVE SETTINGS ALSO PREVENTS KERBEROS AUTHENTICATION ALONG WITH NTLM.
Possible causes of why Kerberos is not working
The failure of in-advance SPN registration in domain controller is one of the main causes of Kerberos authentication failure. The SPN is registered in advance by default when SQL server starts with a log-on account which has enough permissions to register SPN. Suppose the account starting SQL Server doesn’t have permission to register an SPN in Active Directory Domain Services. In that case, this call fails, and a warning message as below is logged in the Application event log as well as the SQL Server error log:
<The SQL Server Network Interface library could not register the Service Principal Name (SPN)>
Consequently, when a client submits SPN to DC to verify, DC cannot verify SPN, and the error in Figure 2 pops out (if NTLM is also not allowed):
<Login failed. The login is from an untrusted domain and cannot be used with Windows authentication>
To register the SPN, the Database Engine must be running under a built-in account, such as Local System (not recommended), or NETWORK SERVICE(NT SERVICE\MSSQLSERVER), or an account that has permission to register an SPN. You can register an SPN using a domain administrator account, but this is not recommended in a production environment. When SQL Server runs on the Windows 7 or Windows Server 2008 R2 or later versions of operating system, you can run SQL Server using a virtual account or a managed service account (MSA). Both virtual accounts and MSA’s can register an SPN. If SQL Server isn’t running under one of these accounts, the SPN isn’t registered at startup, and the domain administrator must register the SPN manually.
In short, please check SPN is registered successfully. You should see the logs as below in ERRORLOG:
The SQL Server Network Interface library successfully registered the Service Principal Name (SPN)If there is a failure, you will see the below log:
The SQL Server Network Interface library could not register the Service Principal Name (SPN)If you see the above failure log, please change your SQL server logon account to NETWORK SERVICE(NT SERVICE\MSSQLSERVER) or manually register the SPNs in DC. For further details, please refer to Register a Service Principal Name for Kerberos Connections — SQL Server | Microsoft Docs.
Summary
To summarize, the error in Figure 1 mostly occurs because of the following:
- NTLM is restricted in one of the involved servers
- The client server is not allowed to access the db server
- SPN registration has failed
If you do not see the issues with the above settings, please contact Microsoft support for further investigations. We are always happy to help you:).
