title: pass username and password to get-credential or run sql query without using invoke-sqlcmd in Powershell
body: <p>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.</p>
  
  <p>Here's the code I'm using:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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"?</p>
  
  <p>If so...what are my alternatives?</p>
  
  <p>Thanks so much for you help</p>
  
  <p>Emo</p>