Skip to content

access sqlserver with different windows credentials in powershell

2011/02/27

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
{
param(
[string]$ServerInstance,
[string]$Database,
[string]$Query,
[Int32]$QueryTimeout=30
)

$conn=new-object System.Data.SqlClient.SQLConnection
$conn.ConnectionString=”Server={0},1897;Database={1};Integrated Security=True” -f $ServerInstance,$Database
$conn.connectionstring
$conn.Open()
$cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)
$cmd.CommandTimeout=$QueryTimeout
$dataset=New-Object system.Data.DataSet
$dataadapter=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
[void]$dataadapter.fill($dataset)
$dataset.Tables[0]
$conn.Close()
}

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

Advertisements

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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: