PowerShell: invoke-sqlcmd with Get-Credential doesn't work

Question

I've never seen a so easy script failing so royally:

$SQLServer = "localhost"
$cred = Get-Credential
invoke-sqlcmd -ServerInstance $SQLServer -Credential $cred -Query "select @@version"

the phrase says -Credentials is not recognized:

Invoke-Sqlcmd : A parameter cannot be found that matches parameter name 'Credential'.

So what is the point to have Get-Credential? I see a lot of examples on internet and they all use it this way.

EDIT EXAMPLE: why this code is working with -Credential? Because -Credential is inside a function?

function Pause ($Message="Press any key to continue..."){ 
    "" 
    Write-Host $Message 
    $null = $Host.UI.RawUI.ReadKey("NoEcho,IncludeKeyDown") 
} 

function GetCompName{ 
    $SQLServer = Read-Host "Please enter a computer name or IP" 
    CheckHost 
} 

function CheckHost{ 
    $ping = gwmi Win32_PingStatus -filter "Address='$SQLServer'" 
    if($ping.StatusCode -eq 0){$pcip=$ping.ProtocolAddress; GetCollation} 
    else{Pause "Host $SQLServer down...Press any key to continue"; GetCompName} 
} 


function GetCollation {
    #Provide Database Name 
    $DatabaseName ="master"
    #Prompt for user credentials 
    $credential = Get-Credential

    $Query = "SELECT name, collation_name FROM sys.databases;  " 

    invoke-sqlcmd -ServerInstance $SQLServer -Database $DatabaseName -Credential $credential  -Query $Query | Format-Table

}
#---------Start Main-------------- 
$SQLServer = $args[0] 
if($SQLServer){CheckHost} 
else{GetCompName}

there is a username and credential param. if you look at this article this is what they used to do what you are asking: stackoverflow.com/questions/7154122/… — Thom Schumacher
– Thom Schumacher, Commented Jul 31, 2018 at 21:39
This seems really odd. If you run the code manually instead of calling the script, does it work then? When you run Get-Credential you should be promted to enter both the username and the password for the new credential object, are you promted for both? — Henrik Stanley Mortensen
– Henrik Stanley Mortensen, Commented Jul 31, 2018 at 21:57
@HenrikStanleyMortensen, I get prompted if invoke-sqlcmd and Get-Credential are inside a function. It's hilarious but outside a function they don't get executed. And more hilarious -Credentials seems not recognized — Francesco Mantovani
– Francesco Mantovani, Commented Jul 31, 2018 at 22:20

goTo-devNull · Accepted Answer · 2018-07-31 23:33:58Z

16

The issue could be resulting from the fact that Microsoft has two versions of Invoke-Sqlcmd:

The Database Engine - no -Credentials parameter.
The SqlServer module - -Credentials parameter is available.

Looked at a couple of your recent SO questions - looks like you have the Database Engine version of the cmdlet. The the SqlServer module is not installed by default, so you have to do it manually. (there's a 'Note' section in previous hyperlink that explains some of the history behind this issue)

In a nutshell, run the following command to get the the SqlServer module:

Install-Module -Name SqlServer -AllowClobber

Make sure to include the -AllowClobber switch. It's a dumb-installer, and if you leave off the switch it will download the ~24MB package and then fail because it's overwriting the database engine version.

edited Jul 31, 2018 at 23:33

answered Jul 31, 2018 at 23:22

goTo-devNull

9,3923 gold badges46 silver badges57 bronze badges

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

2 Comments

Francesco Mantovani Over a year ago

As it was already installed I run Update-Module -Name SqlServer this fixed my issue

E.V.I.L. Over a year ago

You like to The Database Engine is broken. It might be referring to sqlps cmdlets: learn.microsoft.com/en-us/powershell/module/sqlps/…

Collectives™ on Stack Overflow

PowerShell: invoke-sqlcmd with Get-Credential doesn't work

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related