mssqltips logo

Encrypting passwords for use with Python and SQL Server

By:   |   Updated: 2018-01-15   |   Comments (3)   |   Related: More > 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:

cryptography fernet

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:

cryptography fernet

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:

cryptography fernet

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)
cryptography fernet

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)
cryptography fernet

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:



Last Updated: 2018-01-15


get scripts

next tip button



About the author
MSSQLTips author Burt King 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.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Tuesday, December 18, 2018 - 9:59:42 AM - Germán Back To Top

Hi, what if I want to encrypt a string that contains "ñ" character?


Friday, May 18, 2018 - 10:18:00 PM - Burt Back To Top

 There's always going to be someone with the skills and expertise to work through your security -- the technique provided in this article was a simple one -- use the crypto module to add a layer of security.  Securing the file system is another layer of security, or, if you have the luxury of using domain authentication you could switch to windows authentication and let that handle your security.  I don't think switching to a compiled language like C# is a perfect answer either -- your source code lives somewhere.  As you said, there's always someone, somewhere who is going to be able to work around your security measures.  I didn't intend this mssql tip to be an end all solution.  Like all security, it's just one more tool in your toolbox - but there's always risk.

 


Friday, May 18, 2018 - 8:06:07 AM - Ryan Harris Back To Top

 This is a good article for ensuring a first-level security of a user-selected password, but do you know how to take that a step further? Like you said, any notice python dev with the encryption key and bin file will be able to decrypt this password. I'm not concerned with someone hacking another person's device as much as I'm concerned with some savy users opening up my python code, finding my sql queries along with the db, username, and password, and performing their own SELECT * queries on my db tables to see other user data. How can I prevent this from occurring in a Python file? Am I supposed to be using a webapp to launch these types of queries instead of coding them into the python script?

Thank you



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools