1

I have the below code for creating a trusted connection to SQL Server and returning the results as a DataTable:

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()
    }
}

I'd like to enable it such that I can specify different domain credentials.

The Credential property on the connection object is for SQL credentials (System.Data.SqlClient.SqlCredential); which is not what I'm after; I want to impersonate a different domain user.

I could add code to run invoke-command 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...

Update

Based on some code found here I amended my code as follows

        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);
        }

However this didn't work / gave the error: Exception calling "Open" with "0" argument(s): "Login failed for user 'myDomain\myUsername'. 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.

NB: I have confirmed through SSMS that this user does have access to the database in question.

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.

JohnLBevan
  • 22,735
  • 13
  • 96
  • 178
  • Have you tried creating a thread and then just opening the connection inside of it? Here's an example of impersonating another user in a thread: http://stackoverflow.com/questions/2608194/how-do-i-start-a-thread-in-a-different-security-context – David P Aug 04 '15 at 12:16
  • Check this out: http://stackoverflow.com/questions/15619883/cannot-connect-to-sql-server-from-powershell-with-domain-credentials – websch01ar Aug 04 '15 at 12:55
  • @DavidP; thanks for the suggestion; I've not yet had a chance to test it out, but will do soon / will feed back once tested. – JohnLBevan Aug 07 '15 at 10:42
  • @websch01ar: Sadly none of those solutions work where you have domain credentials; I suspect in the cases listed people had created SQL credentials with names matching their domain account's name; but that doesn't play well if you wish to manage access via AD security groups / is a bit of a hack around. – JohnLBevan Aug 07 '15 at 10:43
  • @JohnLBevan why don't you pass the `DOMAIN\USER` as a parameter just like the server and database name into your connection string? ` $connectionString = ("Server={0};Database={1};Integrated Security=True;UID={3}" -f $DbInstance,$DbCatalog,$DomainUser` – draysams Sep 12 '18 at 07:52
  • Thanks @dope_vector. I'd tried that above, only with `domain\user` being the value of `$Credential.Username`. You can specify a domain username when using `integrated connection=SSPI`, but the system will ignore the values specified, so it's a bit pointless. More here: https://blogs.msdn.microsoft.com/spike/2008/11/14/connectionstrings-mixing-usernames-and-windows-authentication-who-goes-first/ – JohnLBevan Sep 12 '18 at 08:53
  • ps. the above link does provide a solution; use the ODBC client, setting up a DSN (data source name)... but I'm not a fan of having configuration outside of my app, so personally try to avoid going that way. – JohnLBevan Sep 12 '18 at 08:59

0 Answers0