Događaji
M03 31 23 - M04 2 23
Najveći događaj učenja jezika SQL, platforme Fabric i platforme Power BI. 31. mart - 2. april Koristite kod FABINSIDER da uštedite 400 dolara.
Registrirajte se danasOvaj preglednik više nije podržan.
Nadogradite na Microsoft Edge da iskoristite najnovije osobine, sigurnosna ažuriranja i tehničku podršku.
Applies to:
SQL Server
This article describes how to enable transparent data encryption (TDE) in SQL Server to protect a database encryption key by using an asymmetric key stored in an extensible key management (EKM) module with Transact-SQL.
TDE encrypts the storage of an entire database by using a symmetric key called the database encryption key. The database encryption key can also be protected using a certificate, which is protected by the database master key of the master database. For more information about protecting the database encryption key by using the database master key, see Transparent Data Encryption (TDE). For information about configuring TDE when SQL Server is running on an Azure VM, see Extensible Key Management Using Azure Key Vault (SQL Server). For information about configuring TDE using a key in the Azure key vault, see Use SQL Server Connector with SQL Encryption Features.
You must be a high privileged user (such as a system administrator) to create a database encryption key and encrypt a database. That user must be able to be authenticated by the EKM module.
Upon startup the Database Engine must open the database. To do this, you should create a credential that will be authenticated by the EKM, and add it to a login that is based on an asymmetric key. Users cannot sign in using that login, but the Database Engine will be able to authenticate itself with the EKM device.
If the asymmetric key stored in the EKM module is lost, the database will not be able to be opened by SQL Server. If the EKM provider lets you back up the asymmetric key, you should create a backup and store it in a secure location.
The options and parameters required by your EKM provider can differ from what is provided in the code example below. For more information, see your EKM provider.
This article uses the following permissions:
To change a configuration option and run the RECONFIGURE statement, you must be granted the ALTER SETTINGS server-level permission. The ALTER SETTINGS permission is implicitly held by the sysadmin and serveradmin fixed server roles.
Requires ALTER ANY CREDENTIAL permission.
Requires ALTER ANY LOGIN permission.
Requires CREATE ASYMMETRIC KEY permission.
Requires CONTROL permission on the database to encrypt the database.
Copy the files supplied by the EKM provider to an appropriate location on the SQL Server computer. In this example, we use the C:\EKM_Files folder.
Install certificates to the computer as required by your EKM provider.
Bilješka
SQL Server does not supply an EKM provider. Each EKM provider can have different procedures for installing, configuring, and authorizing users. Consult your EKM provider documentation to complete this step.
In Object Explorer, connect to an instance of Database Engine.
On the Standard bar, click New Query.
Copy and paste the following example into the query window and click Execute.
-- Enable advanced options.
sp_configure 'show advanced options', 1 ;
GO
RECONFIGURE ;
GO
-- Enable EKM provider
sp_configure 'EKM provider enabled', 1 ;
GO
RECONFIGURE ;
GO
-- Create a cryptographic provider, which we have chosen to call "EKM_Prov," based on an EKM provider
CREATE CRYPTOGRAPHIC PROVIDER EKM_Prov
FROM FILE = 'C:\EKM_Files\KeyProvFile.dll' ;
GO
-- Create a credential that will be used by system administrators.
CREATE CREDENTIAL sa_ekm_tde_cred
WITH IDENTITY = 'Identity1',
SECRET = 'q*gtev$0u#D1v'
FOR CRYPTOGRAPHIC PROVIDER EKM_Prov ;
GO
-- Add the credential to a high privileged user such as your
-- own domain login in the format [DOMAIN\login].
ALTER LOGIN Contoso\Mary
ADD CREDENTIAL sa_ekm_tde_cred ;
GO
-- create an asymmetric key stored inside the EKM provider
USE master ;
GO
CREATE ASYMMETRIC KEY ekm_login_key
FROM PROVIDER [EKM_Prov]
WITH ALGORITHM = RSA_512,
PROVIDER_KEY_NAME = 'SQL_Server_Key' ;
GO
-- Create a credential that will be used by the Database Engine.
CREATE CREDENTIAL ekm_tde_cred
WITH IDENTITY = 'Identity2'
, SECRET = 'jeksi84&sLksi01@s'
FOR CRYPTOGRAPHIC PROVIDER EKM_Prov ;
-- Add a login used by TDE, and add the new credential to the login.
CREATE LOGIN EKM_Login
FROM ASYMMETRIC KEY ekm_login_key ;
GO
ALTER LOGIN EKM_Login
ADD CREDENTIAL ekm_tde_cred ;
GO
-- Create the database encryption key that will be used for TDE.
USE AdventureWorks2022;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER ASYMMETRIC KEY ekm_login_key ;
GO
-- Alter the database to enable transparent data encryption.
ALTER DATABASE AdventureWorks2022
SET ENCRYPTION ON ;
GO
For more information, see the following:
Događaji
M03 31 23 - M04 2 23
Najveći događaj učenja jezika SQL, platforme Fabric i platforme Power BI. 31. mart - 2. april Koristite kod FABINSIDER da uštedite 400 dolara.
Registrirajte se danasObučavanje
Certifikacija
Microsoft Certified: Azure Database Administrator Associate - Certifications
Administer an SQL Server database infrastructure for cloud, on-premises and hybrid relational databases using the Microsoft PaaS relational database offerings.
Dokumentacija
Extensible Key Management (EKM) - SQL Server
Learn how to configure and use Extensible Key Management and how it fits into the data encryption capabilities for SQL Server.
Move a TDE-protected database to another SQL Server - SQL Server
Describes how to protect a database using transparent data encryption (TDE) and then move the database to another instance of SQL Server using SQL Server Management Studio (SSMS) or Transact-SQL (T-SQL).
Extensible Key Management using Azure Key Vault - SQL Server
Use the SQL Server Connector for Extensible Key Management with Azure Key Vault for SQL Server.