Application Research of SQL Server 2005 Data encryption technology

Data encryption is the new data security feature of SQL Server 2005, which is an important improvement for application developers. This paper discusses SQL Server 2005 data encryption characteristics and how to use this feature to guarantee the data security of the system from the point of view of program developers.

SQL Server 2005 is the first major product since Microsoft began implementing its Trustworthy Computing program, providing a wealth of security features that provide security for enterprise data. For developers, the most important concern is how to apply these features to protect data security in the database during the programming process. This article explores applications based on the SQL Server 2005 data encryption feature from the application developer perspective.

SQL Server 2005 Data encryption technology

Data stored digitally in the server is not foolproof. Practice has proven that there are too many ways to outsmart SQL Server 2000 authentication protection, most simply by using an SA account that does not have a password. Although SQL Server 2005 is far more secure than its previous version, it is possible for an attacker to obtain stored data. As a result, data encryption becomes a more thorough data protection strategy, and even if an attacker is able to access data, it has to be decrypted, thus adding a layer of protection to the data.

The previous version of SQL Server 2000 did not have built-in data encryption, and to encrypt data in SQL Server 2000, you had to buy a third product, then make COM calls outside the server, or perform encryption in the client's application before the data-sending server. This means that the encrypted key or certificate has to be protected by the cryptographic person, and that the protection key is the hardest thing in data encryption, so even though many applications have data that is strongly encrypted, data protection is still weak.

SQL Server 2005 solves this problem by using data encryption as an intrinsic feature of the database. In addition to providing a multi-level key and rich encryption algorithm, the biggest advantage is that users can choose the data server Management key. The encryption algorithms supported by the SQL Server 2005 server are as follows:

⑴ symmetric encryption (symmetric Key encryption):

Symmetric encryption uses the same key for encryption and decryption. Typically, this type of encryption is difficult to implement in an application because it is difficult to share the key in the same safe way. But this is ideal when data is stored in SQL Server, and you can let the server manage it. SQL Server 2005 provides RC4, RC2, DES, and AES series encryption algorithms.

⑵ asymmetric key encryption (asymmetric key encryption):

Asymmetric key encryption uses a set of public/private key systems that use a single key for encryption and a different key when decrypting. Public keys can be widely shared and disclosed. This encryption is more convenient when you need to encrypt data from outside the server. SQL Server 2005 supports the RSA encryption algorithm and the 512-bit, 1,024-bit, and 2,048-bit key strength.

⑶ Digital Certificate (certificate):

A digital certificate is an asymmetric key encryption, but an organization can associate a set of public and private keys with its owner by using a certificate and digitally signing them. SQL Server 2005 supports the Internet Engineering Workgroup (IETF) X.509 version 3 (x.509v3) specification. An organization can use externally generated certificates for SQL Server 2005, or you can use SQL Server 2005 to generate certificates.

SQL Server 2005 uses a multilevel key to protect its internal keys and data, as shown in the following illustration:

Figure 1 SQL Server 2005 uses a multilevel key to protect its internal keys and data

The key or service that draws an arrow in the figure is used to protect the key that the arrow refers to. Therefore, the Service master key (Services Master key) protects the database master key, while the database master key protects both the certificate (certificates) and the asymmetric key (asymmetric keys). The lowest symmetric key (symmetric keys) is protected by a certificate, asymmetric key, or other symmetric key (the arrow also refers back to itself). Users only need to provide a password to protect this series of keys.

The Service master key at the top of the diagram, when you install a new instance of SQL Server 2005, it is automatically generated and installed, and the user cannot delete the key, but the database administrator can perform basic maintenance on it, such as backing up the key to an encrypted file, updating it when it is compromised, and restoring it.

The Service master key is managed by the DPAPI (Data Protection API). DPAPI is introduced in Windows 2000, built on top of the Windows Crypt32 API. SQL Server 2005 manages the interface with DPAPI. The Service master key itself is symmetric encryption used to encrypt the database master key in the server.

The database master key differs from the service master key, and the database master key must be created by the database administrator before the data in the database is encrypted. Typically, an administrator provides a password when the key is generated, so it encrypts with the password and service master key. If sufficient permissions are available, the user can open the key explicitly or automatically when needed. The following is an example of T-SQL code that produces the database master key:

The following are the referenced contents: Use Encryptiondb CREATE MASTER KEY Encryption by PASSWORD = ' Uty6%djzz8s7ryl '

There is only one database master key per database. You can use the ALTER MASTR KEY statement to remove encryption, change the password, or delete the database master key. This is usually done by the database administrator.

With the database master key, you can proceed to encrypt the data. T-SQL has the encryption support that is placed therein. Create a variety of passwords using the creation statement, and ALTER statements modify them. For example, to create symmetric encryption, you can do this by using a pair of functions EncryptByKey and DecryptByKey.

Application analysis of Data encryption technology

The following examples are used to explore the implementation of SQL Server 2005 data encryption and decryption technology.

Suppose you have a Customer table with the field customer ID, name, city, and various credit card details in the table. Where credit card details need to be encrypted while other data is not needed. Suppose the User1 has a symmetric key and is logged in with the key to run the appropriate code to encrypt the data.

⑴ Data Encryption

① generates a key: a symmetric key is generated using Triple DES as the encryption algorithm in a database containing customers tables. In this case, the key itself is protected by a certificate that already exists in the database, as shown in figure, and the symmetric password is protected by asymmetric passwords and other symmetric keys that exist.

The following are the referenced contents: CREATE symmetric KEY User1symmetrickeycert AUTHORIZATION User1 with algorithm = Triple_des Encryption by Certificate User1certificate

② Open key: The symmetric key must be explicitly opened before it is used, so then open it, retrieve the password, decrypt it, and put it in the protected server memory for use.

The following are the referenced contents: OPEN symmetric KEY User1symmetrickeycert Decryption by Certificate User1certificate

③ encrypted data: In the following code, a row of data is inserted into a table using a normal T-SQL INSERT statement, ID, name, and city are saved in plaintext, credit card types, numbers, and potentially confidential customer annotations are stored in encrypted form, and the data is encrypted using the Triple DES encryption algorithm.

The following are the referenced contents: INSERT into Customer VALUES (4, ' John Doe ', ' Fairbanks ', EncryptByKey (Key_GUID ( ' User1symmetrickeycert '), ' Amex '), EncryptByKey (Key_GUID ( ' User1symmetrickeycert '), ' 1234-5678-9009-8765 '), EncryptByKey (Key_GUID ( ' User1symmetrickeycert '), ' Window Shopper. spends $ at most. ')

After the encryption is complete, close it and release the memory in case it is misused.

Close symmetric KEY User1symmetrickeycert

The above is the whole process of data encryption operation. It has no messy password management and does not need to invoke special algorithms. The fields that store encrypted data are varbinary type data that is long enough to store extended data (encrypted data requires more space, sometimes much more) than plaintext.

⑵ Data Decryption

To decrypt encrypted data, you need to turn on symmetric encryption again. Use the DecryptByKey function to read the data, and then turn off symmetric encryption. The results and the corresponding code are as follows.

The following are the referenced contents: OPEN symmetric KEY User1symmetrickeycert Decryption by Certificate User1certificate SELECT CustID, Name, City, CONVERT (VARCHAR, DecryptByKey (Creditcardtype)) As Cardtype, CONVERT (VARCHAR, DecryptByKey (Creditcardnumber)) As Cardnumber, CONVERT (VARCHAR, DecryptByKey (Notes)) As Notes From Customer WHERE CustID = 4 Close Symmetrickeyuser1symmetrickeycert

This example shows a way to get SQL Server 2005 to manage your keys. In practice, however, users always choose to provide a password by themselves, using the RC4 algorithm to generate a symmetric password. The code is as follows:

The following are the referenced contents: CREATE symmetric KEY User2symmetrickeypwd AUTHORIZATION User2 with algorithm = RC4 Encryption by PASSWORD = ' SDYLVXF&IMEG3FP '

SQL Server 2005 produces a key that encrypts data based on a user-supplied password. Unless explicitly specified, the password is not guaranteed in SQL Server 2005, the user must protect their password, or anyone who knows the password can decrypt the data.

It would be wrong to think that encrypting data stored in a database is a total waste of processor time and storage space. Data encryption in SQL Server 2005 is an extraordinary feature that provides an important layer of protection for customer data. However, when applied, it should be noted that only those sensitive confidential data are protected because the encryption consumes a large amount of resources from the server processor, and running a select without a WHERE clause can cause server performance to crash if you encrypt each field in a table with 10 million records.