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
