How to use sqlcmd.exe in windows-auth no-trust cross-domain scenario?

Question

I have a somewhat complex deployment scenario to deal with. I have two domains, production and test, which have no trust with one another. I'm implementing automated deployments from the production domain to the test domain, and the databases in the test domain use windows authorization only.

We use sqlcmd.exe to deploy the actual scripts. We've attempted to use Invoke-SqlCmd, but it's not suitable for use in its current state, and we're not considering it any longer.

The original idea was just to use powershell remoting to connect to the server in the test domain, authenticating with credentials using Get-Credential. Then we invoke sqlcmd.exe (using the remote session's test domain credentials) to execute the scripts. However, we quickly ran into the "double hop" scenario common to these types of scenarios: the remote session was not allowed to pass credentials to SQL Server via sqlcmd.

Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'..

The next idea was to configure our database servers to support CredSSP, so that the remote session could pass its credentials via sqlcmd. However, it seems that sqlcmd.exe is able to detect CredSSP sessions and terminates on trust issues.

Sqlcmd: Error: Microsoft SQL Server Native Client 11.0 : Login failed. The login is from an untrusted domain and cannot be used with Windows authentication..

So finally, the solution we're using is to remote into a server in the test domain (explicitly not the database server) using CredSSP, then using sqlcmd to execute the scripts against the database server. As long as sqlcmd isn't invoked on the same machine it's connecting to, it seems to work.

Somehow I can't help but think that I'm doing something wrong, and that this shouldn't be so complicated. However, I can't seem to find much information out there about situations such as these. Any ideas?

Answer 1

We finally figured this out. The answer is still to gather credentials for the no-trust domain using Get-Credential, and then we use New-PSSession -Authentication CredSSP to open the session to the sql server machine. The catch was that, since we know we're executing sqlcmd.exe locally on the target sql server, we omit the server name from the command line so that we avoid the security checks that are apparently invoked in a remote session.

Answer 2

Windows authentication will only work in localhost or domain environments. Use a SQL login and password in your command line when running sqlcmd from an untrusted location.