SQL Column Encryption and .NET application
I have a general question about the effectiveness of encrypting column(s) in SQL.
So you have a column or more that you have encrypted during the creation of a table. Prior, of course, you create a Master and Private Column Encryption keys.
The encryption and decryption takes place in the .NET application that connects to the database. The Private Key created in the SQL database is exported and imported onto the machine running the .NET application.
My question is this ..
What prohibits someone, a developer, with this certificate from creating their own .NET application to access the data in the encrypted columns of the database, if they have access to that database; for instance a DBA. The whole idea is not only encrypt the data from unauthorized access; but to also keep DBA's from viewing it as well.
So you have a column or more that you have encrypted during the creation of a table. Prior, of course, you create a Master and Private Column Encryption keys.
The encryption and decryption takes place in the .NET application that connects to the database. The Private Key created in the SQL database is exported and imported onto the machine running the .NET application.
My question is this ..
What prohibits someone, a developer, with this certificate from creating their own .NET application to access the data in the encrypted columns of the database, if they have access to that database; for instance a DBA. The whole idea is not only encrypt the data from unauthorized access; but to also keep DBA's from viewing it as well.
Everything depends on the proper key management. This is not a feature for one man company.
If you have people which may use the application w/o any restrictions then they can access all data.
Somebody has to generate the keys and store then to predefined storage. Such person should not have rights to application, database, etc. so it should not be DBA.
The application must know where to read the key and connection strings. Developers do not have access to the production App/data etc. at all.
The application may use different encryption keys in development and test env. which does not contain sensitive data.
It is hard to support such application so you should encrypt just necessary data and also maintain test environment where you may simulate all bugs and allow DBAs and developers full access.
You may read about all this in documentation: https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/sql-server-and-database-encryption-keys-database-engine?view=sql-server-2017
If you have people which may use the application w/o any restrictions then they can access all data.
Somebody has to generate the keys and store then to predefined storage. Such person should not have rights to application, database, etc. so it should not be DBA.
The application must know where to read the key and connection strings. Developers do not have access to the production App/data etc. at all.
The application may use different encryption keys in development and test env. which does not contain sensitive data.
It is hard to support such application so you should encrypt just necessary data and also maintain test environment where you may simulate all bugs and allow DBAs and developers full access.
You may read about all this in documentation: https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/sql-server-and-database-encryption-keys-database-engine?view=sql-server-2017
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You are welcome.
BTW, you may express your thanks in a more obvious way, e.g. to select one or more answers as a solution. :-)
BTW, you may express your thanks in a more obvious way, e.g. to select one or more answers as a solution. :-)
If you are trying to keep the DBAs from viewing the decrypted data, then generating and managing the certificates/keys in the database is not the way to go. As you insinuate, anyone with access to the encrypted data as well as the private decryption key will be able to decrypt the data.
Another way to go about this is to use AES256 symmetric key generated in the .NET application and use that key to encrypt / decrypt the data when writing to the database. If your .NET application is the only application that is doing the reading/writing from the database that might be both more secure and more efficient in terms of processing speed.