In SQL Server, there are error few messages which can be caused due to multiple reasons. One of the famous example is “Login Failed for User” error message. If you have seen this earlier, you would know that this message can come due to incorrect user name, incorrect password, incorrect database and many more other reasons. Whenever I see login failed, I look at SQL Server ERRORLOG to see the exact cause.
Here is one of the error where I was not clear about the cause.
Here were the messages in ERRORLOG.
Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT: 169.111.227.120]
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 [CLIENT: 169.111.227.120]
This was coming from the application server which was same domain. Based on my search on the internet, there is some kind of loopback, check taking place which causes trusted connections via the loopback adapter to fail.
WORKAROUND/SOLUTION
Loopback check can be removed by adding a registry entry as follows:
- 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
If above doesn’t solve the issue, we need to create SPNs for SQL Service account. 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 https://www.microsoft.com/en-us/download/details.aspx?id=39046
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.
Have you used this tool earlier?
Reference: Pinal Dave (http://blog.SQLAuthority.com)
Related Posts
SSPI handshake failed with error code 0x8009030c, state 14 while establishing a connection with integrated security; the connection has been closed
I came across a scenario where a more unusual “SSPI Handshake errors” regularly appeared in the error log. In this blog, I am covering the cause of this issue and the solution we followed to fix it:
In the Sql Server error log the below entries was logged:
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 [CLIENT: XX.XX.XXX.XX]
Error: 17806, Severity: 20, State: 14.
Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT: XX.XX.XXX.XX]
After checking the web.configs on the webserver with the above IP adress I found the C# application that was the cause of the error. First I tought this would be a easy fix with a simple security policy error. But after verifying the account’s «domain\sqlaccount» had «Access this computer from the network” in «Local security policy (secpol.msc)». I was out of ideas.
Online I got the tips to change the faulty connection string on data source to use SQL authentication instead of windows authentication. But that is not good enough solution.
After falling back to my old wisdom. RTFEM — Read the Fucking Error Message. I got a hint from untrusted domain.
It was an issue when using DNS aliases to connect to a machine using a different domain name.
For example, if you have a SQL server on called: SqlServerAlfa on yourdomain.com — which is an Active Directory domain and another one: yourdomain.net. Maybe this is a left over from an earlier naming convention standard that you used or a merger. To keep your old applications working when migrating to the new naming convention you have set up a DNS alias (CNAME) record for
database.yourdomain.net —> sqlserveralfa.yourdomain.com
You’ll be able to connect to sqlserveralfa.yourdomain.com using Windows integrated security but won’t be able to connect to database.yourdomain.net even though it’s the same server because the domain name doesn’t match your AD domain! This will cause the error in the error log.
We received the following error when attempting to connect to a SQLServer database from SQL Management Studio this morning.
Login failed. The login is from an untrusted domain and cannot be used with Windows Authentication. (Microsoft SQL Server, Error: 18452
We have never seen this issue before and have been connecting to this server in the same way without issue for months. With some investigation/research found the following error in the SQLServer logs which is a little more helpful:
Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. [CLIENT: 1.2.3.4]
Error: 18452, Severity: 14, State: 1.
SSPI handshake failed with error code 0x80090311, 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. No authority could be contacted for authentication. [CLIENT: 1.2.3.4]
Given the above error we suspected our domain controller was having issues. Sure enough the following nltest command run on the database server showed it couldn’t find the logon server:
So we rebooted our domain controller and the issue was resolved.
The odd part of this problem was only some users couldn’t login while others still could. We assumed some form of credential or authentication caching caused this inconsistency.
I hope this post makes it easier for others to find/fix this problem. If so or you have further questions please leave a comment below.
Happy Coding!
TITLE: Connect to Server
——————————
Cannot connect to SQL2008R2.
——————————
ADDITIONAL INFORMATION:Login failed. The login is from an untrusted domain and cannot be used with Windows authentication. (Microsoft SQL Server, Error: 18452)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=18452&LinkId=20476
This error is not directly related to SQL Server, but since I had to troubleshoot it because I got the wonderful error while teaching a Workshop (Urg!!). And surprise, I have never seen this error before so I am running a bit lost as to what the hell does it mean?
Quickly open up search engine started snooping around, with no luck. I get articles with my computer is not trusted on domain, can’t see domain controller, DNS Issue, SPNs are not registered, login is invalid, password as expired, whole host of different types of issues. So, I started tackle it one issue at time.
DNS Issue
I pinged the server by IP; issues. I pinged the client computer by IP from server. No issues. I tried doing Reverse Lookup using “Ping IP -a”; both location couldn’t find it. AHA! My DNS Reverse look is not configured (note I had no idea how that can be the issue). At last I go on DNS Server (Yeap, I had access to DNS Server, it was my lab not production (phew!)), setup a Reverse Look using the wizard and hoped for best…
Opps!
Still the stupid “PING IP -A” did not work, I figured I got DNS configured wrong. Kept at it for at least an hours to figure out how to configure DNS, finally gave up (concluding to myself how can that cause trust issue, more I learned configuring DNS Server is not easy as I thought, Do’h!).
Can’t See Domain Controller & My Computer is not Trusted on Domain
Maybe from my client I can’t see the domain controller; I was like well how can that be? Okay maybe the computer is using cached credentials. I logged in using Local Administrator, deleted my domain account profile. Relogged, forcing to authenticate to Domain Controller, No Issues. So what gives? Something must have screwed up in AD and my computer doesn’t have proper permission in AD. Removed my computer from AD, readded it, STILL NO GO. By now I am about to destroy my entire LAB and rebuild it from ground up… but I continue.
SPNs are not registered
It occured to me now (just now, kind of feel stupid, but..), well if I am getting failed login error there must be errors logged on SQL Server and sure enough ERRORLOG has some interesting errors.
Error 17806, Severity: 20, State: 14
SSPI handshake failed with error code 0x80090311, 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.
Uhmm, Nope. I don’t know what error message means. But usually when I see SSPI, I think Kerberos. When I see Kerberos related issues I just want to turn around and run the other way, they are not fun :(. But as last as SQL Server DBA we can’t avoid them, so started digging into this now. First thing I want to check with it comes to Kerberos is do I have SPNs created for the SQL Server?
We can do that using …
- SETSPN -L ComputerName
- SETSPN -L Domain\ServiceAccount
Executed both commands on AD Server, came back blank. AHA! Stupid SPNs, created both SPNs. The SQL Server was running under Local Computer account, so i couldn’t create SPN for that. But I created them for SQL Server it self. Went back to my client computer, still no go :(. Now I am about to give up, instead I decided last ditch effort…
Login Account Issues
Maybe my windows account password expired, maybe it was locked out, maybe something happen on SQL Server? After a little bit of snooping around, found out none of those are my issues.
Now, I am tired, been up for almost 18 hours, I give up and went to sleep.
Next day coming in, digging into issue again. Because I am having issue from all my client computers now, which work working at first (Son-of-a….). So I can’t think of something, it MUST be I didn’t create SPN for the Service Account. I decided to change the Service Account for the SQL Server to a domain account. Every time I try to set it, it says “password invalid”. I changed password, I reset, I created new account, ALL FAILED~!
I thought maybe the SQL Server Configuration Manager was not working, I decided to verify the login using command line runas command.
runas /username:domain\username /noprofile cmd
I entered my password and I got another error, ohh it was something different.
1787: The security database on the server does not have a computer account for this workstation trust relationship.
The who and what now? I’ll admit I am completely in uncharted waters here. I am the first person to admit, AD and I don’t get along. We parted ways long time ago, this is why I do. SQL I am good at it, thats it. AD, I need you but stay away from me *angery face holding up a fist*.
Error 1787: The Security Database
I have no idea what database it is talking about? I did some searching found [2]. It suggested some using ADSIEDIT.msc to look for the TrustType property for my computer. Looked around couldn’t find it the list, I didn’t want to add it (my whole stay away from AD theme). I didn’t do anything; but while in ADSIEDIT.msc I decided to snoop around. I notice my SQL Server Computer computer and the Client computer properties were not the same (they are all Windows 2008 R2 Servers; so what is going on?).
Then I remember while adding my client computers to domain somehow my SQL Computer account got deleted from AD. So I had manually added it back in (BIG MISTAKE), could it be that when the accounts are added using the Windows Join Domain there are some special permissions granted (I know Duh, of course they are; but its the whole AD and me not getting along again). Quickly on to next test.
Adding SQL Server Computer back to Domain
- Went to AD, deleted SQL Server Computer from AD.
- Went to SQL Server …
- Right click computer, properties.
- Change Settings under computer name.
- Change.
- Change to Workgroup, type “WS” in workgroup name. Click OK.
- Restarted computer.
- Logged in using Local Administrator account.
- Readded SQL Server to domain.
- Restarted Again.
Crossed my fingers hope to die (just kidding), but was really was my last-last-last effort. And W00000h0000000000! It worked! I can access SQL Server from ALL CLIENT computers now. So the whole error message from untrusted domain was VERY misleading. The error might have been the client, but the really issue was the SQL Server was no longer trusted on the domain. Therefore when clients try to authenticate to SQL Server, the Kerberos broke down as it could not hop over to AD to verify credentials. So the error was caused by my own hands, like all other errors, heh, I need to stop messing around. But then I wouldn’t learn new and cool stuff.
Reference Links
- Microsoft. Source: MSSQLServer ID: 18452. Link.
- TechNet. The security database on the server does not have a computer account for this workstation trust relationship. Link.
I have a docker container that no longer allows me to authenticate using my host Windows credentials. When I try to run the CSIDE client I get this:
---------------------------
Microsoft Dynamics NAV Development Environment
---------------------------
The following SQL Server error or errors occurred:
18452,"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.
---------------------------
OK
---------------------------
When I try to run the Windows client I get this:
---------------------------
Microsoft Dynamics NAV
---------------------------
The program could not create a connection to the server. Do you want to try again?
---------------------------
Yes No
---------------------------
And the Web client will prompt for username/password three times at which point I get:
401 - Unauthorized: Access is denied due to invalid credentials.
You do not have permission to view this directory or page using the credentials that you supplied.
The docker container is healthy and I can launch a Powershell prompt on it and restart the NAV server instance, etc.
Is there anything you can suggest to try to re-establish the trust relationship or will I need to spin up a new container? It’s not a drama obviously, I’d just like to try to understand what broke it and what if anything could fix it.
Since it last worked I installed a new container (using NavContainerHelper) for NAV2018 using latest. The failing container is BC. Here are my containers:
IMAGE CREATED STATUS PORTS NAMES
microsoft/dynamics-nav:nz 3 days ago Up 7 minutes (healthy) 80/tcp, 443/tcp, 1433/tcp, 7045-7049/tcp, 8080/tcp NAV2018
microsoft/bcsandbox:12.0.21576.0-us 6 weeks ago Up 7 minutes (healthy) 80/tcp, 443/tcp, 1433/tcp, 7045-7049/tcp, 8080/tcp MyBCDevEnv2
I found a similar thing happened the last time I changed my Windows logon password but this has not happened. My NAV2018 container still works correctly.
Any ideas or suggestions?