Continuation from http://sqlcontent.com/index.php/category/sql-server-encryption/
Asymmetric Keys Encrypt by Password do not require to have master key in the database.
The following example creates an asymmetric key named _rsa_customer_accmaster_key by using the RSA_2048
algorithm, and protects the private key with a password.
USE RSASecData GO CREATE ASYMMETRIC KEY _rsa_customer_accmaster_key WITH ALGORITHM = RSA_2048 ENCRYPTION BY PASSWORD = 'G%$D@3@f5-34+!DF'; GO
To Encrypt data in existing table, the column data type need to be modified to use [varbinary]. The existing data in the column will not have any impact unless updated with encryption keys.New data inserted without using EncryptByAsymKey function will be lost.
Remember to put the password in the given format N’password’
CREATE TABLE _rsa_customer_accmaster ( ID BIGINT IDENTITY NOT NULL, XXYT_NO BIGINT NOT NULL, XXYT_NAME VARCHAR(255) NOT NULL, XXYT_CARNO BIGINT NULL, XXYT_DTDT DATE, XXYT_DTYY DATE ) INSERT INTO _rsa_customer_accmaster (XXYT_NO,XXYT_NAME,XXYT_CARNO,XXYT_DTDT,XXYT_DTYY) VALUES (34343432289779,'Mohammad Sufian',56756756747367763,getdate(),getdate()) SELECT * FROM _rsa_customer_accmaster ALTER TABLE [_rsa_customer_accmaster] ALTER COLUMN [XXYT_CARNO] varbinary(500); GO update [dbo].[_rsa_customer_accmaster] SET XXYT_CARNO=EncryptByAsymKey(AsymKey_ID('_rsa_customer_accmaster_key'), XXYT_CARNO) GO SELECT ID,XXYT_NO,XXYT_NAME,convert(BIGINT,DecryptByAsymKey(AsymKey_ID('_rsa_customer_accmaster_key'), XXYT_CARNO,N'G%$D@3@f5-34+!DF')) FROM [_rsa_customer_accmaster]
Note: This method of encryption require application and database level changes.