Execute the following Microsoft SQL Server T-SQL scripts in SSMS Query Editor to create a user login table and populate it with 2 logins and EncryptByAsymKey encrypted passwords. Use DecryptByAsymKey to verify passwords for the logins.

USE [AdventureWorks]

GO

-- DROP TABLE [dbo].[UserLogin]

CREATE TABLE [dbo].[UserLogin](

[UserLoginID] [int] IDENTITY(1,1) PRIMARY KEY,

[LoginName] [char](30) NOT NULL,

[PassWord] varbinary(max) NOT NULL,

[IsActive] [bit] NOT NULL

      CONSTRAINT [DF_UserLogin_IsActive] DEFAULT ((1)),

[CreateDate] [smalldatetime] NOT NULL

      CONSTRAINT [DF_UserLogin_CreateDate] DEFAULT (getdate()),

[ModifyDate] [smalldatetime] NOT NULL

      CONSTRAINT [DF_UserLogin_ModifyDate] DEFAULT (getdate()),

[ModifiedBy] [char](6) NOT NULL

      CONSTRAINT [DF_UserLogin_ModifiedBy] DEFAULT ('system')

) ON [PRIMARY]

GO

-- drop ASYMMETRIC KEY Asym_PassWord

CREATE ASYMMETRIC KEY Asym_PassWord

      WITH ALGORITHM = RSA_512

      ENCRYPTION BY PASSWORD = N'secreT007!'

DECLARE @CipherString varbinary(max);

SELECT @CipherString = EncryptByAsymKey(AsymKey_ID('Asym_PassWord'),

 N'SecretPass!01');

INSERT INTO UserLogin

(LoginName, PassWord)

VALUES ('administrator', @CipherString);

GO

DECLARE @CipherString varbinary(max);

SELECT @CipherString = EncryptByAsymKey(AsymKey_ID('Asym_PassWord'),

N'OperPass99$');

INSERT INTO UserLogin

(LoginName, PassWord)

VALUES ('operator', @CipherString);

GO

SELECT * FROM UserLogin

GO

-- Following query can be used to test an entered password:

SELECT  LoginName,

PassWordDecrypted= convert(nvarchar(128),

DecryptByAsymKey(AsymKey_ID('Asym_PassWord'),

[PassWord], N'secreT007!' ))

FROM UserLogin

WHERE LoginName = 'operator'

go

SELECT  LoginName,

PassWordDecrypted= convert(nvarchar(128),

DecryptByAsymKey(AsymKey_ID('Asym_PassWord'),

[PassWord], N'secreT007!' ))

FROM UserLogin

WHERE LoginName = 'administrator'

go

Related article:

Encrypt Password Field in SQL Server, Registry Information & Query String