Encrypting your SQL Server passwords in Powershell


 

A couple months ago, a friend who is now bewitched by supernatural powers of powershell (plus one for the team) asked me a question:

"Laerte, I do not have the luxury of being able to work with my SQL servers in Windows Authentication, I need to pass username and password"

I replied, “simply pass Username and Password in SQLPSX functions” (he uses the SQLPSX too)
He answered:

“My friend, imagine passing
Username-"Me"-password "NowEverybodyKnowsMyPassword"”

or My txt file :

Server1,UserName,NowEverybodyKnowsMyPasswordFromServer1
Server1,UserName,NowEverybodyKnowsMyPasswordFromServer2

 

Well as I have the pleasure of working with Windows Authentication, had not thought about yet (and thank goodness it was a friend, and the shame was not so big).

Talking with Chad Miller, he showed me a code that shows in Powershell ETL session at Tampa SQL Saturday , saving passwords on SQL Server Tables and you can downloaded the scripts in here .

The solution seemed to be ready. I showed this to a friend and he told me:
That is what I want, but needs to be in my txt file with servers that I use on the powershell scripts. Something like: (servers.txt)

Server1,UserName,Password
Server2,UserName,Password

I thought a few milliseconds (hahaha sure I will not put real time, have to do my marketing) and the solution was ready.

First , we have do downloaded Library-StringCripto (many thanks to Steve) and you can download these functions in here.
This library is composed of two functions. One for  encrypt and other to decrypt and the two procedures have to pass a password phrase. You can see more in the help functions.

Now, we have to create a txt file with encrypt passwords, and the code is :

   1: $ServerName = "Server1"
   2:  $UserName = "Login1"
   3:  $Password = "Senha1"
   4:  $PasswordToEncrypt = "YourPassword"
   5:  $UserNameEncrypt = Write-EncryptedString -inputstring $UserName -Password $PasswordToEncrypt 
   6:  $PasswordEncrypt = Write-EncryptedString -inputstring $Password -Password $PasswordToEncrypt 
   7:   "$($Servername),$($UserNameEncrypt),$($PasswordEncrypt)" | Out-File c:\temp\ServersSecurePassword.txt -Append
   8:  
   9:  $ServerName = "Server2"
  10:  $UserName = "Login2"
  11:  $Password = "senha2"
  12:  $PasswordToEncrypt = "YourPassword"
  13:  $UserNameEncrypt = Write-EncryptedString -inputstring $UserName -Password $PasswordToEncrypt 
  14:  $PasswordEncrypt = Write-EncryptedString -inputstring $Password -Password $PasswordToEncrypt 
  15:  "$($Servername),$($UserNameEncrypt),$($PasswordEncrypt)" | Out-File c:\temp\ ServersSecurePassword.txt -Append

And in the c:\temp\ServersSecurePassword.txt will be your Username and Password encrypted. Let’s take a look how the txt looks like ?

clip_image002

With ServerName, Username and Password separated by comma.
To Drecypt is more simple , using :
   1: Read-EncryptedString -InputString $EncryptString -password "YourPassword"

Just remember, the Password Phrase to decrypt must be the same to encrypt

Let´s say I want to use Invoke-DBMaint function from SQLPSX to perform a checkdb in system databases its only split, decrypt and be happy 🙂

   1: Get-Content c:\temp\ServerSecurePassword.txt | foreach { 
   2:      [array] $Split = ($_).split(",")
   3:     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
   4: }

This is why I do love Powershell.

Powershell Rocks !!!!

“The last fire will rise
Behind those eyes
Black house will rock
Blind boys dont lie
Immortal fear
That voice so clear
Through broken walls
That scream I hear

Cry little sister (thou shall not fall)
Come come to your brother (thou shall not die)
Unchain me sister (thou shall not fear)
Love is with your brother (thou shall not kill)”

Cry Little Sister

The Sisters Of Mercy

About Laerte Junior

Laerte Junior Laerte Junior is a SQL Server specialist and an active member of WW SQL Server and the Windows PowerShell community. He also is a huge Star Wars fan (yes, he has the Darth Vader´s Helmet with the voice changer). He has a passion for DC comics and living the simple life. "May The Force be with all of us"
This entry was posted in Algo que Esqueci de Categorizar. Bookmark the permalink.

2 Responses to Encrypting your SQL Server passwords in Powershell

  1. Tome says:

    The only problem with this method is that if a person knows to look in the script for the password they probably will be able to figure out how to run the decrypt method on their own so thy can use it at will. I admit it is better than leaving it in clear text, but it is just as insecure.

  2. Laerte says:

    I agree with you. But this is the problem of any algorithm. You just see the code and ok is undone. This is a little safer, because it has the password phrase. It is up to you to manage the security of the creation of your passwords phrase and safe the code.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s