Sql-server – Separation of Duties – Can a DBA patch or Upgrade a SQL Server 2008 R2 DB without TDE Encryption keys

Note that I am not a crypto systems or security expert. This is what I've seen done and it makes sense, but I cannot claim it doesn't have security issues beyond what I outline below. As always, get a proper security audit if it's important.

In the following, "credential" can refer to ssh keys, passwords, or whatever needs storing.

The typical model is to use a couple of levels of crypto.

• For each user, generate and store a public/private keypair

• Store the public part of the user's key in the clear in the user's record

• Store the private part of the user's key encrypted to the user's passphrase in the user's record.

• Encrypt each credential to a symmetric key that's unique to that credential

• Encrypt each credential's symmetric key to the public key of every user who should have access to that key, and store the resulting encrypted material in a user_credentials mapping table.

Essentially, you maintain a keyring, much like the gpg keyring where the public part is in cleartext and the private part is encrypted to a passphrase. You should make sure the passphrase selected by each user is strong.

Now you can generate a symmetric key for each encrypted credential. Encrypt this key to every user's public key and store it in a user-credentials join table. Never store this symmetric key unencrypted, and use a different symmetric key for each credential.

You land up with something like this

[user]
user_id
username
login_password_salt
login_password_hash
public_key_text
private_key_sym_encrypted_to_passphrase

[user_credential]
user_id
credential_id
credential_key_encrypted_to_user_public_key

[credential]
credential_id
credential_data_encrypted_to_credential_key

There are quite a few advantages to doing things this way:

• If a dump of your database is leaked your credentials are still safe, or as safe as your passphrases and key strengths.

• Since you have a different symmetric key for each credential you can control which users have access to which credentials

• Any user with access to a given credential can give another user access to it; they just decrypt it using their key and encrypt it to the other user's public key. You don't need to get everybody together to add credentials or add new users.

• Because you're encrypting each credential to a symmetric key, and then encrypting that key to the user's key, you can change the stored credential without having to re-encrypt it to each user's key. It just makes updating stored credentials more convenient.

However, if the database host is compromised, an attacker can easily extract the passwords any given user has access to as soon as that user logs in. They capture the user's passphrase by wrapping functions or by enabled detailed logging and searching the logs. Then they can decrypt passwords in a stolen dump or from the live DB. A compromised database host is no danger until a user logs in to use it but then all is lost.

This means that the host of the database is security critical. Leaking the dump isn't fatal to security, but someone being able to modify the code running on the DB is a fatal security breach.

In addition to using your credentials management DB to extract ssh key material, passwords, etc when you need it, you can also use it as an authentication proxy where it can auth against a service without your user having to ever be able to learn the credential used. For example, your app might decrypt an ssh key, add it to an ssh agent, and use the decypted key to log in to a server via the agent without the user ever being able to see and access the key directly.

Yes, it is normal. The certificates and keys are part of you database.

CREATE CERTIFICATE my_certificate
ENCRYPTION BY PASSWORD = 'blahblah' ...;

CREATE SYMMETRIC KEY my_symmetric_key
... ENCRYPTION BY CERTIFICATE my_certificate;

Your posted code creates a symmetric key encrypted with a certificate encrypted with a password. The master key is not involved in any way. This arrangement requires you to provide the certificate decryption password in the session. Your application must provide it, or your stored procedures do.