pass username and password to get-credential or run sql query without using invoke-sqlcmd in Powershell
powershell, powershell-cmdlet, sql-server-2008
Solution
Your command isn't working because the -password attribute is expecting the password as a plain-text string, not the SecureString value you're passing it.
Although it looks like you're trying to use two different methods for executing the SQL - ie using the assemblies as well as using invoke-sqlcmd.
If using invoke-sqlcmd, you don't need to load the assemblies and do all the other stuff.
All you need to do to get your invoke-sqlcmd to work is to convert your password to plain-text:
$serverName = "HLSQLSRV03"
$DB = "Master"
$credential = Get-Credential
$userName = $credential.UserName.Replace("\","")
$pass = $credential.GetNetworkCredential().password
invoke-sqlcmd -query "select @@Servername" -database $DB -serverinstance $servername -username $username -password $pass
Problem
I am trying to connect to a remote sql database and simply run the "select @@servername" query in Powershell. I'm trying to do this without using integrated security. I've been struggling with "get-credential" and "invoke-sqlcmd", only to find (I think), that you can't pass the password from "get-credential" to another Powershell cmdlets. Here's the code I'm using: ``` add-pssnapin sqlserverprovidersnapin100 add-pssnapin sqlservercmdletsnapin100 # load assemblies [Reflection.Assembly]::Load("Microsoft.SqlServer.Smo, ` Version=9.0.242.0, Culture=neutral, ` PublicKeyToken=89845dcd8080cc91") [Reflection.Assembly]::Load("Microsoft.SqlServer.SqlEnum, ` Version=9.0.242.0, Culture=neutral, ` PublicKeyToken=89845dcd8080cc91") [Reflection.Assembly]::Load("Microsoft.SqlServer.SmoEnum, ` Version=9.0.242.0, Culture=neutral, ` PublicKeyToken=89845dcd8080cc91") [Reflection.Assembly]::Load("Microsoft.SqlServer.ConnectionInfo, ` Version=9.0.242.0, Culture=neutral, `PublicKeyToken=89845dcd8080cc91") # connect to SQL Server $serverName = "HLSQLSRV03" $server = New-Object -typeName Microsoft.SqlServer.Management.Smo.Server -argumentList $serverName # login using SQL authentication $server.ConnectionContext.LoginSecure=$false; $credential = Get-Credential $userName = $credential.UserName -replace("\\","") $pass = $credential.Password $server.ConnectionContext.set_Login($userName) $server.ConnectionContext.set_SecurePassword($credential.Password) $DB = "Master" invoke-sqlcmd -query "select @@Servername" -database $DB -serverinstance $servername -username $username -password $pass ``` If if just hardcode the password in at the end of the "invoke-sqlcmd" line, it works. Is this because you can't use "get-credential" with "invoke-sqlcmd"? If so...what are my alternatives? Thanks so much for you help Emo