My question is:
as sql 2005 built in function, is there any way to encrypt a text or varchar(max) fieild which could be more than 8,000 chars?
Thanks
duohong
It is not recommended to encrypt data directly using certificates; instead, it is recommend to use symmetric keys to encrypt data, and certificates can be used to encrypt those symmetric keys. One reason for this is that asymmetric key encryption is much slower than symmetric key encryption. Also, asymmetric key algorithms are particularly good for key management, but are not well suited for general data encryption, which is why asymmetric and symmetric keys are in practice used together, the first for allowing secure key exchange and the second for the actual data encryption. In SQL Server 2005, the main purpose of certificates is for signing modules and for encrypting other keys, not for directly encrypting data.
Also, note that certificates are currently generated with 1024 bit length private keys and the length of data that you can encrypt with such a certificate is at most 117 bytes (1024/8 - 11 for padding). This is less than the varchar limit of 8000 characters, but is sufficient for the intended use of certificates.
Thanks
Laurentiu
