Re: Decryption within an application

Thanks so much for your help, but I must be dense. If I grant permission to
the users or use automatic key management, then it seems to me that the data
can be encrypted outside of my application by a non-sa user. If I only want
the application to display the unencrypted data, I can't see how this
automatic approach works.

That is why I took the approach of having the application open the key.
Sort of like the old application-role security.

But I would love to find a better way.


"Mike C#" <xyz@xxxxxxx> wrote in message
news:Od0T8%23amHHA.4772@xxxxxxxxxxxxxxxxxxxxxxx
With automatic key management you should be able to connect to the
application and open the symmetric keys without a password. You can use
GRANT to grant permissions to users on your keys, certificates, etc.
Here's an article with some samples that demonstrate encryption/decryption
without passwords, thanks to automatic key management:
http://www.sqlservercentral.com/columnists/mcoles/sql2005symmetricencryption.asp

You might also want to look into the DecryptByKeyAutoAsymKey and
DecryptByKeyAutoCert functions that combine the DecryptBy... functions
with OPEN SYMMETRIC KEY automatically.

"Chuck Reif" <creif@xxxxxxxxxxxxxxxxxxx> wrote in message
news:%23GeqmRWmHHA.596@xxxxxxxxxxxxxxxxxxxxxxx
I'm not neccesarily opposed to the sysadmin being able to decrypt the
data, but I don't want any other user outside of the application to have
access to the data. So what I can't figure out (even with SS key
management) is how to open the key only when connecting from the
application, unless I compile a password into the code.

Any thoughts on that would be helpful.

Thanks.

"Mike C#" <xyz@xxxxxxx> wrote in message
news:uH0ftcPmHHA.4624@xxxxxxxxxxxxxxxxxxxxxxx

"Chuck Reif" <creif@xxxxxxxxxxxxxxxxxxx> wrote in message
news:uvmlanAmHHA.4852@xxxxxxxxxxxxxxxxxxxxxxx
I need to encrypt one column of data in a single table and I pretty much
have all the operations figured out, including maintaining both the
encrpyted data and a one way hash for searches. I have a view which
decrypts the data properly when the symmetric key has been opened (and
obviously returns null when the key is not open).

I want the view to return the decrypted data only when the user is
accessing the database from a single application. This application
maintains a single database connection per session. My thought was to
open the key when the database connection is established by the
application and close it when the application exits, thereby granting
access only through the application. Is that an acceptable practice?

If I do that, should I protect the key with a password that is then
compiled in the application so that I can open the key? This means
that every installation will have a key protected by the same password.
Or is there a better way to do that?

Thanks for any help.

Well, when the key is opened it's specific to a session. So you could
have several sessions opening up the same key simultaneously and I
wouldn't think you'd encounter any problems. Of course you will
probably want to do some thorough testing to be sure, and also make sure
you don't take a performance hit there. I wouldn't recommend storing
the key hard-coded in your application. How about using the Automatic
Key Management feature of SQL 2005? The only real downside to it is
that all sysadmins can then decrypt your data (if that's a concern for
you - it is for some folks).







.

Relevant Pages

  • Re: Decryption within an application
    ... With automatic key management you should be able to connect to the ... You might also want to look into the DecryptByKeyAutoAsymKey and ... OPEN SYMMETRIC KEY automatically. ...
    (microsoft.public.sqlserver.security)
  • Re: Damn you, FEDEX! or Nikon D40 lost in Springfield, MO blackhole.
    ... the 2 mp Mavica he had been using with a Nikon D40. ... After shopping around, he got me to order one for him. ... The shipper had it insured, but from what I have read it could take weeks to sort this crap out. ... You may get your insurance from FedEx and a couple weeks later they find it and deliver it. ...
    (alt.photography)
  • Re: The Sci-Fi Rejection Letter That Time Forgot
    ... nations have stockpiled arsenals of these incredible bombs and the time the story is set. ...
    (rec.arts.sf.written)
  • RE: copied music cds have a skip in last 18 seconds
    ... If installing all missing Windows Updates doesn't fix your problem ... xiowan.......in tucson ...
    (microsoft.public.windows.mediacenter)