Encrypt Passwords Using T-SQL Functions

-- 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  DATETIME NOT NULL CONSTRAINT DF_UserLogin_CreateDate DEFAULT (getdate()),

  ModifyDate  DATETIME NOT NULL CONSTRAINT DF_UserLogin_ModifyDate DEFAULT (getdate()),

  ModifiedBy  CHAR(6)    NOT NULL CONSTRAINT DF_UserLogin_ModifiedBy DEFAULT ('system'))

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

-- Cleanup

DROP TABLE dbo.UserLogin