Database
Microsoft SQL Server TDE
Enabling TDE on SQL Server by using EKM
1min
this section shows how to enable transparent data encryption (tde) in sql server to protect a database encryption key by using an asymmetric key stored on the {{futurex}} ekm/hsm module you must run all the following commands inside a query window in sql server management studio to create a credential for system administrators to use, run the following command the values set in the identity and secret fields should be the name and password of the identity created on the {{vectera}} specified in the fxekm configuration file ( fxekm cfg ) create credential ekmcredential 	with 	 identity = 'crypto1', 	 secret = 'safest' 	for cryptographic provider futurexekmprovider; go add the credential to a high privileged user, such as your domain login, in the format \[domain\login] alter login \[win q4e6rg9bsol\administrator] 	add credential ekmcredential; go create an asymmetric key stored inside the {{futurex}} ekm provider use master; go create asymmetric key ekmasym 	from provider futurexekmprovider 	with 	 algorithm = rsa 2048, 	 provider key name = 'ekmasym'; go create a credential for the database engine to use the values set in the identity and secret fields should be the name and password of the identity created on the {{vectera}} specified in the fxekm configuration file ( fxekm cfg ) create credential ekmenginecredential 	with 	 identity = 'crypto1', 	 secret = 'safest' 	for cryptographic provider futurexekmprovider; go create a login to use the asymmetric key stored inside the ekm provider create login ekmlogin from asymmetric key ekmasym; go enable the login to use the database engine credential alter login ekmlogin add credential ekmenginecredential; go create a new example database, add a new table, and insert information into the table you cannot execute database encryption operations on master , model , tempdb , msdb , or resource databases create database exampledb; go use exampledb; go create table users ( 	id int not null primary key, 	name varchar(64), 	password varchar(128), 	ssn varchar(32) ); go insert into users (id, name, password, ssn) values (1, 'someguy', 'blah', '000 00 0000'), (2, 'somegal', 'password', '000 00 0000'), (3, 'testuser', 'test123', '000 00 0000'); go create a database encryption key for the exampledb database use exampledb; go create database encryption key 	with 	 algorithm = aes 256 	encryption by server asymmetric key ekmasym; go enable transparent data encryption on the exampledb database alter database exampledb 	set encryption on; go to verify that you can decrypt data, restart sql server service with the hsm offline and run the following command use exampledb; select from users; if the following command fails, then tde is set up correctly if the hsm is online, the command should succeed