{"id":3039,"date":"2017-03-23T22:30:00","date_gmt":"2017-03-23T22:30:00","guid":{"rendered":"https:\/\/www.wapshere.com\/missmiis\/?p=3039"},"modified":"2017-03-23T22:30:00","modified_gmt":"2017-03-23T22:30:00","slug":"test-non-trusting-cross-domain-windows-authentication-to-sql-using-powershell","status":"publish","type":"post","link":"https:\/\/www.wapshere.com\/missmiis\/test-non-trusting-cross-domain-windows-authentication-to-sql-using-powershell","title":{"rendered":"Test non-trusting cross-domain Windows authentication to SQL using PowerShell"},"content":{"rendered":"<p>Sometimes I want to simulate connectivity from an\u00c2\u00a0application another way, usually for troubleshooting or verifying networks and accounts have been set up correctly. One thing that&#8217;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&#8217;t hardcode credentials in the connection string, as that&#8217;s only for SQL accounts, and I can&#8217;t use RUNAS with the foreign account.<\/p>\n<p>Then I read about RUNAS \/NETONLY which just runs\u00c2\u00a0the over-the-network\u00c2\u00a0parts as a different account, so in this post I&#8217;m going to share a simple SQL query script, which does not need the SQL Management client or full SQL\u00c2\u00a0module installed, and\u00c2\u00a0can be used with RUNAS \/NETONLY.<!--more--><\/p>\n<p>I&#8217;ve posted the script here:\u00c2\u00a0<a href=\"https:\/\/github.com\/missmiis\/Scripts\/blob\/master\/SimpleSQLQuery.psm1\">https:\/\/github.com\/missmiis\/Scripts\/blob\/master\/SimpleSQLQuery<\/a><\/p>\n<p>(Note I have only just set up this github repository &#8211; hopefully I&#8217;ve done it right!)<\/p>\n<p>The script is\u00c2\u00a0in 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.<\/p>\n<p>To run using an account in non-trusted domain:<\/p>\n<p>1. Open a PowerShell command prompt using RUNAS \/NETONLY and specifying the credentials of the other domain&#8217;s account that has rights to the database. Note you need to enter the password interactively:<\/p>\n<pre style=\"padding-left: 60px;\">runas \/netonly \/user:<em>DOMAIN<\/em>\\<em>username<\/em> powershell.exe<\/pre>\n<p>2. CD to the folder containing the script and import the module:<\/p>\n<pre style=\"padding-left: 60px;\">Import-Module .\\SimpleSQLQuery.psm1<\/pre>\n<p>3. Run the Invoke-SQLQuery function, specifying the full server FQDN (which may include a port if required by firewall rules &#8211; ie.,\u00c2\u00a0<em>sqlserver.other.domain,port<\/em>), the instance if required, the DB name, and the SQL query:<\/p>\n<pre style=\"padding-left: 60px;\">$Response = Invoke-SQLQuery -Server SqlServerFQDN [-Instance InstanceName] -Database DBName -Query \"select * from mytable\" -Verbose<\/pre>\n<p>If\u00c2\u00a0all goes well you should get the results of the SQL query stored in the $Response parameter.<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Sometimes I want to simulate connectivity from an\u00c2\u00a0application another way, usually for troubleshooting or verifying networks and accounts have been set up correctly. One thing that&#8217;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&#8217;t hardcode credentials in&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"footnotes":"","jetpack_publicize_message":"","jetpack_is_tweetstorm":false,"jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":[]},"categories":[23,5,64],"tags":[],"class_list":["post-3039","post","type-post","status-publish","format-standard","hentry","category-powershell","category-sql","category-troubleshooting"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/pkp1o-N1","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/posts\/3039","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/comments?post=3039"}],"version-history":[{"count":8,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/posts\/3039\/revisions"}],"predecessor-version":[{"id":3047,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/posts\/3039\/revisions\/3047"}],"wp:attachment":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/media?parent=3039"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/categories?post=3039"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/tags?post=3039"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}