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 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 (i e , config json ) add the credential to a highly privileged user, such as your own 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 cryptohub that is 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, then insert information into the table database encryption operations cannot be executed 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 if data can be decrypted if possible, restart sql server service with the cryptohub offline, then check if the following command fails if it does, then tde is set up correctly if the cryptohub is online, the command should succeed use exampledb; select from users;