SQL Server 2005 is the first major product since Microsoft began to implement its "trusted computing" plan, which provides rich security functions and security for enterprise data. For developers, what they are most concerned about is how to apply these features in the programming process to protect the data security in the database. This paper will discuss the application of data encryption based on SQL Server 2005 from the perspective of application developers.
Data encryption technology of SQL Server 2005
Storing data digitally in the server is not foolproof. Practice has proved that there are too many ways to circumvent the authentication protection of SQL Server 2000, and the simplest way is to use sa account without password. Although SQL Server 2005 is much more secure than the previous version, it is still possible for an attacker to obtain the stored data. Therefore, data encryption has become a more thorough data protection strategy. Even if an attacker can access the data, he will decrypt it, thus adding a layer of protection to the data.
Before SQL Server 2000, there was no built-in data encryption function. If you want to encrypt data in SQL Server 2000, you must purchase a third product, and then make a COM call outside the server or perform encryption in the client application before sending the data to the server. This means that the encrypted key or certificate should be protected by the encryptor itself, and protecting the key is the most difficult thing in data encryption, so even though the data has been strongly encrypted in many applications, the data protection is still weak.
SQL Server 2005 solves this problem by making data encryption an inherent function of the database. Besides providing multilevel keys and rich encryption algorithms, its biggest advantage is that users can choose data servers to manage keys. The encryption algorithms supported by SQL Server 2005 server are as follows:
(1) symmetric key encryption:
Symmetric encryption uses the same key for encryption and decryption. Usually, this encryption method is difficult to implement in applications, because it is difficult to share keys in the same secure way. But when the data is stored in SQL Server, this method is ideal and can be managed by the server. SQL Server 2005 provides RC4, RC2, des and AES series encryption algorithms.
(2) Asymmetric key encryption:
Asymmetric key encryption uses a set of public key * * */private key system, one key is used for encryption and the other key is used for decryption. Public keys can be widely shared and made public. This encryption method is more convenient when data needs to be transmitted to the outside of the server through encryption. SQL Server 2005 supports RSA encryption algorithm and key strength of 5 12 bits, 1 0,024 bits and 2,048 bits.
(3) digital Certificate:
Digital certificate is an asymmetric key encryption, but organizations can use certificates and associate a set of public and private keys with their owners through digital signatures. SQL Server 2005 supports the Internet Engineering Working Group (IETF) X.509 version 3 (X.509v3) specification. Organizations can use externally generated SQL Server 2005 certificates, or they can use SQL Server 2005 to generate certificates.
SQL Server 2005 uses multilevel keys to protect its internal keys and data, as shown in the following figure:
Figure 1 SQL Server 2005 uses multilevel keys to protect its internal keys and data.
Keys or services with arrows in the figure are used to protect keys indicated by arrows. Therefore, the service master key protects the database master key, and the database master key protects certificates and asymmetric keys. The underlying symmetric key is protected by a certificate, asymmetric key or other symmetric key (the arrow points to itself). Users only need to provide passwords to protect this series of keys.
When a new instance of SQL Server 2005 is installed, the service master key at the top level in the diagram is automatically generated and installed. Users can't delete this key, but database administrators can perform basic maintenance on it, such as backing up this key to an encrypted file, and updating and restoring it when security is compromised.
The service master key is managed by DPAPI (data protection API). DPAPI was introduced in Windows 2000, based on Windows Crypt32 API. SQL Server 2005 uses DPAPI management interface. The service master key itself is symmetric encryption, which is used to encrypt the database master key in the server.
The database master key is different from the service master key. Before encrypting the data in the database, the database administrator must create the database master key. Usually, the administrator will provide a password when generating the key, so the key will be encrypted with the password and the service master key. If you have sufficient rights, users can open the key explicitly or automatically when needed. The following is an example of the T-SQL code that generates the database master key:
The following is a quote:
Using an encrypted database
Create master key
Encrypted by password = 'UTY6%djzZ8S7RyL'
There is only one database master key for each database. You can use the ALTER MASTR KEY statement to delete encryption, change passwords, or delete database master keys. Usually this is the responsibility of the database administrator.
Using the database master key, you can start encrypting data. T-SQL has built-in encryption support. Use CREATE statement to create various passwords, and use ALTER statement to modify passwords. For example, to create symmetric encryption, it can be realized by a pair of functions EncryptByKey and DecryptByKey.
Analysis on the Application of Data Encryption Technology
This paper discusses the implementation of data encryption and decryption technology in SQL Server 2005 through examples.
Suppose you have a customer table with fields such as customer ID, name, city and various credit card details. Among them, credit card details need to be encrypted, and other data do not. Assume that User 1 has a symmetric key, log in with this key, and run the corresponding code to encrypt data.
(1) data encryption
① generating key: using Triple DES as encryption algorithm to generate symmetric key in the database containing Customers table. In this example, the key itself is protected by a certificate that already exists in the database. As shown in figure 1, symmetric passwords are protected by asymmetric passwords and other existing symmetric keys.
The following is a quote:
Create symmetric key user 1 symmetric key center
Authorized user 1
WITH algorithm = TRIPLE_DES
Certificate user encryption 1 certificate
② Open key: The symmetric key must be opened explicitly before use, so open it, retrieve the password, decrypt it and put it in the protected server memory for use.
The following is a quote:
Open symmetric key user 1 SYMMETRIC KEY center
Certificate user decryption 1 certificate
③ Encrypted data: In the following code, a row of data is inserted into the table by using an ordinary T-SQL INSERT statement, the id, name and city are saved in plain text, the credit card type, number and potentially confidential customer comments are stored in an encrypted way, and the data is encrypted by using the Triple DES encryption algorithm.
The following is a quote:
Insert customer
Values (4,' Anonymous',' fairbanks',
EncryptByKey(Key_GUID(
user 1 symmetric ckeycert’)，‘Amex’)，
EncryptByKey(Key_GUID(
user 1 symmetric ckeycert’)，
' 1234-5678-9009-8765'),
EncryptByKey(Key_GUID(
user 1 symmetric ckeycert’)，
Window shopper. It costs five dollars at most. ))
After the encryption is completed, close it and free the memory to prevent it from being misused.
Close symmetric key user 1 symmetric key center.
The above is the whole operation flow of data encryption. It has no chaotic password management and does not need to call special algorithms. The field for storing encrypted data is varbinary data, which is long enough to store extended data (encrypted data needs more space than plain text, sometimes even more).
⑵ 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 result and corresponding code are as follows.
The following is a quote:
Open symmetric key user 1 SYMMETRIC KEY center
Certificate user decryption 1 certificate
Select customer ID, name, city,
CONVERT(VARCHAR，DecryptByKey(CreditCardType))
As a card type,
CONVERT(VARCHAR，DecryptByKey(CreditCardNumber))
As a card number,
CONVERT(VARCHAR，DecryptByKey(Notes))
As a note
Customers from CustID = 4
Close symmetrickeyuser1symmetrickeycert.
This example shows one way that SQL Server 2005 manages keys for you. But in fact, users always choose to provide a password and use RC4 algorithm to generate a symmetric password. The code is as follows:
The following is a quote:
Create symmetric key keyuser2symmetrickkeypwd.
Authorized user 2
Use algorithm = RC4
Password encryption. ' imeG3FP '
SQL Server 2005 encrypts data by generating keys according to passwords provided by users. Passwords are not stored in SQL Server 2005 unless explicitly specified. Users must protect their passwords, otherwise anyone who knows the passwords can decrypt the data.
It is wrong to think that encrypting the data stored in the database is a complete waste of processor time and storage space. Data encryption in SQL Server 2005 is an extraordinary function, which provides an important protection layer for customers' data. However, we should pay attention to protect only those sensitive and confidential data, because encryption will consume a lot of server processor resources. If every field in a table containing10 million records is encrypted, running SELECT without a Where clause may cause server performance to crash.