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.

 

SQL 2012 Import - Unknown column type conversion

Importing a flat file (CSV) into MS SQL 2012 should be a straightforward task, one would think.  That is, until you run into a brick wall that reads "Found 1 unknown column type conversion(s).  You are only allowed to save the package."

Thanks, SQL.

The problem, if you bother to look at the details of the offending column (it will be the one with the big red X next to it), is that SQL has no idea how to convert from the incoming column - usually DT_STR - into your desired destination column.  For me, my destination was a datetime2 column which seems to be DT_TIMESTAMP2 in the SQL conversion world.

All of this is powered by an XML file located called DtwTypeConversion.xml located at either C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn or C:\Program Files\Microsoft SQL Server\110\DTS\Binn  (older/newer version of SQL might use it also but they'd be in a different subfolder obviously.. if in doubt, search for the file).

Inside that file if you look down for a SourceType of DT_STR you will see all of the types it can convert to, and the "steps" for converting them.  You will see an entry for DT_TIMESTAMP but not one for DT_TIMESTAMP2.

Add one.

The conversion looks like you can configure several steps if you need to convert to intermediary datatypes before getting to your end goal.  For me, one step was enough.  I just copied the entry for DT_TIMESTAMP and changed it to DT_TIMESTAMP2.

After that, you will get the regular yellow exclamation point instead of the big red x, and you can run your import and move on to solving the next error.