• Published on august 1, 2017
Before You Proceed we recommend you to
read our Disclaimer below
The information contained in this website is for general information, Educational and Practice purposes only. The information is provided by .Raghuveera.com and while we endeavour to keep the information up to date and correct, we make no representations or warranties of any kind, express or implied, about the completeness, accuracy, reliability, suitability or availability with respect to the website or the information, products, services, or related graphics contained on the website for any purpose. Any reliance you place on such information is therefore strictly at your own risk.
In no event will we be liable for any loss or damage including without limitation, indirect or consequential loss or damage, or any loss or damage whatsoever arising from loss of data or profits arising out of, or in connection with, the use of this website.
Through this website you are able to link to other websites which are not under the control of [business name]. We have no control over the nature, content and availability of those sites. The inclusion of any links does not necessarily imply a recommendation or endorse the views expressed within them.
Every effort is made to keep the website up and running smoothly. However, [business name] takes no responsibility for, and will not be liable for, the website being temporarily unavailable due to technical issues beyond our control.
Most databases enable encryption technologies, Microsoft named its encryption as TDE also called a Transparent Data Encryption
In encryption all the attributes are completely supplied by the user or predefined the user . Below are the attributes in encryption.
However If you want to configure your own encryption with other parameters customised I would recommend you to go to https://docs.microsoft.com/en-us/sql/t-sql/functions/cryptographic-functions-transact-sql
SQL SERVER uses Certificate based encryption by making a private key and a certficate. which are needed for decrypting an encrypted database while using TDE .
I have a database named 'xxxvi' in my instance
USE master; GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'p@55wor0M@5t3r'; GO --we have created a masterkey here
Step 2: Create certificate
CREATE CERTIFICATE XXXVI_DB_Cert WITH SUBJECT = 'XXXVI_DB_Certificate'; GO -- we have created a certifcate now
Step 3: Encrypt the database creating an encryption Key with Algorithim, Bitrate and encrypt the test database with a server Certifcate
USE xxxxvi; GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE XXXVI_DB_Cert; GO
sql server will now prommpt us to back up the certificate and key so
Step 4: Backup The credentials
USE master GO BACKUP CERTIFICATE XXXVI_DB_Cert TO FILE = 'D:\Backup\XXXVI_DB_Cert.cer' WITH PRIVATE KEY (FILE = 'D:\Backup\XXXVI_DB_Cert_Key.pvk', ENCRYPTION BY PASSWORD = 'XXXVIT3stD@8@Pw6' ) GO
Step 5: Backup The credentials
USE master GO BACKUP CERTIFICATE XXXVI_DB_Cert TO FILE = 'D:\Backup\XXXVI_DB_Cert.cer' WITH PRIVATE KEY (FILE = 'D:\Backup\XXXVI_DB_Cert_Key.pvk', ENCRYPTION BY PASSWORD = 'XXXVIT3stD@8@Pw6' ) GO
Step 6:Turn Encryption ON
ALTER DATABASE xxxxvi SET ENCRYPTION ON; GO
Step 7: Verification
USE master GO SELECT *FROM sys.certificates WHERE pvt_key_encryption_type <> 'NA' GO --Check Certificates USE master GO SELECT key_length, key_algorithm, encryption_state, create_date FROM sys.dm_database_encryption_keys GO --Check Encryption Key Details for 2008 USE master GO SELECT encryptor_type, key_length, key_algorithm, encryption_state, create_date FROM sys.dm_database_encryption_keys GO --Check Encryption Key Details for 2012 and further versions
Continued to Next Page ------>> Moving Encrypted database to a different instance