Fix Error 18452: Login failed. The login is from an untrusted domain
Recently a user has reported error 18452 while connecting to database server. Full description of this error is given below:
Error: 18452, Severity: 14, State: 1.
Login failed. The login is from an untrusted domain and cannot be used with Windows authentication.
You can see the screenshot of the error 18452 in below image.
Error 18452 – Root Cause
Reason behind error 18452 is because of wrong security authentication mode configuration. SQL Server is set to accept only windows logins to connect to database instance.
As we know SQL Server uses two authentication modes to accept database connections. One is Windows Authentication mode and another one is SQL Server and Windows Authentication mode. We also call it Mixed Authentication mode.
Sometimes, SQL Server authentication mode is set to SQL Server and Windows Authentication mode to accept SQL as well as windows connections but still you will face this issue. That might be because you try to connect to a server that has Always on Availability Group configuration or database mirroring configuration.
Suppose you have AOAG configuration and you are connecting to database using primary replica name not listener name using a login for which default database is set as availability database. If failover got happen during your activity or you are connecting to secondary replica using a login for which default database is set as availability database then also you will get this error 18452 along with some SSPI context error that I have given below.
Error: 17806, Severity: 20, State: 14.
SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed. Reason: AcceptSecurityContext failed. The Windows error code indicates the cause of failure. The logon attempt failed
SSPI handshake errors comes because of Kerberos failure, which would most likely be related to non-existent SPN or bad SPN for SQL Server.
To fix this issue first we need to check the authentication mode set for your SQL Server Instance. If your SQL Server instance is running with windows authentication mode then you must change it to Mixed mode so that windows and SQL both type of logins can be authenticated.
To check the configured authentication mode for your SQL Server Instance, we need to launch SQL Server Instance property window. Right click at the instance node and click at the properties option as shown in below screenshot.
Once you will click at Properties option, you will be getting below SQL Server instance property window. Click at Security option from left side pane. You can see current Server Authentication configuration is set to Windows Authentication mode in below screenshot.
Now we will change it to SQL Server and Windows Authentication mode. Select the radio button for SQL Server and Windows Authentication mode option and click ok button to proceed. You can see that I have selected above mode to apply the changes.
Restart SQL Server services to apply the changes.
If your SQL Server Instance is already set with SQL Server and Windows Authentication mode then you should fix this issue in separate way. As I described above that one of the probable reason to get this error 18452 is because you might use AOAG replica server name to connect to the database with the login for which default database is set as the AOAG availability database.
If failover will happen then you would not be able to access the database because it will become secondary. Error 18452 will be generated along with SSPI handshake errors because same database is set as default database for your login that has become inaccessible now because of acting secondary database in AOAG. Failover will not happen for you because you are using replica server name to make database connection.
Possible solution to fix this issue is failback the AOAG to your earlier primary replica or you should use AOAG Listener name to make database connection. Also, to avoid this error during failover you should set default database for your login to master rather than availability database.
- Fix Error 18456: Login failed for user “User_Name”
- Fix Error 4064: Cannot open user default database. Login failed
- Error 53: Could not open a connection on SQL Server
- AOAG Listener Error 19471: The handle is Invalid
- Fix SQL Error 18456: failed to open the explicitly specified database - September 18, 2021
- Fix Always ON Connection Timeout Error 35206 in SQL Server - July 23, 2021
- How to Enable Preview Features in Azure Data Studio - July 15, 2021