Login failed. The login is from an untrusted domain and cannot be used with Windows authentication

Login failed. The login is from an untrusted domain and cannot be used with Windows authentication
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


This error has troubled me so many times. Each time I have to modify my connection string to use LOCALHOST or the port number to resolved but today I finally found a permanent fix.
Thanks to Pinal Dave who wrote this blog post.

https://blog.sqlauthority.com/2017/04/18/sql-server-login-failed-login-untrusted-domain-cannot-used-windows-authentication/

As you know I write this blog for my own documentation so here the fix as per Pinal's instructions.

• Edit the registry using regedit. (Start –> Run > Regedit )
• Navigate to: HKLM\System\CurrentControlSet\Control\LSA
• Add a DWORD value called “DisableLoopbackCheck”
• Set this value to 1

SQL Server 2008 R2/2012/2014 Security Policies

After installing SQL Server and creating the instance you need to focus on the security features and options that are available to you. I have created a set of standards that all DBAs have to follow in my team after creating the instance. Following are the details.

Service Account

• Change the SQL Server Service account to a domain account. 
• This should not have admin rights on the server.
• Use one AD account for the database engine service and another one for the agent service.
• Use unique AD Account for each database engine but use the same account for all agent services in your environment. If you have jobs that access databases across different hosts then this is required. 

Port Number

• Change the port number on which you runt he SQL Server service. This prevents from attackers in attacking on the default port.

Authentication

• If possible do not enable SQL authentication. I prefer Windows authentication for all logins. 
• If you have to enable SQL Authentication then make sure that you disable the SA account.

Audit

• Enable both Successful and failed logins to be audited. You can do this under properties for the SQL Server Instance.

Password Policy

• If you are creating logins using SQL Authentication then you have to enable the  'Enforce Password Policy'. This will ensure that the passwords that users set will have to comply with the Windows password policies. This will typically require 8 or more characters and a combination of several types of characters.
• Enforce Password Expiration policy so that users are forced to change passwords every 90 days or so. You can check the policy in Windows to find out the expiration days.

Account types and roles

• As much as possible refrain from creating logins for individuals. 
• Create logins for AD groups so that you don't have to keep adding new logins. Once a new AD account is added to a group they will inherit the permissions for that group.
• Create roles that you would know will be used in the database and added logins to those roles. It is easier to modify roles and modify permissions for everyone in that group, rather than doing this for each login.

Monitoring

• Set up alerts to monitor failed logins.
• Set up jobs to check on all the accounts that have expired passwords or if the accounts are locked.

If possible script out all of these and run the script once after you finish the install. This way you will never forget any of the above points.

Error: 18456, Severity: 14, State: 38

Error: 18456, Severity: 14, State: 38
Login failed for user 'produser'. Reason:Failed to open the explicitly specified database. [CLIET: 192.168.10.10]

or

Error: 18456, Severity: 14, State: 16.
Login failed for user 'XXXXXX'. [CLIENT: xxx.xx.x.xxx]

This is one of the most frustrating login failure errors. It does not mention what database the login was trying to connect to. The windows event viewer logs do not give any further information.

It seems that prior to SQL Server 2008 this meant the same as State 16. But in that case you would actually see the database name to which the login was trying to connect.
To troubleshoot state 38 you need to run a profiler trace and capture the following two events.

Errors and Warnings: User Error Message
Security Audit: Audit Login Failed

Make sure you select all the columns and run the trace while the login attempt is made.

For the event “User Error Message” you should see the database name to which the connection is being attempted. Verify the database exists and verify that the user has been created in the database and has the permission to connect to the database. You should also check if this is an orphaned user and fix it.

Error: 18456, Severity: 14, State: 11

Error: 18456, Severity: 14, State: 11.
Login failed for user 'domain\loginname'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: <local machine>]

There are several reasons because of which you could get this error but the primary reason is as follows.
Typically, when you are asked to provide access to a SQL Server instance, you would create a new login (windows or SQL) in the SQL Server instance.

But sometimes the person who is responsible for creating an account for you and give you sysadmin role,  will add your windows domain login to the LOCAL ADMINISTRATIVE login group. This is assuming the fact that the local admin login is a sysadmin on SQL Server instance. So I have always wondered why we get this error.

But the easy fix is to explicitly create a new login with Windows Authentication in the SQL Server instance and assign the server role to that ID.