0

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?

bwerks
  • 8,651
  • 14
  • 68
  • 100

2 Answers2

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.

Metaphor
  • 6,157
  • 10
  • 54
  • 77
  • It is a domain environment though. It's just that we have two different domain environments involved with no trust relationship between them. – bwerks Mar 28 '14 at 21:01
  • Pretty much the same thing. SQL login is the way to go. – Metaphor Mar 28 '14 at 21:07
  • Since the domains have no trust relationship your domain credentials mean nothing when crossing from one to the other. It's equivalent to getting pulled over for speeding and handing the cop your Social Security Card when he asks for your license. Sure, it's legal ID, but it's not any credentials that he can accept to verify who you are. – TheMadTechnician Mar 28 '14 at 21:44
  • You can establish remote sessions to no-trust domains using New-PSSession with credentials from a provider such as Get-Credential. I've edited the original post to make this clearer. – bwerks Mar 28 '14 at 22:36
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.

bwerks
  • 8,651
  • 14
  • 68
  • 100
  • Omitting -S from sqlcmd made it all finally work for me. I had spent hours trying to execute sqlcmd -E within Invoke-Command on computer A, targeting computer B, where the two are not in a domain. No double hops in my case, so -Authentication Default works fine. – Steve Lang Oct 15 '16 at 13:35