Confidential data in SQL server - keep admin from viewing data

Question

0.00/5 (No votes)

See more:

My client is a consultant to a professional sports league on matters that are confidential to the players. He now wants to move a SQL Server database that we've maintained locally to a server controlled by the league. We've been told that, once it's on their server, our only access will be via web services or, when we want to change the schema or the stored procedures, via change scripts we submit to them.

I suppose we could live with that, except my client is adamant that the league not be able to read the data, and I have no idea if that's possible. My web searches have proven fruitless.

Can we set it up that way? What search terms would I need to google?

Thanks

What I have tried:

a variety of google searches. Looked briefly at SQL Server encryption, but what I read was confusing

Posted 13-Sep-16 10:18am

Duke Carey

Updated 13-Sep-16 17:41pm

Sean Ewington

Add a Solution

Comments

an0ther1 13-Sep-16 18:10pm

Hi Duke,
I am sure someone on here is much more knowledgeable than me but my initial thoughts are.
Based on the premise you have described database encryption will not work for you. This is because the Server Administrators will always be able to access the Database keys. - refer MSDN link; MSDN - SQL Server and Database Encryption Keys
it would be possible to encrypt and decrypt the data within whatever application you are using to read & write to the DB but this may not be practical. If you are using a web interface the web server would need to encrypt and decrypt every string you sent/received from the server. Additionally you would need to ensure you have enough storage space - Using a hash function to encrypt passwords in C# increases the amount of space required dramatically (8 characters turns into 88).
I would suggest a compromise. Encrypt some of the data - the things that are personal and can be used to identify the individuals/team - but with enough data this is sort of doomed to failure.
If I was given a database that was partially encrypted but I knew what the content was about & some of the data was public I would soon identify the individuals.
You could try something like a offline master list of the identification data - not storing it in the database & simply using a unique identifier within the remote DB. Then some encryption of sensitive data.

Kind Regards

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Answer 1 · 2016-09-13T17:41:00

One way is to use Always Encrypted feature of the database engine. This way only client application is aware of the key to decrypt the data. Have a look at Always Encrypted (Database Engine)[^] and from a larger point of view: Data security, SQL Server 2016, and your business | SQL Server Blog[^].

As an0ther1 pointed out, it may not be practical to encrypt everything, just the data that really needs to be protected.

Another aspect which should be implemented is auditing, to be able to see who does (or has tried) what. In a secure environment it's important to try to protect the data as well as possible but also to gather information about potential misuses. Have a look at SQL Server Audit (Database Engine)[^]