Decryption within an application

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."Chuck Reif" <creif@.nomail.metopera.org> wrote in message
news:uvmlanAmHHA.4852@.TK2MSFTNGP03.phx.gbl...
>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).|||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@.xyz.com> wrote in message
news:uH0ftcPmHHA.4624@.TK2MSFTNGP04.phx.gbl...
> "Chuck Reif" <creif@.nomail.metopera.org> wrote in message
> news:uvmlanAmHHA.4852@.TK2MSFTNGP03.phx.gbl...
> 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).
>|||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:
[url]http://www.sqlservercentral.com/columnists/mcoles/sql2005symmetricencryption.asp[/
url]
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@.nomail.metopera.org> wrote in message
news:%23GeqmRWmHHA.596@.TK2MSFTNGP06.phx.gbl...
> 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@.xyz.com> wrote in message
> news:uH0ftcPmHHA.4624@.TK2MSFTNGP04.phx.gbl...
>|||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@.xyz.com> wrote in message
news:Od0T8%23amHHA.4772@.TK2MSFTNGP05.phx.gbl...
> 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/col...ion.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@.nomail.metopera.org> wrote in message
> news:%23GeqmRWmHHA.596@.TK2MSFTNGP06.phx.gbl...
>|||"Chuck Reif" <creif@.nomail.metopera.org> wrote in message
news:%23C1qbs$mHHA.668@.TK2MSFTNGP05.phx.gbl...
> 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.
Anyone who has the username and password used by the application to log into
the database would have the ability to decrypt the encrypted data.
Alternatively, if a password is stored in the application, anyone with a hex
editor could decrypt the encrypted data outside of the application. The
only way I can think of to force decryption only through the application
would be to encrypt only in the application. But then you take on the
responsibility of encryption key management yourself. I don't know of any
magic bullet to ensure that data encrypted using SQL Server can only be
accessed via a specific front-end application interface.

> That is why I took the approach of having the application open the key.
> Sort of like the old application-role security.
Anyone with a hex editor could conceivably locate a password stored in an
application and use it to decrypt data. Linking it to a specific Windows
login puts the burden of encryption key management back on the operating
system. That's basically the main difference; do you want to manage your
own passwords, or do you want to let Windows and SQL Server manage your
passwords?

> But I would love to find a better way.
Biometrics?