How to encrypt numeric column in Sql Server 2008?

How to encrypt numeric column in Sql Server 2008?

I want to encrypt the employee's salary column in Sql Server 2008.

The column type is numeric or money.

Is there any encryption method that can support the following requirements?

1. Is there any sql encryption user defined function to encrypt column? The type of Encrypted result is string will be ok.
2. Is sql server support this type of encryption by itself?
3. Is there a simple enc开发者_运维知识库ryption method that can encrypt numbers such as 123.56 to 362.15?

I use C# and Sql Server 2008.

You can't encrypt a numeric data type. What you will have to do is convert the column to a VarChar column, then you can store the number in an encrypted format with Base64. A VarChar column of 1000 will be more then enough.

You could also store it in a Binary field without having to do a Base64 encoding.

Here's an example with the MONEY datatype:

DECLARE @actualAmount MONEY;
DECLARE @encryptedAmount VARBINARY(128);

-- Encrypt value
SET @actualAmount = $12345.67;
SET @encryptedAmount = ENCRYPTBYKEY(KEY_GUID('MyEncryptionKeyName'), CONVERT(VARCHAR(30), @actualAmount));
PRINT @actualAmount;
PRINT @encryptedAmount;

-- Decrypt value
DECLARE @unencryptedActualAmount MONEY;
SET @unencryptedActualAmount = CONVERT(MONEY, CONVERT(VARCHAR(30), DECRYPTBYKEY(@encryptedAmount)));
PRINT @unencryptedActualAmount;

No encryption function will have an output type of numeric or money, ever. Encrypted values are always byte arrays (sql binary data type), or some representation of a byte array (such as a base64-encoded character string).