Powershell – Storing credentials in CSV file as key-value pair, encrypting and decrypting

Posted: January 10, 2020 in Scripts

Usernames and passwords are stored in CSV file:

1.csv:

link,user,password
http://1.1.1.1,user1,password1
http://2.2.2.2,user2,password2

We can extract link,user and password using this Powershell script:

# Constants.
$DELIM = ','
$CSV_F = 'C:\Users\user\Desktop\1.csv' 

# Parse keys
$keys = (gc "${CSV_F}" -TotalCount 1).Split($DELIM)
$csv = Import-CSV "${CSV_F}"
$data = @()

# Iterate through CSV to build array of hashtables.
ForEach ($r in $csv) {
    $tmp_h = @{}

    # Create hash of key-value pairs.
    ForEach($k in $keys) {
        $tmp_h[$k] = $r.($k)
    }

    # Add hash to array of hashes.
    $data += $tmp_h
}
# Display data
foreach($i in $data){
$link = $i.link
$user = $i.user
$password = $i.password
}

Encrypting CSV file

Because this file contains sensitive data, it would be reasonable to encrypt it.We’ll use Protect-CmsMessage cmd-let to achieve that.

Create a certificate for encrypting content

In Powershell console paste certificate request (change subject name and.or inf file name):

# Create .INF file for certreq

{[Version]
Signature = "$Windows NT$"

[Strings]
szOID_ENHANCED_KEY_USAGE = "2.5.29.37"
szOID_DOCUMENT_ENCRYPTION = "1.3.6.1.4.1.311.80.1"

[NewRequest]
Subject = "cn=youralias@emailaddress.com"
MachineKeySet = false
KeyLength = 2048
KeySpec = AT_KEYEXCHANGE
HashAlgorithm = Sha1
Exportable = true
RequestType = Cert
KeyUsage = "CERT_KEY_ENCIPHERMENT_KEY_USAGE | CERT_DATA_ENCIPHERMENT_KEY_USAGE"
ValidityPeriod = "Years"
ValidityPeriodUnits = "1000"

[Extensions]
%szOID_ENHANCED_KEY_USAGE% = "{text}%szOID_DOCUMENT_ENCRYPTION%"
} | Out-File -FilePath DocumentEncryption.inf

After you have created your certificate file, run the following command to add the certificate file to the certificate store.

Only user(s) who have access to this certificate can encrypt/decrypt files.

certreq -new DocumentEncryption.inf DocumentEncryption.cer 

Encrypt CSV file using certificate subject or OID number:

Protect-CmsMessage -To "*youralias@emailaddress.com*" -Path C:\Users\user\Desktop\1.csv -OutFile C:\Users\user\Desktop\1.csv.cms

Following script will decrypt CSV “on the fly”, and store it’s content into $CSV_F variable, avoiding storing un-encrypted CSV file.

$CSV_F = Unprotect-CmsMessage -To "*youralias@emailaddress.com*" -Path .\1.csv.cms 

$Data = $CSV_F | ConvertFrom-Csv
foreach($i in $Data){

$link = $i.link

$user = $i.user

$password = $i.password

}

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