Boring article about fixing a 'Login failed... untrusted domain' issue when connecting to SQL Server.

This will be the most boring and dry technical post I've ever written. I promise. I'm only writing it down because I hear Jeff Atwood's voice in my head telling me that if I haven't shared the solution then there's almost no value in solving it.

So here was the problem...

On one of my machines, in recent months, anytime I tried to run an asp.net application that attempts to connect to a SQL Server database I receive this error:

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

This is in a scenario where everything is local: I'm on a home computer, using a local database server. And it doesn't matter if I'm using IIS, webdevserver, or IIS Express. It doesn't matter if I'm trying to connect to SQL Express or SQL Server 2008 r2. I can enter the credentials using the machine name or IP address. The result is the same.

Looking in the Windows Event Viewer I see:

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. [CLIENT: ].

Most of the 'solutions' online were not applicable because they involve domain issues. This is just a local machine.

One particular forum message involved a guy with a similar setup to me. He solved the problem for himself, but left behind only this very abbreviated and somewhat cryptic explanation:

Solved. Traced the prob. to my net setup. The clue was this a msg in the Win System Log from Lsasrv about the target name and it showed the fqdn. Did some checks and the fqdn didn't look right, cleaned up my network config (I had customized it for another project), and now it's workin.

The 'fqdn' is the fully qualified domain name. I found three different ways to find my fqdn, and one of them disagreed with the others! So this was a smoking gun.

Method 1 for finding the fqdn: When I right clicked on "computer" in the start menu and looked at the properties, the 'full computer name' was reported as 'Leon_xps'.

Method 2 for finding the fqdn: When I ran "ipconfig /all" and got these values for Host Name and Primary Dns Suffix:

   Host Name . . . . . . . . . . . . : Leon_xps
   Primary Dns Suffix  . . . . . . . : 

(Add them together and you get a fqdn of just 'Leon_xps'

Method 3 for finding the fqdn: In cmd when I ran 'ping -a Leon_xps' I saw a very unexpected result!

It said:

Pinging leonx_xps [192.168.1.2] with 32 bytes of data:
Request timed out.
Request timed out.
Request timed out.
Request timed out.

Notice that it's changed the name 'leon_xps' into 'leonx_xps' and come up with the ipaddress of '192.168.1.2'. instead of what I expected (such as a loopback address, 127.0.0.1... or some IPv6 nonsense)

There's only one kind of alchemy I know of for turning a good name into a crazy ipaddress: and that's the magic of the HOSTS file.

So I looked in there for 'leonx_xps' and I found it. Found this crazy nonsense line, probably added by myself during some other wild-goose-chase for a solution to some other messed up problem:

192.168.1.2 leonx_xps leon_xps

Once I'd commented out that rule, the asp.net application immediately started to connect succesfully to SQL Server 2008 r2. And I can move forward with my testing of Massive and Dapper.

But just mentioning the probable cause of this (not cleaning up during an earlier problem-panic) I remember that earlier tonight, while frantically trying to fix this problem I added some stupid rules to the windows firewall. I had better go and remove them now before my computer gets owned. Hey wait a moment!? Why is my CPU at 100%? Be right back...

My book "Choose Your First Product" is available now.

It gives you 4 easy steps to find and validate a humble product idea.

Learn more.