Skip to content

access sqlserver with different windows credentials in powershell


I needed to get data out of a SQL Server instance that runs on a SQL cluster. That DB used a custom port and integrated windows security. The user that has access to the database is a domain user account different to the user logged on when running the script. The user with the SQL access has permissions on my workstation and normally when I need to get data from the database, I use MS access started with runas different user and that account.

That brought me to the idea to implement this mechanic into a posh script by using jobs. I put the SQL access cmds into the $sqlscript variable and invoke it as the scriptblock of a job. That job can be started with different credentials.

As this is a prove of concept it is okay for me that

I have to input the credentials manual and
that the SQL server port (1897) is hardcoded
That I run only that job, so I do not need to use a specific job name but can wait and receive all jobs

$sqlscript = {
function Invoke-Sqlcmd2

$conn=new-object System.Data.SqlClient.SQLConnection
$conn.ConnectionString=”Server={0},1897;Database={1};Integrated Security=True” -f $ServerInstance,$Database
$cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)
$dataset=New-Object system.Data.DataSet
$dataadapter=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)

invoke-sqlcmd2 -server 'sqlserver' -database 'dbinstance003'  -query "select * from userstable where user_id = 'MIKE'"

start-job -ScriptBlock $remotescript1 -Credential (Get-Credential -Credential "addomain\slquser1")
get-job *
wait-job *
receive-job *

The SQL code is based on an idea found on powershellreflections which was mentioned by powerscripting podcast episode 140


From → powershell, sql

One Comment
  1. Thanks for the pingback!

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: