PowerShell query remote SQL Server with different Windows Credentials

Question

I am trying to query a SQL Server using a a script block and passing in a different set of windows credentials. The script works if i run it on the same machine as the SQL Server but fails when i try and run it remotely. I know i can access the SQL server remotely because i can access the SQL server with SQL Server Managment Studio and run the query manually. Below is the code i am trying to use. I specify the server, database, and query prior to the point in the script.

$credentials = Get-Credentials
#Script block
$sqlscript = {
    #SQL variables
    $sqlDataSource = $sqlServer
    $sqlDatabase = $database

    $connectionString = "Server="+$sqlDataSource+";Database="+$sqlDatabase+";Integrated Security=SSPI;"

    $connection = New-Object System.Data.SqlClient.SqlConnection
    $connection.ConnectionString = $connectionString

    $connection.Open()
    $command = New-Object System.Data.SqlClient.SqlCommand($queryString,$connection)
    $command.CommandTimeout=$queryTimeout

    # use dataset to get sql results
    $dataset=New-Object system.Data.DataSet
    $dataAdapter=New-Object system.Data.SqlClient.SqlDataAdapter($command)
    [void]$dataAdapter.fill($dataset)
    $connection.close()

    # dataset has separate results tables
    #$versionData = $dataset.Tables[0] #Version query results #not needed
    $hardwareData = $dataset.Tables[1] #Hardware query results
    $softwareData = $dataset.Tables[2] #Software query results


}
start-job -ScriptBlock $sqlscript -Credential $credentials
# output to separate CSV files
#$versionData | Export-CSV $outputPath -notype #not needed
$hardwareData | Export-CSV $outputPathHardware -notype
$softwareData | Export-CSV $outputPathSoftware -notype

This is the status of the job:

score 1 · Answer 1 · answered Nov 28 '16 at 20:13

1

Ahh the joys of second-hop remoting. Basically what's going wrong here is that after creating a remote powershell session you cannot access any resources on servers other than the one you are connected to since your credentials are not actually passed to the second server when you log in. The only real way around this is to use CredSSP authentication, which you can do using Enable-WSManCredSSP Make sure to read over the links as there are some specific requirements and potential issues introduced when using CredSSP compared with standard auth.

answered Nov 28 '16 at 20:13

Mike Garuccio

2,588
1
11
20

Maybe i am missing something. But i am not trying to start a remote session. I am creating a new session on the same machine as the PowerShell script. I am passing a different set of Windows Credentials than what i am logged in with to that session. If i remove the script block and just log into the machine with windows credentials that have access to the SQL server it works just fine. Even if i run the script from different machine than the SQL Server. – seanmt13 Nov 28 '16 at 21:51
It doesn't matter if the session is remote or local. The standard authentication for a session which was provided credentials does not pass along the username and password, so they cannot be used to connect to another system. – Mike Garuccio Nov 28 '16 at 22:09
Actually credentials do get passed when you invoke Start-Job. That is the whole point of using the script-block. I can call that with a different credentials than the current session. Here are the links that pointed me in the right direction. [MSDN Start-Job](https://msdn.microsoft.com/en-us/powershell/reference/5.1/microsoft.powershell.core/start-job) [SQL Connection with Start-Job](http://arthurremy.com/index.php/108-tips/338-access-sql-server-integrated-security-with-different-windows-credentials-in-powershell) – seanmt13 Nov 28 '16 at 22:57

score 1 · Accepted Answer · answered Nov 28 '16 at 22:52

So i figured out what the issue was. As i stated earlier in my comment I was correct the new session when invoking Start-Job does pass the parameters. If you look at SQL connection properties and no user/password is passed it will attempt to authenticate via windows authentication with the credentials you passed to the new session. The issue was my own fault in get-credentials i supplied the username and password as follows.

User: user@domain.local

Password: mypassword

The issue is that it needs to be like this:

User: DOMAIN\user

Password: mypassword

I am not sure why that makes a difference but it does. I thought both of those syntax's are the same but maybe not with SQL Windows Authentication.

PowerShell query remote SQL Server with different Windows Credentials

2 Answers2