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.