Powershell: Run SQL command using different credentials

Hey!

Finally found time to post in here. Recently I’ve been working on a Powershell script that connects to a SQL 2016 database. Easy one, huh?

The problem may be using Invoke-Sqlcmd cmdlet when you’re using Windows authentication ONLY. In such cases, here’s a function I use to connect to SQL:

function Invoke-SqlCommand {
[CmdletBinding()]
param(
[Parameter(Mandatory=$true)] [string]$ServerInstance,
[Parameter(Mandatory=$true)] [string]$Query,
[Parameter(Mandatory=$true)] [string]$Username,
[Parameter(Mandatory=$true)] [string]$Password,
[Parameter( Mandatory=$false)] [Int32]$QueryTimeout=600,
[Parameter(Mandatory=$false)] [Int32]$ConnectionTimeout=15,
[Parameter(Mandatory=$false)] [ValidateSet("DataSet", "DataTable", "DataRow")] [string]$As="DataRow"
)

$ConnectionString = "Server={0};User ID={1};Password={2};Trusted_Connection=False;Integrated Security=SSPI;Connect Timeout={3}" -f $ServerInstance,$Username,$Password,$ConnectionTimeout
$conn=new-object System.Data.SqlClient.SQLConnection
$conn.ConnectionString=$ConnectionString

$conn.Open()
$cmd=new-object system.Data.SqlClient.SqlCommand($Query,$conn)
$cmd.CommandTimeout=$QueryTimeout
$ds=New-Object system.Data.DataSet
$da=New-Object system.Data.SqlClient.SqlDataAdapter($cmd)
[void]$da.fill($ds)
$conn.Close()
switch ($As) {
'DataSet' { return $ds }
'DataTable' { return $ds.Tables }
'DataRow' { return $ds.Tables[0] }
}
}

Usage is simple…

Invoke-SqlCommand -ServerInstance $ServerInstance -Query "QUERY GOES HERE" -Username sqladmin -Password SecretPwd123

Let me know if this worked for you!

 

Cheers!

Robot(ICT) guy

Published by

Lukas Vu

I started in IT with my own business since high school providing automated and centralized hosting solutions to end customers. Nowadays I'm focusing on corporate area, analyzing, managing and improving customer's IT environments. My main focus nowadays is automation and architectural improvements.

Leave a Reply

Your email address will not be published.