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:
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.