First off, let me state for the record that I know that this probably has its security flaws. For one, any time you have reversible encryption your data is only secure as the key that was used to encrypt the data. Secondly, I’m not a security guy by trade - I do my best to understand and secure where I can, but security is an artform I have just not mastered. However, I have seen (and myself commited) many acts of bad security in the name of getting a script to work with credentials of a service account on any machine. Is it bad practice? Probably. Is it sometimes a necessity of our jobs? Probably. (although our security teams would probably all collectively create a brick of excrement or two if they knew some of the things we do from day to day to make management possible.) I am ALWAYS open to suggestions (and the code is in GitHub so you can generate pull requests as well if you find any glaring issues) - if you have thoughts please leave them in the comments below.

With that out of the way, here is where the idea came from:

My goals were as follows:

1. Create a PowerShell Module (so that it can be easily consumed by your script).
2. Have the module generate a self-signed certificate if preferred.
3. Have the module encrypt both username and password to store in the database using a certificate.
4. Have the module decrypt password to a Secure String, or if absolutely necessary, cleartext via switch.
5. Have the access to the credentials managed by SQL.

For the most part this hasn’t been too challenging of a project. Microsoft makes available nearly everything that I need to do via simple commands. The most time consuming part of it was just writing it and more importantly commenting out my code like a good scripter/developer. The table that is consumed by this PowerShell module is really simple - four columns:

1. Credential Purpose - a unique key to identify the credential. Like “Local Administrator on SQL01” or something like that. You could even choose to obfuscate the purpose - but we use this column to lookup the credentials later.
2. Encrypted Username - exactly what it sounds like. The output of the encryption process stored in the database as an exceptionally long string.
3. Encrypted Password - again, exactly what it sounds like. Same as the Encrypted Username column except this time it stores the password.
4. Certificate Thumbprint - used to identify which certificate the module should use to decrypt the data. Frankly, I went back and forth on storing this data in the database, but ultimately came to the conclusion that it improved the functionality of the module. In a future release maybe I’ll make it possible to not store the thumbprint and allow you to define a thumbprint in your script (although that kind of defeats the purpose of hiding it right?)

The Juicy Stuff

Look - I get it… long winded posts don’t make anyone happy. If you’re interested in just getting to the module and the associated SQL script, just go here: https://github.com/theznerd/PowerShellCredentialManager

If you need some further instruction and what I would define as “best practices” you should probably read on.

Setting up the database

I’ve included in GitHub a T-SQL script to create the database. By default I named it PSCM but honestly you can call it whatever you want - the script has a parameter in it to allow you to choose the name. The only important part is that the table name remains the same “dbo.SecureCredentials”

If you’re wanting to rename the table - you can do that too. Just make sure you update the appropriate lines in the PowerShell script which reference the table.

After creating the database you’ll want to make sure that you setup your access to the database properly. I recommend creating two security groups:

1. One group that has access to read from the database. In here you’ll add all the devices or users who will be running scripts calling the module to decrypt the passwords/usernames. If the script runs as SYSTEM, you’ll need to put the computer object in this security group. If the script runs as a user, you’ll need to put the user in this security group.
2. One gorup that has access to write to the database. This will be used for any users who will be adding or removing credentials from the database. Unfortunately I have not at this point implemented a “user level” type access to the credentials, so anyone in this group will be able to read the rows and update the rows (including removing rows).

Creating your certificate

Any certificate on a machine with an exportable private key will do. Or it doesn’t have to be exportable if you are only going to use the credentials for that user on that machine (or just on that machine if the certificate is in the local machine certificate store).

The script has a function built in to create a self-signed certificate. It’s as simple as loading the module and then calling New-PSCredCertificate. The script is populated with the proper Get-Help info, but for posterity sake the following parameters are available:

1. Subject - the subject name of the certificate.
2. FriendlyName - the friendly name of the certificate.
3. ValidityPeriod - the number of years the certificate is valid.

By default it creates an SHA256 cert with a key length of 2048. In a future revision I may add extensibility here as well but I believe this is enough for the time being and offers a significant amount of compatibility between systems.

Also of note - it creates it in the user’s personal certificate store. You’ll need to export the certificate with the private key to be installed on any machine/user who wishes to use it to decrypt the password. You can also just export the certificate without the private key if you wish to pass that around to encrypt and store passwords in the database without the ability to decrypt them.

Setitng up your SQL connection

You can either specify your SQL server and database when you call commands requiring them, or instead you can just call New-PSCredSQLConnection and set them for the duration of the script. The command is straight forward; give it a SQL server and a SQL database. The database is not required if you used the default PSCM database that is created by the SQL script. Run the New-PSCredSQLConnection before you call the New, Delete, or Get functions for the credentials.

Creating a credential object in the database

This is super simple. Run New-PSCredCredential and give it a purpose, password, username, and the thumbprint of the certificate you wish to use to encrypt the data. It will then handle encrypting the data and then inserting that information into the database. The most important part of this step is that the credential purpose must be unique in the database. This is the key we’ll later use to retrieve the record.

Getting a credential object from the database

This again, is super simple. Run Get-PSCredCredential and give it the credenital purpose. The command will then attempt to find the row in the database matching that credential purpose, find the certificate associated with it, decrypt the data, and then return an object with the decrypted username and a secure string of the password. You can also pass an “-Insecure” switch to the command to have the password returned as cleartext, but I wouldn’t recommend it unless you have a specific need for a cleartext version of the password.

Removing a credential object from the database

Simple as running Remove-PSCredCredential and giving it the credential purpose. The command will attempt to find the record in the database and then remove it.

Updating a credential object in the database

At this point I have not implemented a specific command to do this due to the possibility the public key may not be available for you to re-encrypt the password and username again for the row. So I would recommend that you just delete the existing record from the database and then create a new one. Or you could just create a new row provided that the credential purpose property changes. It’s really up to you.

“Best” Practices

I’m not going to claim this is an exhaustive list. But, here are the things that have come to mind as I have given it some thought:

1. Keep the certificate with an exportable key SEPARATE from wherever you are consuming the passwords. Really, you could just export the certificate to a PFX file and then delete the certificate from that device/user. Secure this PFX file with a nice secure password or user based security.
2. If at all possible, only install the certificate for the user who will be consuming the credentials. If the script is running as SYSTEM, unfortunately you will have no other choice but to install it in the local computer personal store, but if you can avoid this it’s best. The reason for this should be obvious - if you install it for the computer, any user who has access to the computer will be able to decrypt with that certificate.
3. Encrypt your SQL database to protect data at rest. There are plenty of guides on how to do this online.
4. Make sure you give least privilege necessary to the database. Since the database cannot be as granular as specific levels of access (yet), only give the necessary users access to write/read the database. In a future revision I may include a “userlevel” option or something similar to allow for specific credentials to be accessible to only certain users. If you have a need to do something like this now - you could always just create a separate database of a different name as there is no technical limitation to doing this.

Final thoughts

Please let me know if you find this module of any use. It has the MIT license so also please feel free to adapt it as you see fit. If you have any questions about how to use the module don’t hesitate to ask in the comments below. Or if you have suggestions on improving the security of the script I am all ears. For now please enjoy it - if you didn’t catch the link earlier, the module and the associated T-SQL script are available here.

And as always and forever… Happy Admining!