Generating symmetric encryption key in Transact SQL and exposing to .NET

This is a fairly broad question but I would like to know if anyone has experience of doing this.

I need SQL Server 2008 to generate a symmetric encryption key for me and expose it to .NET for encryption and decryption. I would like to store the key temporarily in the DB for a definined period, let's say an hour, and then generate a new key.

Does this sound at all possible to anyone? It is very important that the encryption/decryption is done in the application layer and not in the DB.

I'm not too fussy about the encryption algorithm, but the stronger the better rea开发者_StackOverflow社区lly.

Any pointers etc. would really be appreciated.

Cheers, Twibbles the 2nd.

---

@Edit: My first assumption was wrong. SQL server allows to create symmetric keys - of course. But it does not allow the backup nor any other access to the keys data. If you are satisfied with - f.e. a master key, you may generate this and use a regular backup of it (encrypted or not).

You could export (backup) the key via TSQL. Generation and backup could be done via scheduled tasks (if you aren't on SQLEXPRESS).

On the .NET side you may read the saved files and use them with whatever tools you may have. Dont know, if the keys generated are compatible with the regular .NET en/decryption algorithms.

Regarding security: it can be made (more) secure if the keys are encryptedly stored from TSQL. For example, you may generate a certificate in TSQL, preshare it with the .NET layer and use it to transfer the symmetric encrypted key.

See: http://msdn.microsoft.com/en-us/library/ms188357.aspx