0

Here's my requirement:

  • A database where at least 1 column on at least 1 table is sensitive, and should be encrypted
  • DBAs and people accessing the database with SSMS should not be able to view the plaintext values of the columns
  • There will be a client asp.net application that will be able to retrieve and decrypt the data, and insert and update to the table(s)

So, if I enable always encrypted on the necessary columns, any normal query will retrieve the ciphertext for those columns. Fine.

The client application will have Column Encryption Setting=Enabled on the connection string, so will have the ability to encrypt and decrypt data.

But what's to stop someone connecting with SSMS and enabling the same column encryption setting? They could then select from the table and see the plaintext.

In short, how do I apply permissions so that only people in a specific AD group, and excluding DBAs, has permissions to access the encryption keys to decrypt the data?

Is it as simple as making sure that the IIS that the .net application is on is not on the same server, and creating the column master key certificate only on that IIS machine?

jarlh
  • 42,561
  • 8
  • 45
  • 63
the Ben B
  • 166
  • 8

1 Answers1

0

There are two keys that are created Column Master Key (CMK) and Column Encryption Key (CEK).

The column master key is only stored on the application side, while the column encryption key will be stored on SQL Server side.

The result is that once the client has the CMK, you can activate encryption by changing the connection string and the driver will then look for this CMK. Without it the data cannot be decrypted.

Asher
  • 348
  • 1
  • 3
  • 19