Two Things

Gaming and Programming.. maybe programming for some games, who knows. Either way, I'm a geek, right?

SQL Connect Failure with Windows Authentication

Login failed.  The login is from an untrusted domain and cannot be used with Windows authentication.  (Microsoft SQL Server, Error: 18452)

That's how it starts.  The headache.

The Confusion

I work in a two-domain environment, and as far as I could tell the trusts were in place, my accounts on both domains were admin-level and I wasn't fat-fingering anything since I was trying to connect with Windows Authentication.

I was obviously logged into Windows successfully. 

I could connect to shared folders located on the server, which uses Windows Authentication.

I could connect to the server via Remote Desktop and connect to SQL on that server using Windows Authentication.

So what gives?  Why wouldn't my local SQL Management Studio (and Visual Studio too) connect to the server's SQL instance using Windows Authentication?

Not-So-Useful Troubleshooting

Looking on the server's Event Log, I found two entries for the authentication attempt:

EventID 17806 ERROR: SSPI handshake failed with error code 0xc0000413, 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.

EventID 18452 INFORMATION: Login failed.  The login is from an untrusted domain and cannot be used with Windows Authentication.

They both seem to imply a trust issue, but the domains trust each other. 

Googling around didn't help much.  Lots of advice and things to look at and try, all of which I did.

Here's what I did to solve my situation, ignoring the 3 hours of conversation with our IT guys and surfing google.

Useful Troubleshooting

Using nltest in debug mode  (nltest /dbflag:0x2080ffff)  (NOTE: must run cmd as an administrator for this to work)

I captured the login and reviewed the log file (located in %sysroot%/Windows/Debug/netlogon.log) then turned off nltest debug to stop the spam collection (nltest /dbflag:0x0)

The log file showed something interesting - the windows account that was connecting to SQL was NOT the account that I was logged into Windows with, and it did not seem to be authenticating with the domain controller.

Huh?

Well, I use a VPN to connect to this network, and the account that I use to connect the VPN is on Domain A, but my server (and my windows login) is on Domain B.  I guess the VPN account is being used when I try to connect to the remote server via the VPN.

Long story short - my account on Domain A did not have the right to Authenticate on the server that I was trying to connect to.

The Fix

Went into AD, enabled Advanced Features (under View), found the server in the computer list, Properties, Security, added my Domain A account with Allow to Authenticate checked.

Problem solved.

 

Loading