[Encrypting passwords for use with Python and SQL Server] #Python #encryption #encyptionPassword #password

0 - Problem.md

• Background : SQL Server Authentication + my Python scripts
• Problem : don't want a clear text password stored in the Python scripts
• Requirement : secure the passwords

1 - Solution.md

ecuring passwords is always an issue when using SQL Server authentication or any application that may store the password in clear text in a configuration file.

Python offers a number of cryptographic options (libraries) that encrypt our data and make it difficult to steal the password.

use a symmetric key system :

a key is required to encrypt and decrypt data

Once data has been encrypted the key which was used to encrypt the data is required to decrypt it.

The key must be preserved or the ability to work with the encrypted data is lost forever.

This tutorial introduced:

1. how to import the cryptography module?
2. use this module to encrypt one password
3. store this encrypted password in a file
4. then retrieve the encrypted password
5. use the cryptographic module to decrypt the password
6. then pass it off to SQL Server to be used

2 - Install Cryptography Library and Create Key.md

1. install cryptographic module www.Pythonforbeginners.com/basics/how-to-use-pip-and-pypi

C:\pip install cryptography

2. an easy way to validate its ready is to start using it
   • to create a symmetric key (which to use encrypt and decrypt our password)

from cryptography.fernet import Fernet
key = Fernet.generate_key()
print(key)

this printed results return a byte literal

3 - Encrypt Password and Test Decrypting.md

1. Create an encrypted password create an encrypted password by using the previously generated key:

from cryptography.fernet import Fernet
key = b'pRmgMa8T0INjEAfksaq2aafzoZXEuwKI7wDe4c1F8AY='
cipher_suite = Fernet(key)
ciphered_text = cipher_suite.encrypt(b"SuperSecretPassword")   #required to be bytes
print(ciphered_text) 

The ciphered_text returned from the print statement is what we will later write to a binary file.

2. validate this encrypted password Before doing that, let's validate that we can return the password previously encrypted with the following statement:

from cryptography.fernet import Fernet
key = b'pRmgMa8T0INjEAfksaq2aafzoZXEuwKI7wDe4c1F8AY='
cipher_suite = Fernet(key)
ciphered_text = b'gAAAAABaHvk3g8IG4cln7g5HCulppy1bAPVuhtskVcgPXRyytx6RkIqjcI0mAMA7Oy_56T6J0dk-yjxI_WlZtjxnUBbR-EvoQa_oqCKoQJFbv_uc2WdXMSI='
unciphered_text = (cipher_suite.decrypt(ciphered_text))
print(unciphered_text)

The result, returned as a byte literal is our password:

4 - Write Encrypted Password to Binary File.md

have the encrypted password as a byte literal we can store that object in a file

writing to a text file writes the text, but only if it’s actually text.

While the object returned above may look like text it is not.

It’s a byte literal and will either need to be converted to a string (UTF-8) to be written to a text file or, as we will demonstrate, write the byte literal to a binary file.

from cryptography.fernet import Fernet
key = b'pRmgMa8T0INjEAfksaq2aafzoZXEuwKI7wDe4c1F8AY='
cipher_suite = Fernet(key)
ciphered_text = cipher_suite.encrypt(b'SuperSecretpassword')
with open('c:\savedfiles\mssqltip_bytes.bin', 'wb') as file_object:  file_object.write(ciphered_text)

5 - Retrieve Encrypted Password and Decrypt.md

With the data now written to a binary file we’re going to retrieve the encrypted password and use the previously generated key to decrypt it.

Essentially, working in reverse from where we started.

retrieve the encrypted password

with open('c:\savedfiles\mssqltip_bytes.bin', 'rb') as file_object:
    for line in file_object:
        encryptedpwd = line
print(encryptedpwd)

take the encrypted password, use the cryptography library to decrypt it and finally convert it back to a string

from cryptography.fernet import Fernet
key = b'pRmgMa8T0INjEAfksaq2aafzoZXEuwKI7wDe4c1F8AY='
cipher_suite = Fernet(key)
with open('c:\savedfiles\mssqltip_bytes.bin', 'rb') as file_object:
    for line in file_object:
        encryptedpwd = line
uncipher_text = (cipher_suite.decrypt(encryptedpwd))
plain_text_encryptedpassword = bytes(uncipher_text).decode("utf-8") #convert to string
print(plain_text_encryptedpassword)

At this point the situation is pretty straight forward – pass the password on to your favorite Python driver and connect to SQL Server.

6 - Note.md

While the process is relatively straightforward once it is in front of us there are a lot of caveats along the way. The most important being that the key you created in the very beginning must be stored forever or you will lose the ability to decrypt any passwords that you encrypt.

Additionally, the process does encrypt the password so it is possible to hide the password from most users, but a fairly novice Python developer would be able to retrieve it with only a few lines of code if he can gain access to the key.

We also saw that the cryptography library works with byte literals – converting back and forth to strings is part of the work you will need to perform routinely with the cryptographic library. If you decide to store more than one password in a Python dictionary, for example, you will need to investigate Python pickle or convert back and forth to utf-8 as you go.

what does it mean "At this point the situation is pretty straight forward – pass the password on to your favorite Python driver and connect to SQL Server."? I do no thave "...favorite Python driver... " How actually I can pass an encrypted password to the MSSQL server?