By: Burt King | Comments (3) | Related: > Security
Problem
Due to requirements in my environment I need to use SQL Server authentication with my Python scripts instead of Windows trusted authentication. We don’t want a clear text password stored in the Python scripts and we’re not sure how we can secure the passwords.
Solution
Securing 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. Windows file server permissions offer one layer of protection that can prevent wandering eyes from coming across a password, but that may not always be feasible. Since Python offers a number of cryptographic options we’re going to use one popular library that will encrypt our data and make it more difficult to steal the password.
This solution assumes Python 3.x version and users are familiar with the Python language.
The Python library used in this solution is ‘Cryptography’ which uses a symmetric key system that most are familiar with; briefly, 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 tip will walk us through how to import the cryptography module and then use it to encrypt one password and store it in a file. We will then retrieve the encrypted password, use the cryptography module to decrypt the password and then pass it off to SQL Server to be used.
Before we demonstrate the process we need to have a brief discussion about binary files and how to work with them in Python. The cryptography module we’re using will be providing us a binary object to work with. This presents some problems because we cannot write binary to a text file unless we convert it to UTF-8. For this tip we will be taking the binary object and using the native capacity of writing in binary to a binary file rather than a text file. For storing a single password in a file this is more than adequate but for those who will want to store dictionaries, lists, tuples, and so forth, investigating the pickle module is advised but not covered in this tip.
Install Cryptography Library and Create Key
To install the library we use the Python pip installer. For those not familiar with pip you can find more information on pip and the Python package index here: www.Pythonforbeginners.com/basics/how-to-use-pip-and-pypi .
C:\pip install cryptography
Once the library is installed an easy way to validate its ready is to start using it. The first thing we’re going to do is to create a symmetric key which we’ll later use to encrypt and decrypt our password. As with other Python libraries we need to import it into our Python file and then we’ll use it.
from cryptography.fernet import Fernet key = Fernet.generate_key() print(key)
On my workstation this produced the following:
An important thing to note is that the printed results return a byte literal. As we move forward this will present some challenges that need to be accounted for such as figuring a way to write the data to a text file.
Encrypt Password and Test Decrypting
Now that we understand how to create a key we can now use it to encrypt a password and decrypt it. We’ll cover writing it to a text file a bit later. The following chunk of code creates 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)
When I run this on my machine I receive the following output:
The ciphered_text returned from the print statement is what we will later write to a binary file. 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:
Write Encrypted Password to Binary File
Now that we have the encrypted password as a byte literal we can store that object in a file. If you’re a Python master you may recall that 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. The following code uses the previously generated key, creates an encrypted password and then writes that byte literal result 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)
Retrieve Encrypted Password and Decrypt
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. Let’s start by retrieving the encrypted password.
with open('c:\savedfiles\mssqltip_bytes.bin', 'rb') as file_object:
for line in file_object:
encryptedpwd = line
print(encryptedpwd)
Taking this further we 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.
Notes
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.
Next Steps
Review both of these articles to gain a better understanding of the cryptography library:
About the author
Burt King is Senior Database Administrator at enservio with more than 15 years experience with SQL Server and has contributed to MSSQLTips.com since 2011.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
