While some good information has been provided in previous answer, there is a critical design flaw, which is not really noticed - but comes from some of the implicit assumptions in the question.
The difference should not be between:
- Web app and DB on same server
- Web app and DB on different servers
- application vs. db encryption
The design should start from:
- Who needs access to the encryption / decryption key(s) ?
Or, actually even more correct:
- Who is responsible for protecting the confidentiality of the data ?
Which, in turn, comes from:
- What threats are you trying to protect against?
- Who are you trying to prevent from reading your data?
Only based on these questions, can you design a properly engineered cryptosystem. (And prevent the crypto-fairy dust that @D.W. referred to).
So, some example scenarios:
- The user should be responsible for encryption, preventing even the app admins from seeing the data
- The client application should be responsible, without having to involve the user - but this still needs to be done client-side (e.g. a backup program).
- The appserver applicatively and specifically encrypts selected data, as part of the business logic (even the dba cannot see the data)
- The database server should automatically and transparently encrypt stored data. (This protects only against theft of the db file / physical disk, with caveats...).
For simplicity, let's assume that encryption should be done server side, responsibility lies on the server, and the end-user (and administrator) has no influence on the encryption.
The main question, then, is the encryption performed by the application server, or the database server? (Actually not so simple, there are other options...)
This, as most security issues do, comes back to a tradeoff:
- Will you manage your own keys, or do you prefer the infrastructure abstract this automatically? (the original question... more info below...)
- Are you worried about malicious (or
incompetent inexperienced) programmers?
- Are you worried about malicious admins?
- Are you worried about unauthorized application usage?
- Are you worried about unauthorized SQL access?
- Are you worried about unauthorized direct file access?
- Are you worried about physical disk / server theft?
And so on. (Note that some of the above questions are irrelevant, and cannot be solved by server encryption).
Now, let's assume you made your decision, based on the above, between appserver encryption and database encryption.
Note that whether the web server and the db are on the same server or not, has almost no effect here - if the app is encrypting, then it's just sending "special" data to the db, otherwise it's moot. However, this does affect the above threats discussion - some issues are obsoleted by the design, some are enhanced...
- Web / Application server encryption
- The encryption key (EK) you use to encrypt the data, should be itself encrypted by a key encrypting key (KEK).
- The encrypted EK should be stored in a protected place - this can be a file, a registry key, whatever - the important thing is restricting access to it, to the appserver's user only (and, perhaps the admin - see below).
- The KEK also needs protection - this is a bit trickier.
If you're on Windows, you have easy access to DPAPI - this provides OS-level key management, so you can have the OS encrypt your KEK and transparently manage the keys for you. (Behind the scenes, DPAPI eventually relies on knowing the user's password (in USER_MODE), so that is the final, weak point of an actual known secret - but noone else should know the server's password in the first place, right?)
Another option is external device / HSM, discussed further below.
- The KEK also needs access control, of course - restrictive ACLs and all that.
- There are several reasons you want to separate the EK from the KEK: you don't want to encrypt huge amounts of data with a single key; if you need to replace the key, it becomes a lot easier (just re-encrypt the EK with the new KEK); you can use stronger, slower, more complex encryption/storage on the KEK; if you need to be PCI compliant, you can implement split-knowledge on the KEK; and more.
- Both the EK and the KEK should not be stored long-term unencrypted - depending on your platform/language, this means e.g. not storing it in a String object in .NET or Java. Instead, store it in mutable byte arrays, and always ensure you dispose of it as soon as possible.
- Some frameworks even go a step further, and provide specific protected objects. E.g. in .NET you can use the System.Security.Cryptography.ProtectedMemory and/or System.Security.Cryptography.ProtectedData classes to keep the keys protected, even in memory.
- Database encryption
- If you have your database server perform the encryption, this becomes a lot easier: the tables / fields are configured to automatically encrypt any data stored in it, key management is completely transparent (from the PoV of the application, not the DBA), and so on.
- For example, Oracle and MS SQL Server both support automatic built-in encryption. For MSSQL, the key management is complex, yet similar to what I described above. Simplified: There is an EK, stored in the database, that the dba can configure to use to encrypt/decrypt tables or fields. This is in a protected table, encrypted by a KEK, in turn encrypted by a server level KEK, in turn encrypted using DPAPI on the MSSQL's service account.
- Alternatively, MSSQL also supports using asynchronous encryption, e.g. storing public/private key pairs - this can be useful in specific scenarios.
- Third option: external encryption storage, or HSM (hardware security module). This can be a shared hardware appliance, server, or a hardware card or dongle... etc.
- These devices are designed to very securely protect small bits of data - e.g., your KEK.
Again, another reason why to separate the EK from the KEK - this will enable you to efficiently encrypt and decrypt the masses of data with EK, while still securely managing the KEK.
- The HSM is usually accessed via a special driver or API, it may be possible to plug these into the database encryption too (depending on product, etc).