Database
Microsoft SQL Server
Enable Transparent Data encryption on the Microsoft SQL Server
1min
all of the following commands need to be run inside a query window in sql server management studio perform the following steps to enable transparent data encryption on the microsoft sql server create a credential for use by system administrators create credential ekmcredential with identity = 'crypto1', secret = 'safest' for cryptographic provider fxclekmprovider; go the values set in the identity and secret fields should be the name and password of the identity created on the {{ch}} that is specified in the fxcl ekm configuration file (such as config json ) add the credential to a highly privileged user, such as your domain login alter login \[win q4e6rg9bsol\administrator] add credential ekmcredential; go create an asymmetric key stored in {{ch}} through the {{futurex}} ekm provider use master; go create asymmetric key ekmasym from provider fxclekmprovider with 	 algorithm = rsa 1024, 	 provider key name = 'ekmasym'; go create a credential for the database engine to use create credential ekmenginecredential with 	 identity = 'crypto1', 	 secret = 'safest' for cryptographic provider fxclekmprovider; go the values set in the identity and secret fields should be the name and password of the identity created on the {{ch}} specified in the fxcl ekm configuration file (such as config json ) create a login that uses the asymmetric key stored inside the fxcl ekm provider create login ekmlogin from asymmetric key ekmasym; go enable the login to use the database engine credentials alter login ekmlogin add credential ekmenginecredential; go create a new example database, add a table to it, 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 128 encryption by server asymmetric key ekmasym; go enable transparent data encryption on the exampledb database alter database exampledb set encryption on; go check whether the data can be decrypted if possible, restart sql server service with the {{ch}} offline, then check if the following command fails if it does, then tde is set up correctly if the {{ch}} is online, the command should succeed use exampledb; select from users;