asked
Using Powershell Credentials to connect to SQL Server
I'm a powershell newbie and have got all my code working, however I developed using open text password. I'm now changing that to use encrypted password and I'm getting login failures. My code looks like this: $Path = "D:\Scripts\Powershell\password.txt" $UID = 'WebReportUser' $PWD = Get-Content D:\Scripts\Powershell\Password.txt | ConvertTo-SecureString $conn = New-Object System.Data.SqlClient.SqlConnection $conn.ConnectionString = "Server=localhost;Database=MA_Reporting;User ID=$UID;Password=$PWD;" $conn.Open() $sql = "SELECT sServerName from ServerConfiguration" $cmd = New-Object System.Data.SqlClient.SqlCommand($sql,$conn) $rdr = $cmd.ExecuteReader() $test = @() while($rdr.Read()) { $test += ($rdr["sServerName"].ToString()) } Write-Output $test This is just a test script to get the credentials working. I have saved the encrypted password in a file using this: "Password" | ConvertTo-SecureString -AsPlainText -Force | ConvertFrom-SecureString | Out-File "D:\Scripts\Powershell\Password.txt" But it's failing saying login failed, however if I change the $PWD to the actual password it works fine, can someone please advise what I've done wrong please. Many thanks
Ok, I am not sure if I will be able to explain this in way it will be easy to understand...but here we go. The method in which you are building out the string where you include the user name and password would require that you pass in the password as a string, and not a secure string. So to get your code to work would require you to build out your credentials and pass the password back out as plain text. This is obviously not what you are wanting to do. You will need to build your connection utilizing `SqlCredential` which allows a secure string to be passed. Something like the below code worked on my instance. Using `System.Data.SqlClient.SqlCredential` I can build the authentication required in a secure manner where it is not readable over the wire. You then simply do not include the UID and PWD in your connection string property. Instead use the `Credential` property, that requires a `SqlCredential` object to be passed. [string]$uid = "TestLogin1" $pwd = "P@ssword" | ConvertTo-SecureString -AsPlainText -Force $pwd.MakeReadOnly() $creds = New-Object System.Data.SqlClient.SqlCredential($uid,$pwd) $con = New-Object System.Data.SqlClient.SqlConnection $con.ConnectionString = "Server=MANATARMS\SQL12;Database=AdventureWorks2012;" $con.Credential = $creds $con.Open() $sql = "SELECT @@SERVERNAME AS ServerName" $cmd = New-Object System.Data.SqlClient.SqlCommand($sql,$con) $rdr = $cmd.ExecuteReader() while($rdr.Read()) { $rdr["ServerName"].ToString() } $con.Close()
Thank you, that works perfectly. I knew I had to be doing something wrong with the connection.
Have you checked that $PWD contains the plain text password you encrypted? I'd start with that. The next bit I'd look at is where you set the ConnectionString. It doesn't look like the sort of syntax that involves substituting the values of the variables into the string. I have a nasty feeling you are setting it to the literal string "Server=localhost;Database=MA_Reporting;User ID=$UID;Password=$PWD;". Presumably you need to do something like "Server=localhost;Database=MA_Reporting;User ID=" + $UID + ";Password=" + $PWD + ";".
Thank you, I have just tried that and it gives the same error message. When I put in write-output $UID it returns the correct username and when I do write-output $PWD it returns system.security.securestring as expected.
Ah. Perhaps this will help:
http://social.technet.microsoft.com/wiki/contents/articles/4546.working-with-passwords-secure-strings-and-credentials-in-windows-powershell.aspx. It looks as if you have to use InterOp stuff to get the plain text back out of the secure string.
16 People are following this question.
