在Azure Data StudioSQL笔记本中使用Python脚本加密密码

This article explores the Python scripts for encrypting and decrypting passwords in SQL Server using Azure Data Studio SQL Notebooks.

本文探讨了使用Azure Data Studio SQL Notebook在SQL Server中加密和解密密码的Python脚本。

介绍 (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.

密码安全性是任何组织的关键要求。 首先使用复杂的密码，该密码由字母，数字和特殊字符组成。 我们还会在使用绝对数量的错误密码后执行安全策略，例如帐户锁定，密码更改策略。 服务帐户在SQL Server中对于运行服务，链接的服务器或应用程序很有用。 通常，我们为这些帐户使用复杂的密码，但不要经常更改它们。 我们将这些服务帐户和密码存储在受密码保护的Excel或第三方工具（例如Keepass密码安全）中。 我们不希望未经授权的人使用这些帐户和密码，因为它们会在数据库服务中进行不必要的更改。

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.

假设我们将这些密码存储在SQL表中，并限制对该表的访问。 但是，具有较高权限级别的用户可能会轻松访问密码。 这可能是安全漏洞和未经授权的数据访问。 另一种选择是将密码以加密形式存储在SQL表中。 如果任何用户尝试访问加密的表，他将获得一个加密的密码，并且在没有加密密钥的情况下，他无法将其用于数据库连接。

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.

原文： SQLShack是DBA和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.

加密的文本： 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.

您能理解上述指定的加密文本吗？ 无权利！ 这绝对是胡言乱语。 除非解密，否则我们无法理解。

Python语言概述 (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.

Python是一种高级编程语言，我们可以将其用于复杂的数据分析和处理。 我们可以使用Python来加密密码，稍后，我们可以将这些密码存储在SQL表中。

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.

在阅读本文之前，您应该探索Python脚本并熟悉Python语言。 您可以阅读有关Python的文章 。

Prerequisites

先决条件

• We use Azure Data Studio SQL Notebooks in this article for executing Python code
  我们在本文中使用Azure Data Studio SQL Notebooks执行Python代码
• We require Python 3 kernel in SQL Notebook for executing Python queries
  我们需要SQL Notebook中的Python 3内核来执行Python查询

If you are familiar with it, you can explore articles in the Azure Data Studio category, here on SQLShack.

如果您熟悉它，可以在SQLShack上的“ Azure Data Studio”类别中浏览文章。

Python中的密码学 (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.

我们可以在Python中使用加密技术，该技术会生成用于加密和解密数据的对称密钥。 一旦我们使用对称密钥加密了数据，它就会为输入数据生成加密的文本。 以后，我们需要相同的对称密钥来解密加密的文本。 我们必须将此对称密钥保存在安全的地方； 否则，我们将无法检索解密的数据。

Let’s understand a few useful terminologies before proceeding with Cryptography in Python.

在使用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.

在将密码存储到SQL表中的情况下，我们使用加密将密码（纯文本）转换为加密形式（密文）。 后来，根据要求，我们再次使用解密将其转换回纯文本（密码）。

We can understand the encryption and decryption process quickly using the following screenshot:

我们可以使用以下屏幕截图快速了解加密和解密过程：

使用Python脚本安装加密模块 (Installing the cryptography module using Python Scripts)

We need to install a cryptography module in Python using pip command.

我们需要使用pip命令在Python中安装加密模块。

Open SQL Notebook in Azure Data Studio and change the connection to Python 3 kernel:

在Azure Data Studio中打开SQL Notebook并将连接更改为Python 3内核：

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

现在，单击添加代码并粘贴以下命令：

 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.

让我们将此包用于加密或使用对称密钥的文本。

使用Python脚本进行加密和解密的过程 (Process for encryption and decryption using Python Scripts)

生成对称（秘密）密钥的Python脚本 (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模块。 它可以确保没有对称密钥的任何人都无法读取加密的文本。 我们使用fernet模块的generate_key（）函数生成对称密钥。

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.

Fernet是密码术中可访问且最有用的安全原语。 它适用于小文本和文件。 我们不能将其用于GB的更广泛的文本。 它加密或解密内存中的文本。

The following code generates a symmetric key in SQL Notebook and prints it:

以下代码在SQL Notebook中生成对称密钥并进行打印：

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.

它是URL安全的base64编码密钥。 记下该对称密钥并将其存储在安全的位置。

Python脚本使用对称密钥加密密码 (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中生成的对称密钥以及我们要加密的text（password）。 我们使用cipher_suite.encrypt（）函数从字符串生成密文：

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:

在SQL Notebook的输出中，它打印出密文，我们可以将此密码存储在SQL Server表中。 SQL Server无法识别此加密文本，因此，如果任何人也可以访问该表并读取密码，则无法通过SQL Server进行身份验证：

使用Python脚本中的对称密钥解密文本并生成密码 (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:

在以下Python脚本中，我们指定对称密钥（第1步的输出）和密文（第2步的加密密码）。 我们使用加密函数cipher_suite.decrypt（）解密文本并生成原始密码以供使用：

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:

在SQL Notebook的输出中，我们可以看到解密后的密码类似于我们为加密指定的密码。 我们可以看到字节字面量格式的密码：

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.

如前所述，在解密过程中，我们需要与步骤1中生成的用于加密的对称密钥相同的密钥。 如果我们尝试使用其他一些对称密钥，则会显示以下错误消息。

用于从密码和盐生成对称密钥的Python脚本 (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 导入Base 64模块：我们使用base64模块将二进制数据编码为Base64编码格式。 我们还可以使用base64模块将这些编码解码回二进制数据
• Import OS module: We use the OS Python module to use operation system interactive functions. It is a standard Python utility module导入OS模块 ：我们使用OS Python模块来使用操作系统交互功能。 这是一个标准的Python实用程序模块
• Salt: We use salt as a string input for generating hash values in cryptography. It is useful for safeguarding passwordsSalt ：我们将salt用作字符串输入，用于在密码学中生成哈希值。 这对于保护密码很有用
• PBKDF2: Key definition function (PBKDF2) function derives a cryptographic key from the password PBKDF2 ：密钥定义功能（PBKDF2）功能从密码中得出加密密钥
• 
  
  

In the following query, we specified the following values:

在以下查询中，我们指定了以下值：

• Password: SQLShack@Demo 密码： SQLShack @ Demo
• Salt: SQLShack_ 盐： SQLShack_
• 
  
  

Execute the following code in SQL Notebook, and it generates the symmetric key using the specified password and salt with function:

在SQL Notebook中执行以下代码，它使用指定的密码和带有功能的salt生成对称密钥：

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.

我们可以使用此对称密钥，并按照上面指定的步骤2和3对密码进行加密和解密。

结论 (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.

在本文中，我们探讨了Python脚本在SQL Notebook中用于加密和解密文本（例如密码）的有用情况。 您应该根据自己的需求探索Python的用法。 您可以将本文作为加密的参考目的。 我们有许多可用的Python加密机制，我将在以后的文章中尝试探索更多这样的加密机制。

翻译自: https://www.sqlshack.com/encrypting-passwords-with-python-scripts-in-sql-notebooks-of-azure-data-studio/