SQL Server 2005
--由证书加密对称密钥
CREATE SYMMETRIC KEY SymmetricByCert
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE CertTest;
--测试数据
CREATE TABLE t_A  
(ID int,name VARCHAR(200),pwd VARCHAR(200))  
INSERT INTO t_A  
SELECT 1,'aa','asd123'  
UNION ALL  
SELECT 2,'bb','123wer' 
UNION ALL  
SELECT 3,'cc','ryrty' 
UNION ALL  
SELECT 4,'dd','sdfsd' 
CREATE TABLE t_B  
(ID int,name VARCHAR(200),pwd VARCHAR(200))
--打开之前创建的由证书加密的对称密钥
OPEN SYMMETRIC KEY SymmetricByCert
DECRYPTION BY CERTIFICATE CertTest
--利用这个密钥加密数据并插入新建的表
insert t_B(
ID,
name, 
pwd

select
ID,
name, 
pwd =EncryptByKey(KEY_GUID('SymmetricByCert'), pwd)
from t_Aselect * from t_BOPEN SYMMETRIC KEY SymmetricByCert
DECRYPTION BY CERTIFICATE CertTest
select
ID,
name, 
pwd = convert(varchar(200), DecryptByKey(pwd))
from t_B drop table t_A
drop table t_B每次运行的结果都不一样,结果为何会出现好多种。---------------------------------------------------------------------------------------------------------------------------------------------------------------------ps.具体可参照http://blog.csdn.net/yole_grise/article/details/14226773
sql server 2005加密对称密钥

解决方案 »

  1.   


    你看是这样不,你把t_B表的字段pwd的字段类型改为varbinary(200),就可以了,这时效果:
    --由证书加密对称密钥
    CREATE SYMMETRIC KEY SymmetricByCert
        WITH ALGORITHM = AES_256
        ENCRYPTION BY CERTIFICATE CertTest;
    --测试数据
    CREATE TABLE t_A  
    (ID int,name VARCHAR(200),pwd VARCHAR(200))  INSERT INTO t_A  
    SELECT 1,'aa','asd123'  
    UNION ALL  
    SELECT 2,'bb','123wer' 
    UNION ALL  
    SELECT 3,'cc','ryrty' 
    UNION ALL  
    SELECT 4,'dd','sdfsd' CREATE TABLE t_B  
    (
    ID int,name VARCHAR(200),
    pwd varbinary(200)  --必须是varbinary类型才可以的,因为加密后是2进制的数据
    )
    --打开之前创建的由证书加密的对称密钥
    OPEN SYMMETRIC KEY SymmetricByCert
    DECRYPTION BY CERTIFICATE CertTest
    --利用这个密钥加密数据并插入新建的表
    insert t_B(
    ID,
    name, 
    pwd

    select
    ID,
    name, 
    pwd =EncryptByKey(KEY_GUID('SymmetricByCert'), pwd)
    from t_Aselect * from t_B
    /*
    ID name pwd
    1 aa 0x002B0AE13562624CA394BF2AFE093EBC01000000B0E19D45B958E79FCEABFF76E195FB465590B68ACB8554852D1F39C2F91FBC25
    2 bb 0x002B0AE13562624CA394BF2AFE093EBC0100000041D7A459EF5A335BB0F98F0D073FAF903F28C0BD80E3EC8443C87712E3D530FB
    3 cc 0x002B0AE13562624CA394BF2AFE093EBC0100000088A2A0442E021C36FC3099F69C0013C0EDF5113472381436233B7B01D57316C1
    4 dd 0x002B0AE13562624CA394BF2AFE093EBC01000000666BD66A4A090E6EFCD487BDF7DD7918B05A6D5206A02D4249C4E6F604259F3A
    */OPEN SYMMETRIC KEY SymmetricByCert
    DECRYPTION BY CERTIFICATE CertTest
    select
    ID,
    name, 
    pwd = convert(varchar(200), DecryptByKey(pwd))
    from t_B 
    /*
    ID name pwd
    1 aa asd123
    2 bb 123wer
    3 cc ryrty
    4 dd sdfsd
    */drop table t_A
    drop table t_B
      

  2.   


    这样可以~
    要是我想实现t_B中pwd不用解密密钥查上去是空的,有什么好办法么?
      

  3.   


    你看是这样不,你把t_B表的字段pwd的字段类型改为varbinary(200),就可以了,这时效果:
    --由证书加密对称密钥
    CREATE SYMMETRIC KEY SymmetricByCert
        WITH ALGORITHM = AES_256
        ENCRYPTION BY CERTIFICATE CertTest;
    --测试数据
    CREATE TABLE t_A  
    (ID int,name VARCHAR(200),pwd VARCHAR(200))  INSERT INTO t_A  
    SELECT 1,'aa','asd123'  
    UNION ALL  
    SELECT 2,'bb','123wer' 
    UNION ALL  
    SELECT 3,'cc','ryrty' 
    UNION ALL  
    SELECT 4,'dd','sdfsd' CREATE TABLE t_B  
    (
    ID int,name VARCHAR(200),
    pwd varbinary(200)  --必须是varbinary类型才可以的,因为加密后是2进制的数据
    )
    --打开之前创建的由证书加密的对称密钥
    OPEN SYMMETRIC KEY SymmetricByCert
    DECRYPTION BY CERTIFICATE CertTest
    --利用这个密钥加密数据并插入新建的表
    insert t_B(
    ID,
    name, 
    pwd

    select
    ID,
    name, 
    pwd =EncryptByKey(KEY_GUID('SymmetricByCert'), pwd)
    from t_Aselect * from t_B
    /*
    ID name pwd
    1 aa 0x002B0AE13562624CA394BF2AFE093EBC01000000B0E19D45B958E79FCEABFF76E195FB465590B68ACB8554852D1F39C2F91FBC25
    2 bb 0x002B0AE13562624CA394BF2AFE093EBC0100000041D7A459EF5A335BB0F98F0D073FAF903F28C0BD80E3EC8443C87712E3D530FB
    3 cc 0x002B0AE13562624CA394BF2AFE093EBC0100000088A2A0442E021C36FC3099F69C0013C0EDF5113472381436233B7B01D57316C1
    4 dd 0x002B0AE13562624CA394BF2AFE093EBC01000000666BD66A4A090E6EFCD487BDF7DD7918B05A6D5206A02D4249C4E6F604259F3A
    */OPEN SYMMETRIC KEY SymmetricByCert
    DECRYPTION BY CERTIFICATE CertTest
    select
    ID,
    name, 
    pwd = convert(varchar(200), DecryptByKey(pwd))
    from t_B 
    /*
    ID name pwd
    1 aa asd123
    2 bb 123wer
    3 cc ryrty
    4 dd sdfsd
    */drop table t_A
    drop table t_B
    这样可以~
    要是我想实现t_B中pwd不用解密密钥查上去是空的,有什么好办法么?
      

  4.   


    你看是这样不,你把t_B表的字段pwd的字段类型改为varbinary(200),就可以了,这时效果:
    --由证书加密对称密钥
    CREATE SYMMETRIC KEY SymmetricByCert
        WITH ALGORITHM = AES_256
        ENCRYPTION BY CERTIFICATE CertTest;
    --测试数据
    CREATE TABLE t_A  
    (ID int,name VARCHAR(200),pwd VARCHAR(200))  INSERT INTO t_A  
    SELECT 1,'aa','asd123'  
    UNION ALL  
    SELECT 2,'bb','123wer' 
    UNION ALL  
    SELECT 3,'cc','ryrty' 
    UNION ALL  
    SELECT 4,'dd','sdfsd' CREATE TABLE t_B  
    (
    ID int,name VARCHAR(200),
    pwd varbinary(200)  --必须是varbinary类型才可以的,因为加密后是2进制的数据
    )
    --打开之前创建的由证书加密的对称密钥
    OPEN SYMMETRIC KEY SymmetricByCert
    DECRYPTION BY CERTIFICATE CertTest
    --利用这个密钥加密数据并插入新建的表
    insert t_B(
    ID,
    name, 
    pwd

    select
    ID,
    name, 
    pwd =EncryptByKey(KEY_GUID('SymmetricByCert'), pwd)
    from t_Aselect * from t_B
    /*
    ID name pwd
    1 aa 0x002B0AE13562624CA394BF2AFE093EBC01000000B0E19D45B958E79FCEABFF76E195FB465590B68ACB8554852D1F39C2F91FBC25
    2 bb 0x002B0AE13562624CA394BF2AFE093EBC0100000041D7A459EF5A335BB0F98F0D073FAF903F28C0BD80E3EC8443C87712E3D530FB
    3 cc 0x002B0AE13562624CA394BF2AFE093EBC0100000088A2A0442E021C36FC3099F69C0013C0EDF5113472381436233B7B01D57316C1
    4 dd 0x002B0AE13562624CA394BF2AFE093EBC01000000666BD66A4A090E6EFCD487BDF7DD7918B05A6D5206A02D4249C4E6F604259F3A
    */OPEN SYMMETRIC KEY SymmetricByCert
    DECRYPTION BY CERTIFICATE CertTest
    select
    ID,
    name, 
    pwd = convert(varchar(200), DecryptByKey(pwd))
    from t_B 
    /*
    ID name pwd
    1 aa asd123
    2 bb 123wer
    3 cc ryrty
    4 dd sdfsd
    */drop table t_A
    drop table t_B
    这样可以~
    要是我想实现t_B中pwd不用解密密钥查上去是空的,有什么好办法么?这个没什么好办法,你想,你把数据加密了,然后把密文,放到了列里面,到时候你需要从把这些列读取密文,然后解密。如果你的这个加密的字段里是一堆的NULL,或者空串,那么解密的时候,就没办法解密了。