|
Execute the following
Microsoft SQL Server T-SQL script 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.
-- SQL Server password encryption with assymetric key
USE AdventureWorks2008;
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 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
------------
Related articles:
How to: Encrypt a Column of Data
SQL Server 2005 Encryption – Encryption and data length limitations
|
