Execute SQL command with powershell and specifying the domain account

Question

I am putting together some powershell scripts to execute SQL commands against SQL Server and need to specify the windows account to use in the connectionstring. I have tried the following which does not work.

$SQLServer = ".\sqlexpress"
$SQLDBName = "dbname"
$uid ="domain\user
$pwd = "password"
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = false; User ID = $uid; Password = $pwd;"
$SqlCmd.Connection = $SqlConnection
$SqlConnection.Open();

.... Execute query...

I get the following error.

Exception calling "Open" with "0" argument(s): "Login failed for user 'Domain\user'.

I was lead to understand that if you specify false on integrated security then is would take the username and password and connect using the corresponding windows account.

For me this is failing to connect. Am I missing something here ?

Many thanks

Darren

Ok, I have done some more reading and understand now that the domain account cannot be specified. I need to look at either creating a SQL account or somehow impersonating the domain user. — zybroxz
– zybroxz, Commented May 11, 2016 at 21:04
You probably should just create the SQL account, and make the SQL account name similar to your program name. — Chuck
– Chuck, Commented May 11, 2016 at 21:16
How we get around this is we use "Integrated Security=true" and execute the PowerShell script using a service account. I suppose you could also invoke a PSSession passing in a PSCredential object. — websch01ar
– websch01ar, Commented May 11, 2016 at 22:00

Tyler Helder · Accepted Answer · 2016-06-12 00:22:27Z

1

Another way to tackle this would be to use the SQL PowerShell Module (SQLPS) which can be obtained by installing "Management Tools" from the SQL Management Studio Installer. By default, when using Invoke-Sqlcmd it will connect as the windows account that initiated the PowerShell instance. If you wish to run as another windows account other than the one you are signed in with, you would shift + right click PowerShell and choose option to "run as different user".

Once Module has been installed, you can import module by executing:

Import-Module SQLPS

Next you would execute your query like the following:

Invoke-Sqlcmd -ServerInstance "ServerName\Instance" -Database "DatabaseName" -Query "Some Query Here"

While entirely optional, I would recommend adding -Verbose as this will show you output just as you would see in Sql Management Studio.

Hope this helps.

answered Jun 12, 2016 at 0:22

Tyler Helder

6444 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

zybroxz Over a year ago

Thanks for your time answering. I will have a look at this. However, I need run this on a remote box, so won't be able to do the runas thing. I changed the way I approached the problem by creating a sql login that I now use in a sqlcommand connection string. Which solved the problem, although would have preferred to have impersonated a domain account. Thanks

Collectives™ on Stack Overflow

Execute SQL command with powershell and specifying the domain account

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related