This article explores the Python scripts for encrypting and decrypting passwords in SQL Server using Azure Data Studio SQL Notebooks.
Introduction
Password security is a critical requirement for any organization. It starts with using a complex password with combinations of alphabets, digits, special characters. We also enforce security policies such as account lockout after using an absolute number of incorrect passwords, password change policy. The service account is useful in SQL Server for running services, linked servers or applications. Usually, we use a complex password for these accounts but do not change them frequently. We stored these service account with passwords in password-protected Excel or third-party tools such as Keepass password safe. We do not want unauthorized persons to use these accounts and passwords as they can make undesired changes in the database services.
Suppose we store these passwords in a SQL table and restricted access to that table. However, there is a chance that users with a higher level of permissions can access passwords easily. It might be a security breach and unauthorized data access. Another alternative could be storing passwords in the SQL table in an encrypted form. If any user tries to access the encrypted table, he will get an encrypted password, and he cannot use it for database connections without the encryption key.
Let’s understand the encryption and decryption using the following example.
Original text: SQLShack is a popular site among DBA and developers for Microsoft SQL Server.
You can easily read the above text and understand it. Now read the below sentence.
Encrypted text: hjlpedss peqat mozaedreerp cokedzwlcni mkddah ytirucesrfed di wedcuhla lpopps qzmposE.
Are you able to understand the above-specified encrypted text? No, right! It is absolute gibberish. We cannot understand it unless decrypted.
Overview of Python language
Python is a high-level programming language that we can use for complex data analytics, manipulations. We can use Python for encrypting passwords and later, we can store these passwords in SQL tables.
You should explore Python scripts and be familiar with the Python language before going through this article. You can go through Python articles for it.
Prerequisites
- We use Azure Data Studio SQL Notebooks in this article for executing Python code
- We require Python 3 kernel in SQL Notebook for executing Python queries
If you are familiar with it, you can explore articles in the Azure Data Studio category, here on SQLShack.
Cryptography in Python
We can use cryptography in Python that generates a symmetric key for encrypting and decrypting data. Once we encrypted data using a symmetric key, it generates encrypted text for input data. Later, we require the same symmetric key for decrypting the encrypted text. We must preserve this symmetric key in a safe and secure location; otherwise, we cannot retrieve the decrypted data.
Let’s understand a few useful terminologies before proceeding with Cryptography in Python.
Plain text
It is a standard text that is readable and can be understood by the user. In other words, it is the text without encryption.
Ciphertext
It is the output text after applying cryptography on the plain text. It is difficult to understand and remember the ciphertext.
Encryption
We convert plain text into ciphertext using the encryption process.
Decryption
It is the reverse of the encryption process. In this, we convert ciphertext into the plain text.
In the context of storing passwords into SQL tables, we use encryption to convert passwords (plain text) into an encrypted form (ciphertext). Later, as per requirement, we again convert it back to plain text (password) using decryption.
We can understand the encryption and decryption process quickly using the following screenshot:
Installing the cryptography module using Python Scripts
We need to install a cryptography module in Python using pip command.
Open SQL Notebook in Azure Data Studio and change the connection to Python 3 kernel:
Now, click on add code and paste the following command:
|
1 |
pip install cryptography |
It installs the packages. I have already installed the cryptography package. Therefore, it says – Requirement already satisfied:
Let’s use this package for encryption or the text using the symmetric key.
Process for encryption and decryption using Python Scripts
Python scripts to generate a symmetric (secret) key
We use the fernet module in the cryptography package. It guarantees that no one can read the encrypted text without the symmetric key. We generate a symmetric key using the generate_key() function of the fernet module.
Fernet is an accessible and most useful secured primitive in the cryptography. It is suitable for small texts and files. We cannot use it for more extensive texts in GB’s. It encrypts or decrypts the texts in the memory.
The following code generates a symmetric key in SQL Notebook and prints it:
|
1 2 3 |
from cryptography.fernet import Fernet key = Fernet.generate_key() print(key) |
It is a URL safe base64 encoded key. Note down this symmetric key and store it in a safe location.
Python scripts to encrypt the password using a symmetric key
In the step, we will use the symmetric key generated in step 1 along with text(password) that we want to encrypt. We use the cipher_suite.encrypt() function for generating a ciphered text from the string:
|
1 2 3 4 5 |
from cryptography.fernet import Fernet key = b'PCHl_MjGyEyBxLYha3S-cWg_SDDmjT4YYaKYh4Z7Yug=' cipher_suite = Fernet(key) ciphered_text = cipher_suite.encrypt(b"SQLShack@DemoPass") print(ciphered_text) |
In the output of the SQL Notebook, it prints the ciphered text, and we can store this password in SQL Server tables. SQL Server does not recognize this encrypted text, so if anyone gets access to the table also and read passwords, he cannot be authenticated by SQL Server:
Decrypt text and generate a password using the symmetric key in Python scripts
Suppose we want to decrypt the ciphered text and generate the password from it. We still need access to the same symmetric key for decryption as well.
In the following Python script, we specify the symmetric key (step 1 output) and ciphered text (encrypted password from step 2). We use cryptographic function cipher_suite.decrypt() for decrypting text and generated the original password for use:
|
1 2 3 4 5 6 |
from cryptography.fernet import Fernet key = b'PCHl_MjGyEyBxLYha3S-cWg_SDDmjT4YYaKYh4Z7Yug=' cipher_suite = Fernet(key) ciphered_text = b'gAAAAABd_jcLWEz-fIBt3y2P3IoB3jGdbNnSzeSINZ8BomP9DrKIX2YF4pMLkMCvCxLshmKgKXk7np42xop6QIaiawbhjGayMU0UrbTeUX-6XA8zmo55vwA=' unciphered_text = (cipher_suite.decrypt(ciphered_text)) print(unciphered_text) |
In the output of the SQL Notebook, we can see that the decrypted password is similar to the password we specified for encryption. We can see the password in byte literal format:
As specified earlier, we need the same symmetric key during decryption that we generated in step 1 for encryption. If we try to use some other symmetric keys, it gives the following error message.
Python scripts for generating a symmetric key from a password and salt
In the previous section, we generated the symmetric key randomly. We did not specify any inputs for generating it. Suppose we want to use a string for the base of this key. It requires the following steps:
- Import Base 64 module: We use the base64 module for encoding binary data to the Base64 encoded format. We can also decode these encodings back to binary data with the base64 module
- Import OS module: We use the OS Python module to use operation system interactive functions. It is a standard Python utility module
- Salt: We use salt as a string input for generating hash values in cryptography. It is useful for safeguarding passwords
- PBKDF2: Key definition function (PBKDF2) function derives a cryptographic key from the password
In the following query, we specified the following values:
- Password: SQLShack@Demo
- Salt: SQLShack_
Execute the following code in SQL Notebook, and it generates the symmetric key using the specified password and salt with function:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
import base64 import os from cryptography.hazmat.backends import default_backend from cryptography.hazmat.primitives import hashes from cryptography.hazmat.primitives.kdf.pbkdf2 import PBKDF2HMAC password_provided = "SQLShack@Demo" password = password_provided.encode() salt = b'SQLShack_' kdf = PBKDF2HMAC( algorithm=hashes.SHA256(), length=32, salt=salt, iterations=200000, backend=default_backend() ) key = base64.urlsafe_b64encode(kdf.derive(password)) print(key) |
We can use this symmetric key and follow steps 2 and 3 specified above for encrypting and decrypting the passwords.
Conclusion
In this article, we explored the useful case of Python scripts for encrypting and decrypting texts (such as passwords) in SQL Notebook. You should explore the use of Python according to your requirements. You can take this article as a reference purpose for encryption. We have many encryption mechanisms available in Python and I will try to explore more such encryption mechanisms for future articles.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023
- Learn the PostgreSQL COALESCE command - January 19, 2023