Encrypted Columns

[Matt_F]

Is it possible to generate data for encrypted columns where the data has been encrypted using SQL Server symmetric keys?

For example, could realistic data be generated and then encrypted using the specified symmetric key for FirstName and Surname columns in a customer table.

I think this may be possible using a custom generator, however I can't see how I could specify the symmetric key to be used.

[Chris Auckland]

I might have misunderstood, but if you generate data for a encrypted column, then as long as the account that you use to generate the data has access to the key and certificate then the newly inserted data would be encrypted.

However, I haven't really played around with encrypted columns, so I don't know that this is definitely correct.

If you manually insert a row to you encrypted column, is it automatically encrypted, or does it only encrypt if you insert in a certain way?
_________________
Chris

[Matt_F]

Thanks for the reply Chris.

The data is only encrypted when inserted through a stored procedure which takes the incoming parameters, encrypts the data and then inserts.

[Chris Auckland]

Sorry for the delay.

You're right then; I think this could only be done through a custom generator.

You might be able to do it with a SQL Statement generator pulling the unencrypted data from another table and then inserting it thought the sp, but I haven't ever tried it myself.
_________________
Chris