Test non-trusting cross-domain Windows authentication to SQL using PowerShell

Sometimes I want to simulate connectivity from an application another way, usually for troubleshooting or verifying networks and accounts have been set up correctly. One thing that’s always been difficult is testing I can connect to a SQL database in a non-trusting domain, using an AD account in the other domain. I can’t hardcode credentials in the connection string, as that’s only for SQL accounts, and I can’t use RUNAS with the foreign account.

Then I read about RUNAS /NETONLY which just runs the over-the-network parts as a different account, so in this post I’m going to share a simple SQL query script, which does not need the SQL Management client or full SQL module installed, and can be used with RUNAS /NETONLY.

I’ve posted the script here: https://github.com/missmiis/Scripts/blob/master/SimpleSQLQuery

(Note I have only just set up this github repository – hopefully I’ve done it right!)

The script is in the form of a module which has to be loaded, however it only contains the one function, which runs a query against the specified SQL database.

To run using an account in non-trusted domain:

1. Open a PowerShell command prompt using RUNAS /NETONLY and specifying the credentials of the other domain’s account that has rights to the database. Note you need to enter the password interactively:

runas /netonly /user:DOMAIN\username powershell.exe

2. CD to the folder containing the script and import the module:

Import-Module .\SimpleSQLQuery.psm1

3. Run the Invoke-SQLQuery function, specifying the full server FQDN (which may include a port if required by firewall rules – ie., sqlserver.other.domain,port), the instance if required, the DB name, and the SQL query:

$Response = Invoke-SQLQuery -Server SqlServerFQDN [-Instance InstanceName] -Database DBName -Query "select * from mytable" -Verbose

If all goes well you should get the results of the SQL query stored in the $Response parameter.