SQL SERVER 2005 加密解密数据

　　应用SQL Server 2005的数据加密功能实现对敏感数据的保护。

对称加密

--======================================================================
/*
Create Symmetric Encryption by password
*/
--======================================================================

DECLARE @PSW1 NVARCHAR(400),
@PSW2 NVARCHAR(400)

SET @PSW1 = 'TestKeyPsw';
SET @PSW2 = 'TestKeyPswNew'

--Create Symmetric
/*
CREATE SYMMETRIC KEY Symmetric_Key_Test
--AUTHORIZATION User2
WITH ALGORITHM = RC4 --option: DES | TRIPLE_DES | TRIPLE_DES_3KEY | RC2 | RC4 | RC4_128 | DESX | AES_128 | AES_192 | AES_256
ENCRYPTION BY PASSWORD = 'TestKeyPsw'
*/

--modify Symmetric Encryption password. In actually, it can only add or drop, if you want to modify, just add a new and drop the old one
/*
EXEC ('Open Symmetric Key Symmetric_key_Test DECRYPTION by Password=N'''+ @PSW1 +''';')
ALTER SYMMETRIC KEY Symmetric_key_Test 
ADD ENCRYPTION BY PASSWORD = N'TestKeyPswNew';
-- DROP ENCRYPTION BY PASSWORD = N'TestKeyPsw';
*/

--DROP Symmetric Key
/*
DROP SYMMETRIC KEY Symmetric_Key_Test
*/

--EXEC ('Open Symmetric Key Symmetric_key_Test DECRYPTION by Password=N''TestKeyPsw'';')
--SELECT * FROM sys.openkeys

/*
-- create a temptable to test
CREATE TABLE #tempTable
(
desction VARBINARY(4000)
)
TRUNCATE TABLE #tempTable

INSERT INTO #tempTable
select encryptbykey(key_guid('Symmetric_key_Test'), CONVERT(NVARCHAR(MAX),'123456'))
*/

EXEC ('Open Symmetric Key Symmetric_key_Test DECRYPTION by Password=N'''+ @PSW1 +''';')
SELECT desction,CONVERT(nvarchar(max), DecryptByKey(desction))AS DecryptByKeyDesction FROM #tempTable

--------------------------------------------------------------------------------------------------------

 

非对称加密

--======================================================================
/*
Create Asymmetric Encryption by password
*/
--======================================================================
--Create Asymmetric
/*
CREATE ASYMMETRIC KEY Asymmertric_key_Test
    WITH ALGORITHM = RSA_2048  --option: RSA_512 | RSA_1024 | RSA_2048
    ENCRYPTION BY PASSWORD = N'TestKeyPsw';  
*/

--modify Asymmetric  password
/*
ALTER ASYMMETRIC KEY Asymmertric_key_Test
    WITH PRIVATE KEY (
    DECRYPTION BY PASSWORD = 'TestKeyPsw', --old password
    ENCRYPTION BY PASSWORD = 'TestKeyPswNew'); --new password
*/

--DROP Asymmetric Key
/*
DROP ASYMMETRIC KEY Asymmertric_key_Test
*/

--SELECT * FROM sys.asymmetric_keys_Test

/*
-- create a temptable to test
CREATE TABLE #tempTable
(
desction VARBINARY(4000)
)
TRUNCATE TABLE #tempTable

INSERT INTO #tempTable VALUES (EncryptByAsymKey(AsymKey_ID( 'Asymmertric_key_Test'), N'123456'))
*/

SELECT desction,CONVERT(NVARCHAR(1000),DecryptByAsymKey( AsymKey_Id( 'Asymmertric_key_Test'), desction, N'TestKeyPsw')) as DecryptDesction
FROM #tempTable