Hi,
I am working on an change where I need to encrypt the values being stored to a SQL Server DB. For that I have changed the column 'credit_score' in the DB to varbinary(max) and created certificate and symmetric key like this:
Code:
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'XXXXXXXX'
GO
CREATE CERTIFICATE customer_crypt_cert
WITH SUBJECT = 'Customer Personal Data Encryption';
GO
CREATE SYMMETRIC KEY customer_value_crypto_key
WITH ALGORITHM = AES_256
ENCRYPTION BY CERTIFICATE customer_crypt_cert;
GO
My c# mapping code looks like this:
Code:
base.Map(x => x.CreditScore).Formula(
"(open symmetric key cim_customer_value_crypto_key decryption by certificate cim_customer_crypt_cert; select encryptbykey(key_guid('cim_customer_value_crypto_key'),credit_score);)");
The mapping hbm files has this code:
Code:
<property name="CreditScore" type="System.String, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=XXXXXXXX">
<column name="credit_score" />
</property>
When I execute the operation, it fails with the message "could not execute batch command.[SQL: SQL not available]". I turned on the sql tracer and the Insert statement was issued, but the credit score value was not in encrypted form.
Where am I going wrong?
Sandeep
Login
FAQ
Search