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.
John RugoSenior SQL DBAAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hi John,

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.
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
John RugoSenior SQL DBAAuthor Commented:
Thanks very much for the clarification.  That is exactly what I was thinking too. It almost doesn't make sense to use column encryption.
have a great day.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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. :-)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.