Sqlserver encryption and decryption function (asymmetric key certificate encryption symmetric key) usage code

Time:2022-4-21

Encryptbyasymkey() — asymmetric key
Encryptbycert() — certificate encryption
Encryptbykey() — symmetric key
Encrypt passphrase() — passphrase encryption

Copy codeThe codes are as follows:
–Encryption function in sqlserver July 11, 2013
Encryptbyasymkey() — asymmetric key
Encryptbycert() — certificate encryption
Encryptbykey() — symmetric key
Encrypt passphrase() — passphrase encryption

—————————————————————————————
–The asymmetric key contains the internal public key and private key at the database level, which can be used to encrypt and decrypt the data in the SQL Server database,
–It can be imported from an external file or assembly, or generated in a SQL Server database. Unlike certificates, it cannot be backed up to files.
–This means that once it is created in SQL server, there is no very simple way to reuse the same key in other user databases.
Asymmetric key is a high security option for database encryption, so more SQL server resources are required.
–Let’s look at a set of examples:

–Example 1: create an asymmetric key

–To create an asymmetric key, use the following command:

–CREATE ASYMMETRIC KEY 

 

–The following statement creates an asymmetric key asymdemokey

use [pratice]
go

Create asymmetric key asymdemokey — create asymmetric key name
WITH ALGORITHM = RSA_ 512 — encryption security type
ENCRYPTION BY PASSWORD = ‘123!’ — password

–Example 2: View asymmetric keys in the current database

–Use catalog view sys asymmetric_ keys( http://msdn.microsoft.com/en-us/library/ms188399.aspx )To see.

–View asymmetric keys in the current database
USE [pratice]
go

SELECT name, algorithm_desc, pvt_key_encryption_type_desc
FROM sys.asymmetric_keys

–Example 3: modify the private key password of asymmetric key

–You can use with encryption by password and encryption by password options
–ALTER ASYMMETRIC KEY( http://technet.microsoft.com/en-us/library/ms189440.aspx )Change the password of the private key

–Modify private key password
Alter asymmetric key asymdemokey — name of key to be modified
With private key — private key
(encryption by password = ‘456’ — specify a new password)
Definition by password =’123! ‘)– The old password is used to decrypt

–Example 4: encrypt and decrypt data with asymmetric key.

–Because both public key and key are needed, it is a very safe way to use asymmetric key to encrypt data when maintaining confidential data.
–But it will consume more resources when it is used for large data sets at the same time.

–Asymmetric key encryption is not recommended, but it is still an option. Once the asymmetric key is added to the database, it can be used to encrypt and decrypt data.

–The following two SQL functions are used:

–Encryptbyasymkey encrypts data. ( http://technet.microsoft.com/en-us/library/ms186950.aspx )

–Decryptbyasymkey decrypts data. ( http://msdn.microsoft.com/en-us/library/ms189507.aspx )

–Note that when encrypting by certificate, decryptbyasymkey returns the encrypted data of varbinary type.

–Here is an example:

use [pratice]
go

–Create data that needs to be encrypted
Create Table BankUser
(PKID int primary key identity(1,1)
, userno varbinary (1000) null — be sure to use binary data type
,CurState datetime   not null
)
go

insert into BankUser
(UserNo,CurState)
VALUES (EncryptByAsymKey(AsymKey_ID(‘asymDemoKey’),’137′),GETDATE())
–Insert a record, and the field userno stores the encrypted number value
go

 

–View unencrypted data:
SELECT PKID,Curstate,
cast
(DecryptByAsymKey(AsymKey_ID(‘asymDemoKey’),UserNo,N’123′)
As varchar (1000)) as userno — original private key required
from BankUser

 

 

–Example 5: delete asymmetric key

–Command: drop asymmetric key deletes the specified asymmetric key( http://msdn.microsoft.com/en-us/library/ms188389.aspx )

–Example:
DROP ASYMMETRIC KEY asymDemoKey

DROP TABLE [dbo].[BankUser]

–Summary:

–1. This paper mainly introduces the creation, deletion and viewing of asymmetric key, and uses it to modify the private key and encrypt and decrypt data.

–2. The asymmetric key contains the internal public key and private key at the database level. It can be used to encrypt and decrypt the data in the SQL Server database.

–3. Asymmetric key is a high security option for database encryption, so it requires more SQL server resources and is not recommended.

–The following will mainly introduce the relatively simple and widely used symmetric key encryption
—————————————————————————————————–

–SQL Server 2008 introduces transparent data encryption (TDE)
–It allows you to encrypt the entire database without modifying the application code at all.

–When a user database is available and TDE is enabled, encryption is implemented at the page level when writing to disk. Decrypt when the data page is read into memory.
–If the database file or database backup is stolen, it will not be accessible without the original certificate used for encryption.
–This is almost the most exciting feature in SQL Server 2008 security options. With it, we can at least keep out some primary malicious peeping.
–The following two examples show how to enable and maintain transparent data encryption
–Example 1: enable transparent encryption (TDE)

USE Master
GO
–Delete old master key
–Drop MASTER KEY
–GO
–Create master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD =’B19ACE32-AB68-4589-81AE-010E9092FC6B’
GO
–Create a certificate for transparent data encryption
CREATE CERTIFICATE TDE_Server_Certificate
WITH SUBJECT = ‘Server-level cert for TDE’
GO

USE [pratice]
GO
–Step 1: now start transparent encryption
Create database encryption key — creates a database encryption key
    WITH ALGORITHM = TRIPLE_ DES_ 3Key — encryption method
    ENCRYPTION BY SERVER CERTIFICATE TDE_ Server_ Certificate — use server level certificate encryption
GO
/*
Warning: The certificate used for encrypting the database encryption key
has not been backed up.
You should immediately back up the certificate and the private key
associated with the certificate.
If the certificate ever becomes unavailable or
if you must restore or attach the database on another server,
you must have backups of both the certificate and the private key
or you will not be able to open the database.
*/

–Step 2: turn on the encryption switch
ALTER DATABASE [pratice] SET ENCRYPTION ON
GO

–Check whether the database is encrypted
SELECT  is_encrypted FROM    sys.databases
WHERE   name = ‘pratice’

–Note: once encryption is applied to the database, the server level certificate should be backed up immediately!

–Without the certificate for encrypting DEK, the database cannot be opened, attached to other servers cannot be used, and the database file will not be hacked.
–If a DBA wants to legally move a database from one SQL server instance to another,
–Then she should first back up the server level certificate and then create the certificate in the new SQL server instance.
–At this time, the database or additional data and log files can be legally backed up and restored.

–Example 2: managing and removing transparent encryption (TDE)
USE [pratice]
GO
–Modify encryption algorithm
ALTER DATABASE  ENCRYPTION KEY
REGENERATE WITH ALGORITHM = AES_128
Go

SELECT  DB_NAME(database_id) databasenm,
        CASE encryption_state
          WHEN 0 THEN ‘No encryption’
          WHEN 1 THEN ‘Unencrypted’
          WHEN 2 THEN ‘Encryption in progress’
          WHEN 3 THEN ‘Encrypted’
          WHEN 4 THEN ‘Key change in progress’
          WHEN 5 THEN ‘Decryption in progress’
        END encryption_state, key_algorithm, key_length
FROM    sys.dm_database_encryption_keys

/*
The encryption processing of all user databases also includes the processing of tempdb, because tempdb is used for table connection and temporary tables
databasenm encryption_state key_algorithm key_length
tempdb Encrypted AES 256
DB_Encrypt_Demo Encrypted AES 128
*/

–In addition to changing the DEK algorithm, we can also change the server level certificate used to encrypt the database (the certificate should be changed regularly)

USE master
GO
CREATE CERTIFICATE TDE_Server_Certificate_V2
WITH SUBJECT = ‘Server-level cert for TDE V2’
GO
USE [pratice]
GO
ALTER DATABASE ENCRYPTION KEY
ENCRYPTION BY SERVER CERTIFICATE TDE_Server_Certificate_V2
–Modify DEK with new certificate

–Remove database transparent encryption
ALTER DATABASE [pratice]
SET ENCRYPTION OFF
GO

–After removing TDE, DEK can be deleted
USE [pratice]
GO
DROP DATABASE ENCRYPTION KEY
Go

–Note: if DEK is the last user-defined database using TDE in the SQL server instance,
–After the SQL server instance is restarted, tempdb will also become unencrypted.

–Summary:

–1. This paper mainly introduces the use of transparent data encryption (TDE).

–2. The modification of DEK also affects the encryption status of tempdb database.
————————————————————————————————

–Code security in SQL Server 2008 (VII) certificate encryption

–Certificates can encrypt and decrypt data in the database.
–A certificate contains a key pair, information about the certificate owner, the start and end expiration dates when the certificate is available.

–The certificate contains both public key and key. The former is used for encryption and the latter for decryption. SQL server can generate its own certificate,
–You can also load from an external file or assembly. Because they can be backed up and loaded from files, certificates are easier to migrate than asymmetric keys,
–Asymmetric keys can’t. This means that the same certificate can be easily reused in the database.

–Note: certificates and asymmetric keys consume the same resources.

–Let’s look at a set of examples:

–Example 1: create a database certificate

–Create database certificate: create symmetric key( http://msdn.microsoft.com/en-us/library/ms187798.aspx )
USE [pratice]
GO
–Create certificate
CREATE CERTIFICATE cert_ Demo — certificate name
ENCRYPTION BY PASSWORD = ‘123!!!’ — Password for encryption certificate
WITH SUBJECT = ‘DB_ Encrypt_ Demo database encryption certificate ‘, — certificate subject
START_ DATE = ‘3/14/2012′, EXPIRY_ Date = ’10 / 20 / 2016’ — start and end date
GO

–Example 2: view the certificate in the database

–Use catalog view sys certificates( http://msdn.microsoft.com/en-us/library/ms189774.aspx )To see.

–View certificates in the current database
USE [pratice]
go
–View Certificate
SELECT  name, pvt_key_encryption_type_desc, issuer_name
FROM    sys.certificates

–Example 3: backup and restore certificate

–After you create a certificate, you can also use backup certificate( http://msdn.microsoft.com/en-us/library/ms178578.aspx )
–Command to back up to a file in order to save it safely or restore it in another database.
–Backup certificate
BACKUP CERTIFICATE cert_Demo
TO FILE = ‘D:\certDemo. Bak ‘- Certificate backup path, used for encryption
With private key (file =’d: \ certdemopk. Bak ‘– path of certificate private key file, used to decrypt
ENCRYPTION BY PASSWORD = ‘465!!!’,– Encrypt private key password
DECRYPTION BY PASSWORD = ‘123!!!’ )– Decrypt private key password

–After the backup, you can use this certificate in other databases or delete it using the drop certificate command.
DROP CERTIFICATE cert_Demo
GO
–Restore the certificate from the backup file to the database
CREATE CERTIFICATE cert_Demo
FROM FILE = ‘D:\\certDemo.BAK’
WITH PRIVATE KEY (FILE = ‘D:\certDemoPK.BAK’,
DECRYPTION BY PASSWORD = ‘456!!!’ ,– Decrypt private key password
ENCRYPTION BY PASSWORD = ‘123!!!’)– Encrypt private key password

–Example 4: manage the private key of the certificate

–Use alter certificate( http://msdn.microsoft.com/en-us/library/ms189511.aspx )
–Command to add or remove a private key for a certificate.
–This command allows you to delete the private key (encrypted by the database master key by default), add the private key, or change the password of the private key.

–Remove private key from certificate
ALTER CERTIFICATE cert_Demo
REMOVE PRIVATE KEY

–Re add the private key for the existing certificate from the backup file
ALTER CERTIFICATE cert_Demo
WITH PRIVATE KEY
(FILE = ‘D:\certDemoPK.BAK’,
DECRYPTION BY PASSWORD = ‘1234GH!!!’ ,– Decrypt private key password
ENCRYPTION BY PASSWORD = ‘123!!!’)– Encrypt private key password

–Modify the password of the existing private key
ALTER CERTIFICATE cert_Demo
WITH PRIVATE KEY (DECRYPTION BY PASSWORD = ‘123!!!’,
ENCRYPTION BY PASSWORD = ‘789!!!13E’)

–Example 5: use certificate encryption and decryption.

–Use the function encryptbycert to encrypt data. ( http://msdn.microsoft.com/zh-cn/library/ms174361.aspx )
USE [pratice]
GO
CREATE TABLE PWDQuestion
(
  CustomerID INT ,
  PasswordHintQuestion NVARCHAR(200) ,
  PasswordHintAnswer NVARCHAR(200)
)
–Insert test data
INSERT  dbo.PWDQuestion ( CustomerID, PasswordHintQuestion, PasswordHintAnswer )
Values (10, ‘name of the hospital where you were born?’, encryptbycert (cert_id (‘cert_demo ‘),’ Beijing Quadrangle home ‘))

–View ciphertext
SELECT [PasswordHintQuestion], CAST(PasswordHintAnswer AS VARCHAR(200)) PasswordHintAnswer
FROM    dbo.PWDQuestion
WHERE   CustomerID = 10

–View original text
SELECT  PasswordHintQuestion,
        CAST(DECRYPTBYCERT(CERT_ID(‘cert_Demo’), PasswordHintAnswer,
                           N’789!!!13E’) AS VARCHAR(200)) PasswordHintAnswer
FROM    dbo.PWDQuestion
WHERE   CustomerID = 10

 

–Example 6: encrypt and decrypt data with symmetric key.

–In the previous article, you have seen a demonstration of opening a symmetric key encrypted with an asymmetric key,
–It is divided into two steps. First, use the open symmetric key command, and then call the actual decryptbykey function.
–SQL server also provides additional decryption functions that can combine these two steps:
–DECRYPTBYKEYAUTOASYMKEY(http://msdn.microsoft.com/en-us/library/ms365420.aspx)
–And decryptbykeyautocert( http://msdn.microsoft.com/en-us/library/ms182559.aspx )
USE [pratice]
GO

–This example uses the database master password encryption, so no password is required.
—-Create master Key Encryption By password=’123ASD!’
—-go

–Create asymmetric key
CREATE ASYMMETRIC KEY asymDemo_V2
WITH ALGORITHM = RSA_512
–Create symmetric key
CREATE SYMMETRIC KEY sym_Demo_V2
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY ASYMMETRIC KEY asymDemo_V2

–Open the symmetric key and insert the record
OPEN SYMMETRIC KEY sym_Demo_V2
DECRYPTION BY ASYMMETRIC KEY asymDemo_V2
INSERT  dbo.PWDQuestion ( CustomerID, PasswordHintQuestion, PasswordHintAnswer )
Values (22, ‘name of the hospital where you were born?’, encryptbykey (key_guid (‘sym_demo_v2 ‘),’ Shaw Hospital ‘))
CLOSE SYMMETRIC KEY sym_Demo_V2

–At this time, only one operation is required to decrypt data using decryptbykeyautoasymkey
SELECT  CAST(DECRYPTBYKEYAUTOASYMKEY(ASYMKEY_ID(‘asymDemo_V2’), NULL,
                                     PasswordHintAnswer) AS VARCHAR)
FROM    dbo.PWDQuestion
WHERE   CustomerID = 22

–Summary:

–1. This paper mainly introduces the creation, deletion and viewing of certificate, and uses it to modify the encryption method, encrypt and decrypt data.

–2. Certificate encryption and asymmetric key encryption consume more resources than symmetric key encryption.

–The following will focus on the most encouraging transparent data encryption (TDE) in SQL server
—————————————————————————————–
–Code security in SQL Server 2008 (VI): symmetric key encryption

–The certificate and asymmetric key encrypt the data using the database level internal public key and decrypt the data using the database level internal private key.
Symmetric keys are relatively simple. They contain a key used for encryption and decryption at the same time.
–For this reason, using symmetric keys to encrypt data is faster and more suitable for big data.
–Although complexity is a factor to consider, it is still a good choice for encrypting data.

 

–Example 1: create a symmetric key

–The characteristic of symmetric key is that it must be opened before using it to encrypt and decrypt data in database session.

–To create a symmetric key, use the following command: create symmetric key.
–(http://msdn.microsoft.com/en-us/library/ms188357.aspx)

USE [pratice]
go

–Create an asymmetric key for encrypting a symmetric key
Create asymmetric key symdemokey — name
WITH ALGORITHM = RSA_ 512 — encryption algorithm
ENCRYPTION BY PASSWORD =’TestSYM456!’
–Code

–Create a symmetric key
CREATE SYMMETRIC KEY sym_Demo
WITH ALGORITHM = TRIPLE_DES
ENCRYPTION BY ASYMMETRIC KEY symDemoKey

–Example 2: view the symmetric key in the current database

–Use catalog view sys symmetric_ keys( http://msdn.microsoft.com/en-us/library/ms189446.aspx )To see.
–View asymmetric keys in the current database
USE [pratice]
go
SELECT  name, algorithm_desc
FROM    sys.symmetric_keys

–Example 3: modify the encryption method of asymmetric key

–You can use alter symmetric key( http://technet.microsoft.com/en-us/library/ms189440.aspx )Command modification
–Encryption method of symmetric key.
–However, open symmetric key must be used before execution( http://msdn.microsoft.com/en-us/library/ms190499.aspx )Command to open it.
USE [pratice]
go

–First open the symmetric key with the private key password
OPEN SYMMETRIC KEY sym_Demo
DECRYPTION BY ASYMMETRIC KEY symDemoKey
WITH PASSWORD =’TestSYM456!’

–After opening, add password encryption to replace the original key
ALTER SYMMETRIC KEY sym_Demo
ADD ENCRYPTION BY PASSWORD =’newnew!456′
–Then delete the asymmetric key encryption
ALTER SYMMETRIC KEY sym_Demo
DROP ENCRYPTION BY ASYMMETRIC KEY symDemoKey
–When you are finished, turn off the symmetric key
CLOSE SYMMETRIC KEY sym_Demo

–Example 4: encrypt and decrypt data using a symmetric key.

–1. In order to encrypt data with a symmetric key, you must first open it,
–Then use the function encryptbykey to encrypt the data. ( http://msdn.microsoft.com/zh-cn/library/ms174361.aspx )

–2. Decryptbykey is used to decrypt data encrypted with symmetric key. Note that decryptbykey is not like encryptbykey,
–Symmetric key GUID is not required. Therefore, in order to decrypt, the correct symmetric key session must be opened, otherwise null will be displayed.

–Here is an example:
USE [pratice]
GO
–DROP TABLE [dbo].[PWDQuestion]
–GO
–Create a test data table for symmetric encryption
CREATE TABLE dbo.PWDQuestion
(
  CustomerID INT NOT NULL PRIMARY KEY ,
  PasswordHintQuestion NVARCHAR(300) NOT NULL ,
  PasswordHintAnswer VARBINARY(200) NOT NULL
)
GO
–Insert encrypted data
OPEN SYMMETRIC KEY sym_Demo
DECRYPTION BY PASSWORD =’newnew!456′
INSERT  dbo.PWDQuestion ( CustomerID, PasswordHintQuestion, PasswordHintAnswer )
Values (12, ‘name of the hospital where you were born?’, encryptbykey (key_guid (‘sym_demo ‘),’ Hangzhou first ‘))
CLOSE SYMMETRIC KEY sym_Demo

–View unencrypted data:
–Decrypt data
OPEN SYMMETRIC KEY sym_Demo
DECRYPTION BY PASSWORD =’newnew!456′
SELECT  CustomerID, PasswordHintQuestion,
        CAST(DECRYPTBYKEY(PasswordHintAnswer) AS VARCHAR(200)) PasswordHintAnswer
FROM    dbo.PWDQuestion
WHERE   CustomerID = 12
–Remember to close after opening!!!
CLOSE SYMMETRIC KEY sym_Demo

–Do not open direct read
SELECT  CustomerID, PasswordHintQuestion,
        CAST(DECRYPTBYKEY(PasswordHintAnswer) AS VARCHAR(200)) PasswordHintAnswer
FROM    dbo.PWDQuestion
WHERE   CustomerID = 12

–So far, it seems to have been completed. Don’t, don’t be happy too early!

–There is a problem here. If the malicious user does not know the true value of the passwordhintanswer column with CustomerID = 13,
–But know the true value of passwordhintanswer column with CustomerID = 14,
–You can bypass encryption by maliciously replacing the passwordhintanswer column!!
–At this time, we simply encrypt the CustomerID column as the verification column to avoid future trouble!

–Note: the encrypted verification column can also be passed in as a parameter from the column of another related table.

–Example 5: delete symmetric key

–Command: drop symmetric key deletes the specified symmetric key( http://technet.microsoft.com/en-us/library/ms182698.aspx )

–Example:

DROP SYMMETRIC KEY sym_Demo
–Note: if the encryption key is opened but not closed, drop fails.

–Summary:

–1. This paper mainly introduces the creation, deletion and viewing of symmetric key, and uses it to modify the encryption method, encrypt and decrypt data.

–2. The characteristic of symmetric key is that it must be opened before using it to encrypt and decrypt data in database session.

–3. Symmetric key can be used for big data encryption.
——————————————————————————————-
–Code security in SQL Server 2008 (IV): master key
–Encryption in SQL server is handled in a hierarchical form to provide multiple levels of security.
–SQL server contains two key types for encrypting data. As shown below:
–1. The server master key is located at the top of the hierarchy and is automatically created when SQL server is installed,
–Used to encrypt system data, linked server login and database master key.
–When using the service master key to encrypt the certificate, database master key or linked server master password through SQL server for the first time,
–The service master key is automatically generated and generated using the windows certificate of the SQL Server service account.
–If the SQL Server service account must be changed, Microsoft recommends using SQL Server Configuration Manager,
–Because this tool will perform the appropriate decryption and encryption methods required to generate the new service master key, and can keep the encryption hierarchy intact.
–The service master key is also used to encrypt the database master key under it.

–2. The database master key is used to encrypt certificates, asymmetric keys and symmetric keys.
–All databases can contain only one database master key, which is encrypted by the service master key when it is created.
–When creating an asymmetric key, you can decide whether the private key corresponding to the encrypted asymmetric key contains a password.
–If the display contains a password, the database master key is used to encrypt the private key.

–Let’s look at a set of examples:

–Example 1: backup and restore service master key
–Example 1: backup and restore service master key

–The following two SQL commands are used:

–Backup service master key # export the service master key. ( http://msdn.microsoft.com/zh-cn/library/ms190337.aspx )

–Restore service master key imports the service master key from the backup file. ( http://msdn.microsoft.com/zh-cn/library/ms187972.aspx )

–The following statement backs up the service master key to C: \ sqlbackup \ SMK bak

BACKUP SERVICE MASTER KEY
TO FILE=’D:\SMK.bak’
ENCRYPTION BY PASSWORD =’123!1AB’
—-Note that the password can use single quotes
go

–Recovery service master key
RESTORE SERVICE MASTER KEY
FROM FILE=’D:\SMK.bak’
DECRYPTION BY PASSWORD =’123!1AB’
go

 

–If there is no actual change in the key, you will receive a prompt when performing key recovery:

–The old and new master keys are identical. No data re-encryption is required.

–Example 2: create, regenerate and delete the database master key

–The following two SQL commands are used:

–Create master key( http://technet.microsoft.com/zh-cn/library/ms174382.aspx )

–Alter master key regenerates the database master key( http://msdn.microsoft.com/en-us/library/ms186937%28SQL.90%29.aspx )

–Drop master key delete database master key( http://msdn.microsoft.com/en-us/library/ms180071.aspx )

–When the database master key is explicitly created, an additional security layer will be automatically generated at the same time,
–It is used to encrypt the new certificate and asymmetric key in the database to further protect the encrypted data.

IF NOT EXISTS ( SELECT  name
                FROM    sys.databases
                WHERE   name = ‘BookStore’ )
    BEGIN
        CREATE DATABASE BookStore
    END
GO

USE BookStore
GO
–Create database master key
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘password’
go

USE BookStore
GO
–Regenerate database master key
ALTER MASTER KEY REGENERATE WITH ENCRYPTION BY PASSWORD =’password’

–Delete database master key
USE BookStore
GO
DROP MASTER KEY

–Note: if the database master key is still used by other database objects, it cannot be deleted, which is similar to the schema.

–At the same time, once the database master key is created, it is a good habit to back it up immediately.
–Backup database master key
USE BookStore
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘MagneticFields!’
GO
BACKUP MASTER KEY TO FILE=’D:\BookStore_Master_Key.BAK’
ENCRYPTION BY PASSWORD =’4D280837!!!’

–Recover database master key
RESTORE MASTER KEY FROM FILE=’D:\BookStore_Master_Key.BAK’
DECRYPTION BY PASSWORD =’4D280837!!!’
ENCRYPTION BY PASSWORD =’MagneticFields!’

–Similar to the service master key, if it is not modified, you will receive the following prompt:

–The old and new master keys are identical. No data re-encryption is required.

–Example 3: delete the service master key from the database master key

–When a database master key is created, it is encrypted in two ways by default:
–The service master key and the password used in the create master key command.
–If you don’t want to use the service master password to encrypt the database master key
–(in this case, login with sysadmin privilege cannot access the encrypted data without knowing the database master key),
–You can use the alter master key command to delete the service master key.

–The brief syntax is as follows:

ALTER MASTER KEY

ADD ENCRYPTION BY SERVICE MASTER KEY |

DROP ENCRYPTION BY SERVICE MASTER KEY

–Since the service master key allows users with sufficient licenses (such as sysadmin) to automatically decrypt with the database master key,
–Therefore, once you delete the encryption of the service master key and want to modify the database master key, you must use a new command to access it.
–Open master key, the syntax is as follows:

OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘password’

–Here is an example:
ALTER MASTER KEY DROP ENCRYPTION BY SERVICE MASTER KEY

–Once executed, any modification of the database master key needs to be accessed with the password of open master key,
–This is to reapply the encryption of the service master key
OPEN MASTER KEY DECRYPTION BY PASSWORD =’123456!’

–Once the service master key is used to encrypt the database master key, the database master key no longer needs to be explicitly turned on or off.
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

–Close database master key
CLOSE MASTER KEY

–Summary:

–1. This paper mainly introduces the backup and restore of service master key, and the creation, regeneration, deletion, backup and restore of database master key.

–2. Once the master key is created, it is a good habit to back it up immediately.

————————————————————————————
–Code security in SQL Server 2008 (III): encryption through passphrase

–Introduction: This paper mainly involves encryptbypassphrase and decryptbypassphrase functions to encrypt passphrase.

–Preface:

–Before SQL Server 2005 and SQL Server 2008.
– if you want to encrypt sensitive data, such as financial information, wages or ID number, you must rely on external applications or algorithms.
–SQL Server 2005 introduces the ability of built-in data encryption, which is completed by the combination of certificate, key and system function.

–Similar to digital certificates. SQL server certificate includes a pair of keys, public key and private key, which are used to encrypt and decrypt data.
–SQL server also has the ability to create asymmetric keys and symmetric key objects. Asymmetric keys are similar to certificates,
–The public key is used to encrypt the database and the private key is used to decrypt the data.
–Asymmetric keys and certificates provide strong encryption strength. However, it has more performance overhead in the process of completing complex encryption and decryption.
–The solution that is more suitable for encrypting a large amount of data and has low performance overhead is symmetric key,
–It is a key to encrypt and decrypt the same data.

–SQL Server allows these encryption capabilities to be placed in the encryption hierarchy.
–After installing SQL server, create a server level certificate named service master key in the database master,
–And bind it to the login name of SQL Server service account by default.
–The service master key is used to encrypt all other database certificates and keys created in the SQL server instance.
–In addition, you can also create a database master key in the user database,
–It can be used to encrypt database certificates and keys.

–In SQL Server 2008, Microsoft introduced transparent data encryption (TDE), which encrypts the entire database,
–Without modifying any applications that access it. Data, log files and related database backups are encrypted.
–If the database is stolen, the data cannot be accessed without the database encryption key (DEK).
–Several examples will be given later in this article.

–In SQL Server 2008, support for extensible key management (EKM) is also introduced,
–This means that SQL server can use hardware security module (HSM) to store and manage encryption keys.
–HSM can reduce the coupling between data and actual encryption keys.

–This part is divided into six articles:
–1. Encryption via passphrase
–2. Master key
–3. Asymmetric key encryption
–4. Symmetric key encryption
–5. Certificate encryption
–6. Transparent data encryption

–1、 Encryption via passphrase

–For emergency data encryption that does not involve certificates and keys, data can be encrypted and decrypted directly based on the password provided by the user.
–A passphrase is a password that allows spaces.
–This passphrase will not be stored in the database, which means that it will not be “cracked” by the stored system data.
–At the same time, you can use spaces to create a long, easy to remember sentence to encrypt and decrypt sensitive data.

–The pair of functions we need to know is encrypt bypass( http://technet.microsoft.com/zh-cn/library/ms190357.aspx )And decryptbypass( http://technet.microsoft.com/zh-cn/library/ms188910.aspx )

–This pair of functions must use the same parameters.

–Let’s take an example:
USE [pratice]
go

— Table used to store the encrypted data
— for the purposes of this recipe
–DROP TABLE SecretInfo
–GO

CREATE TABLE SecretInfo
(
Mysecret varbinary (max) not null — be sure to use binary data format
)
GO

Insert secret info (mysecret) select encrypt bypass (‘123456 ‘,’ hello ‘)

SELECT  MySecret FROM    SecretInfo

 

–To know the content of the original text, use the following statement:

SELECT  CAST(DECRYPTBYPASSPHRASE(‘123456’, MySecret) AS VARCHAR(MAX))
FROM    SecretInfo

–Note:

–1. Use passphrases to encrypt data without worrying about sysadmin server role members reading data
–(as you will see later, sysadmin, a member of the server role, has inherent permissions to read other forms of encrypted data.)


–2. Assuming that passwords are not stored in tables or used in any modules (stored procedures, triggers, etc.),
–Encrypted data will prevent theft from the database backup or penetration into the database in the SQL server instance.
–If the passphrase password is leaked, the data can be decrypted.