Encrypting your SQL Server Passwords in Powershell
- by laerte
A couple of months ago, a friend of mine who is now bewitched by the seemingly supernatural abilities of Powershell (+1 for the team) asked me what, initially, appeared to be a trivial question:
"Laerte, I do not have the luxury of being able to work with my SQL servers through Windows Authentication, and I need a way to automatically pass my username and password. How would you suggest I do this?"
Given that I knew he, like me, was using the SQLPSX modules (an open source project created by Chad Miller; a fantastic library of reusable functions and PowerShell scripts), I merrily replied, "Simply pass the Username and Password in SQLPSX functions". He rather pointed responded:
"My friend, I might as well pass:
Username-'Me'-password 'NowEverybodyKnowsMyPassword'"
As I do have the pleasure of working with Windows Authentication, I had not really thought this situation though yet (and thank goodness I only revealed my temporary ignorance to a friend, and the embarrassment was minimized).
After discussing this puzzle with Chad Miller, he showed me some code for saving passwords on SQL Server Tables, which he had demo'd in his Powershell ETL session at Tampa SQL Saturday (and you can download the scripts from here). The solution seemed to be pretty much ready to go, so I showed it to my Authentication-impoverished friend, only to discover that we were only half-way there:
"That's almost what I want, but the details need to be stored in my local txt file, together with the names of the servers that I'll actually use the Powershell scripts on. Something like:
Server1,UserName,Password
Server2,UserName,Password"
I thought about it for just a few milliseconds (Ha! Of course I'm not telling you how long it actually took me, I have to do my own marketing, after all) and the solution was finally ready.
First , we have to download Library-StringCripto (with many thanks to Steven Hystad), which is composed of two functions: One for encryption and other for decryption, both of which are used to manage the password. If you want to know more about the library, you can see more details in the help functions.
Next, we have to create a txt file with your encrypted passwords:$ServerName = "Server1"
$UserName = "Login1"
$Password = "Senha1"
$PasswordToEncrypt = "YourPassword"
$UserNameEncrypt = Write-EncryptedString -inputstring $UserName -Password $PasswordToEncrypt
$PasswordEncrypt = Write-EncryptedString -inputstring $Password -Password $PasswordToEncrypt
"$($Servername),$($UserNameEncrypt),$($PasswordEncrypt)" | Out-File c:\temp\ServersSecurePassword.txt -Append
$ServerName = "Server2"
$UserName = "Login2"
$Password = "senha2"
$PasswordToEncrypt = "YourPassword"
$UserNameEncrypt = Write-EncryptedString -inputstring $UserName -Password $PasswordToEncrypt $PasswordEncrypt = Write-EncryptedString -inputstring $Password -Password $PasswordToEncrypt
"$($Servername),$($UserNameEncrypt),$($PasswordEncrypt)" | Out-File c:\temp\ ServersSecurePassword.txt -Append
.And in the c:\temp\ServersSecurePassword.txt file which we've just created, you will find your Username and Password, all neatly encrypted. Let's take a look at what the txt looks like:
.and in case you're wondering, Server names, Usernames and Passwords are all separated by commas.
Decryption is actually much more simple:Read-EncryptedString -InputString $EncryptString -password "YourPassword"
(Just remember that the Password you're trying to decrypt must be exactly the same as the encrypted phrase.)
Finally, just to show you how smooth this solution is, let's say I want to use the Invoke-DBMaint function from SQLPSX to perform a checkdb on a system database: it's just a case of split, decrypt and be happy!Get-Content c:\temp\ServerSecurePassword.txt | foreach {
[array] $Split = ($_).split(",")
Invoke-DBMaint -server $($Split[0]) -UserName (Read-EncryptedString
-InputString $Split[1] -password "YourPassword" ) -Password
(Read-EncryptedString -InputString $Split[2] -password
"YourPassword" ) -Databases "SYSTEM" -Action "CHECK_DB"
-ReportOn c:\Temp
}
This is why I love Powershell.
