Invoke-SqlCmd with either Windows Authentication OR SQL Authentication

Question

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
}

does it have to be a single line solution? what about 2 lines? if so, first line will set a variable to the credentials desired, second line will be your invoke, utlizing the variable in the first line. I'm not the biggest fan of single line solutions for everything, it can make reading/interpreting more difficult. — Greg
– Greg, Commented Sep 10, 2015 at 21:59
So which code in your question doesn't work? The blob of code at the top or the Invoke-Expression bit at the end. If the later then why even mention the first blob? "It just doesn't work.." - in what way does it "just doesn't work?", do you get any error messages, if so why not post them. If you want folks to help you here then please learn how to ask a question because quite frankly I have no idea what you're asking: codeblog.jonskeet.uk/2010/08/29/writing-the-perfect-question - please don't waste our time. — Kev
– Kev, Commented Sep 10, 2015 at 23:34
This is actually a good question. Not wanting to express the Invoke-SQLCmd twice is natural, and there's a good PowerShell solution. — Mike Shepard
– Mike Shepard, Commented Sep 11, 2015 at 0:07

Matt · Accepted Answer · 2017-09-27 14:15:16Z

12

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.

edited Sep 27, 2017 at 14:15

Matt

47k9 gold badges90 silver badges125 bronze badges

answered Sep 11, 2015 at 0:05

Mike Shepard

18.4k7 gold badges59 silver badges72 bronze badges

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

1 Comment

Serge Wautier Over a year ago

I wish I could upvote this one more than once!

Collectives™ on Stack Overflow

Invoke-SqlCmd with either Windows Authentication OR SQL Authentication

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