[BOL]:Public Key Cryptography (PKI) is a form of message secrecy in which a user creates a public key and a private key. The private key is kept secret, whereas the public key can be distributed to others. Although the keys are mathematically related, the private key cannot be easily derived by using the public key. The public key is used to encrypt data and the private key is used to decrypt data. A message that is encrypted by using the public key can only be decrypted by using the correct private key. Since there are two different keys, these keys are asymmetric.

Asymmetric keys are used for securing symmetric keys. They can also be used for limited data encryption and to digitally sign database objects. An asymmetric key consists of a private key and a corresponding public key.

Create Asymmetric without Password:

If you are creating asymmetric key without password you should have master key created in the database with strong password.Asymmetric key will encrypt data using master key.

--Error creating asymmetric key without password--
Msg 15581, Level 16, State 6, Line 18
Please create a master key in the database or open the master key in the session before performing this operation.

 

USE RSASecData
GO
IF NOT EXISTS 
    (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
    CREATE MASTER KEY ENCRYPTION BY 
    PASSWORD = '23987hxJKL95QYV4369#ghf0%lekjg5k3fd117r$$#1946kcj$n44ncjhdlj'
GO

CREATE  ASYMMETRIC KEY _rsa_customer_master_key WITH ALGORITHM = RSA_1024

Now how to encrypt data. 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.

ALTER TABLE [_rsa_customer_master] ALTER COLUMN [XXYT_CARNO] varbinary(255);
GO
update [dbo].[_rsa_customer_master] SET XXYT_CARNO=EncryptByAsymKey(AsymKey_ID('_rsa_customer_master_key'), XXYT_CARNO)
GO
SELECT  ID,XXYT_NO,XXYT_NAME,convert(BIGINT,DecryptByAsymKey(AsymKey_ID('_rsa_customer_master_key'), XXYT_CARNO)) FROM [_rsa_customer_master]

 

Note:  This method of encryption require application and database level changes.