关于SQL Server 2005内置加密方法接受输入参数的最大长度

之前发表关于关于SQL Server 2005内置加密方法的链接是http://blog.csdn.net/huangya0/archive/2008/06/13/2543900.aspx , 但发现它的长度有限制,太长会不成功的加密,我发了email转给我的客户再转去问MS, 相关人员还没有给一个正确的答复.

下边我把我的测试代码贴下来:

--Create a table for testCREATE TABLE [dbo].[SourceStringTable]([ID] [int] NOT NULL,[VarcharEngString] [varchar](max) NULL,[NVarcharEngString] [nvarchar](max) NULL,[NVarcharChnString] [nvarchar](max) NULL, CONSTRAINT [PK_SourceStringTable] PRIMARY KEY CLUSTERED ([ID] ASC)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]) ON [PRIMARY]GO-----------------------------------------------------------------------delete from SourceStringTable--接受加密的英文字符串(varchar)最大长度是117--接受加密的英文字符串(nvarchar)最大长度是58--接受加密的中文字符串(nvarchar)最大长度是58(116 bytes)declare @VarcharEngString varchar(max)declare @NVarcharEngString nvarchar(max)declare @NVarcharChnString nvarchar(max)--/*Below three string all can encrypt and decrypt correctly*/--length: 117set @VarcharEngString='Based on NCSA Mosaic. NCSA Mosaic(TM); was developed at the National Center for Supercomputing Applications at the Un'--length: 58set @NVarcharEngString='Based on NCSA Mosaic. NCSA Mosaic(TM); was developed at th'--length: 58set @NVarcharChnString='改进产品以满足用户不断增长的需求，所以不应将这些信息视为腾腾讯公司的承诺，另一方面，腾讯公司也不能保证在发布日期之后'--/*******************/insert into SourceStringTable(ID, VarcharEngString, NVarcharEngString, NVarcharChnString) values(1, @VarcharEngString, @NVarcharEngString, @NVarcharChnString)--/*Below three string all can not encrypt and decrypt correctly*/--length: 118set @VarcharEngString='Based on NCSA Mosaic. NCSA Mosaic(TM); was developed at the National Center for Supercomputing Applications at the Uni'--length: 59set @NVarcharEngString='Based on NCSA Mosaic. NCSA Mosaic(TM); was developed at thi'--length: 59set @NVarcharChnString='改进产品以满足用户不断增长的需求，所以不应将这些信息视为腾腾讯公司的承诺，另一方面，腾讯公司也不能保证在发布日期之后提'--/*******************/insert into SourceStringTable(ID, VarcharEngString, NVarcharEngString, NVarcharChnString) values(2, @VarcharEngString, @NVarcharEngString, @NVarcharChnString)select * from SourceStringTable-----------------------------------------------------------------------CREATE TABLE [dbo].[Customer]([CustomerID] [int] NULL,[Name] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,[City] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,[CreditCardType] [varbinary](max) NULL,[CreditCardNumber] [varbinary](max) NULL,[Notes] [varbinary](max) NULL,[Descr] [varchar](max) COLLATE Chinese_PRC_CI_AS NULL) ON [PRIMARY]GO--Create a DB master keyCREATE MASTER KEY ENCRYPTION BY PASSWORD = 'abcd1234';GO--Create a Certificate CREATE CERTIFICATE User2Certificate AUTHORIZATION ODMSDB_User_Name  ---this parameter should change to your DB user name    WITH SUBJECT = 'DB testing',START_DATE = '10/31/2007',EXPIRY_DATE = '10/31/2017';GO--delete all data in this tabledelete from Customer-------------CustomerID=12. this row CAN decrypt correctly----------------------------------------------------declare @VarcharEngString varchar(max)declare @NVarcharEngString nvarchar(max)declare @NVarcharChnString nvarchar(max)select @VarcharEngString=VarcharEngString,@NVarcharEngString=NVarcharEngString,@NVarcharChnString=NVarcharChnString from SourceStringTable where ID=1--print @VarcharEngString--print @NVarcharEngString--print @NVarcharChnStringINSERT INTO CustomerVALUES (12, 'John Doe', 'Fairbanks',EncryptByCert(Cert_ID('User2Certificate'), @VarcharEngString),EncryptByCert(Cert_ID('User2Certificate'), @NVarcharEngString),EncryptByCert(Cert_ID('User2Certificate'), @NVarcharChnString),'Description for test')select * from Customer where CustomerID=12SELECT CustomerID, Name, City,CONVERT(VARCHAR(max), DecryptByCert(Cert_ID('User2Certificate'), CreditCardType) ) as CreditCardType,CONVERT(NVARCHAR(max), DecryptByCert(Cert_ID('User2Certificate'), CreditCardNumber)) as CreditCardNumber,CONVERT(NVARCHAR(max), DecryptByCert(Cert_ID('User2Certificate'), Notes) ) as Notes,DescrFROM Customer WHERE CustomerID = 12-------------CustomerID=13, this row CAN NOT decrypt correctly----------------------------------------------------declare @VarcharEngString2 varchar(max)declare @NVarcharEngString2 nvarchar(max)declare @NVarcharChnString2 nvarchar(max)select @VarcharEngString2=VarcharEngString,@NVarcharEngString2=NVarcharEngString,@NVarcharChnString2=NVarcharChnString from SourceStringTable where ID=2INSERT INTO CustomerVALUES (13, 'John Doe', 'Fairbanks',EncryptByCert(Cert_ID('User2Certificate'), @VarcharEngString2),EncryptByCert(Cert_ID('User2Certificate'), @NVarcharEngString2),EncryptByCert(Cert_ID('User2Certificate'), @NVarcharChnString2),'Description for test')SELECT CustomerID, Name, City,CONVERT(VARCHAR(max), DecryptByCert(Cert_ID('User2Certificate'), CreditCardType) ) as CreditCardType,CONVERT(NVARCHAR(max), DecryptByCert(Cert_ID('User2Certificate'), CreditCardNumber)) as CreditCardNumber,CONVERT(NVARCHAR(max), DecryptByCert(Cert_ID('User2Certificate'), Notes) ) as Notes,DescrFROM Customer WHERE CustomerID = 13select * from Customer where CustomerID=13--my question is: Each input parameter of the method EncryptByCert's maximal length when input value under the data type of varchar/nvarchar(english or chinese string)

客户转发回来咨询MS的内容如下:

I just confirmed that the max length of EncryptByCert(…,str) parameter str is 8000 bytes, which is the max length for a varchar/char/nvarchar/nchar data type. I understand that we can specify “max” such as “varchar(max)” so that the varchar data type can have length of 2^31-1 bytes, but in EncryptBycert() function the max length is 8000. I am sorry I did not find any public document that clearly state this.
 
By the way the return value of this function also has a  maximum size of 8,000 bytes.
 但跟我实际测试的结果不相符,真YM
我在网上根本找不到与之相关的问题和知识,google或百度搜到的相关关键字只有几个