title: Create SQL Connection With Different Domain Credentials
body: <p>I have the below code for creating a trusted connection to SQL Server and returning the results as a DataTable:</p>
  
  <pre><code>function Execute-SQLQuery {
      param (
          [Parameter(Mandatory = $true)]
          [string]$DbInstance
          ,
          [Parameter(Mandatory = $true)]
          [string]$DbCatalog
          ,
          [Parameter(Mandatory = $true)]
          [string]$Query
          ,
          [Parameter(Mandatory = $false)]
          [int]$CommandTimeoutSeconds = 30 #this is the SQL default
          ,
          [Parameter(Mandatory = $false)]
          [System.Management.Automation.Credential()]
          [System.Management.Automation.PSCredential]$Credential=[System.Management.Automation.PSCredential]::Empty
      )
      begin {
          $connectionString = ("Server={0};Database={1};Integrated Security=True;" -f $DbInstance,$DbCatalog)
          $connection = New-Object System.Data.SqlClient.SqlConnection
          $connection.ConnectionString = $connectionString
          #$connection.Credential = $Credential #this is for SQL credentials only
          $connection.Open()    
      }
      process {
          $command = $connection.CreateCommand()
          $command.CommandTimeout = $CommandTimeoutSeconds
          $command.CommandText = $query
          $result = $command.ExecuteReader()
          $table = new-object “System.Data.DataTable”
          $table.Load($result)
          Write-Output $table
      }
      end {
          $connection.Close()
      }
  }
  </code></pre>
  
  <p>I'd like to enable it such that I can specify different domain credentials.</p>
  
  <p>The <code>Credential</code> property on the connection object is for SQL credentials (<code>System.Data.SqlClient.SqlCredential</code>); which is not what I'm after; I want to impersonate a different domain user.</p>
  
  <p>I could add code to run <code>invoke-command</code> using the credential, then call this function within that; however there's a bad smell to that solution / I'm certain something better should exist...</p>
  
  <p><strong>Update</strong></p>
  
  <p>Based on some code found <a href="https://gallery.technet.microsoft.com/scriptcenter/SQL-PowerShell-Module-0d1e38ec" rel="nofollow">here</a> I amended my code as follows</p>
  
  <pre><code>        if($Credential -and ($Credential -ne [System.Management.Automation.PSCredential]::Empty)) {
              #remove integrated security to allow credential to be specified
              $connectionString = ("Server={0};Database={1};" -f $DbInstance,$DbCatalog) #Integrated Security=True;
  
              #make password read only to allow for conversion
              $Credential.Password.MakeReadOnly();
              #convert to SQL credential and assign to credential property
              $connection.Credential = New-Object System.Data.SqlClient.SqlCredential($Credential.UserName, $Credential.Password);
          }
  </code></pre>
  
  <p>However this didn't work / gave the error: <code>Exception calling "Open" with "0" argument(s): "Login failed for user 'myDomain\myUsername'</code>.
  I assume this is what I'd originally thought; i.e. it's expecting an actual SQL user rather than just a user with access to the SQL DB.</p>
  
  <p>NB: I have confirmed through SSMS that this user does have access to the database in question.</p>
  
  <p>For now my workaround is to leave the credential parameter with its default value (leaving it in place such that it's easy to implement this functionality later if desired; even though having this parameter do nothing may be misleading) and am running my powershell ISE session as the relevant user account; not ideal, but sufficient for my immediate needs.</p>