Invoke-SqlCmd with either Windows Authentication OR SQL Authentication
10,826
In my opinion the best solution here is called splatting (really). You build a hashtable with a set of parameters (potentially empty) and then use the splat operator (@) to present those arguments to the cmdlet.:
Function MyFunction{
Param
(
[string]$S = "MyServer",
[string]$U,
[string]$P
)
# Find the SQL Engine version
$Q = "select serverproperty('ProductVersion')"
if($U)
{
$auth=@{UserName=$u;Password=$p}
Write-Host "SQL Authenticated"
}
else
{
$auth=@{}
Write-Host "Windows Authenticated"
}
$R = Invoke-Sqlcmd -ServerInstance $S -Query $Q -Verbose @Auth
return $R.column1
}
See get-help about_splatting
for more information.
Author by
PollusB
I have been a SQL Server Architect, DBA for about 15 years.
Updated on July 27, 2022Comments
-
PollusB almost 2 years
This code does what I need. But I am trying to replace a call to Invoke-SqlCmd with either Windows Authentication or SQL Authentication on the same line using some kind of switch. Can someone help me with a more elegant way? I was thinking about Invoke-Expression but I don't get any good result out of it.
Function MyFunction{ Param ( [string]$S = "MyServer", [string]$U, [string]$P ) # Find the SQL Engine version $Q = "select serverproperty('ProductVersion')" if($U) { $R = Invoke-Sqlcmd -ServerInstance $S -Query $Q -Username $U -Password $P -Verbose Write-Host "SQL Athenticated" } else { $R = Invoke-Sqlcmd -ServerInstance $S -Query $Q -Verbose Write-Host "Windows Athenticated" } return $R.column1 }
Here is my Invoke-Expression. It just doesn't work...
Invoke-Expression "$R = Invoke-Sqlcmd -ServerInstance $S -Query $Q $(if($true){""-Username $U -Password $P""})"
Here is the elegant solution I was looking for. Thanks to Mike:
Function MyFunction{ Param ( [string]$S = "xps15", [string]$U, [string]$P ) # Find the SQL Engine version $Q = "select serverproperty('ProductVersion')" $auth=@{} if($U){$auth=@{UserName=$U;Password=$P}} $R = Invoke-Sqlcmd -ServerInstance $S -Query $Q -Verbose @Auth return $R.column1 }