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
