CODEWITHSUNDEEP
sql-serverpowershellauthenticationwindows-authenticationscriptblock
seanmt13
seanmt13

Reputation: 422

PowerShell query remote SQL Server with different Windows Credentials

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: enter image description here

Upvotes: 0

Views: 3467

Answers (2)

seanmt13
seanmt13

Reputation: 422

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: [email protected]

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.

Upvotes: 1

Mike Garuccio
Mike Garuccio

Reputation: 2718

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.

Upvotes: 1

Related Questions