Rajendra Gupta

Encrypting passwords with Python Scripts in SQL Notebooks of Azure Data Studio

January 6, 2020 by

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:

useful terminologies

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:

SQL Notebook in Azure Data Studio

Now, click on add code and paste the following command:

It installs the packages. I have already installed the cryptography package. Therefore, it says – Requirement already satisfied:

Install cryptography package in Azure Data Studio SQL Notebooks

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:

Python scripts to generate a symmetric (secret)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:

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:

encrypt the password

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:

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:

Decrypt text

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.

Error message due to invalid key

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:

symmetric key from a password

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.

Rajendra Gupta
2,255 Views