Prevent Windows admin from querying a Database

Question

Prevent Windows admin from querying a Database

Yuckon

SSChasing Mays

Points: 645
More actions
March 16, 2015 at 1:48 pm

#398195

Hi
I was a DBA a long time ago and my knowledge is a bit jaded now,
We have a SQL database as part of an off the shelf software package and there is sensitive information inside stored as a BLOB in a field in a table.
Our applcation owners are freaked out about an IT administrator (I know) logging on to the server as administrator, connecting to the database using SSMS, running some simple t-sql code to deserialize the data in the blob field and read the data.
Is there any way to stop a Windows Admin from doing this in SQL server?
These blob tables should only ever be accessed by the application (which uses a service account to access the database) and not through SSMS.
Please help,
A long since retired DBA

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

You can ensure that the administrator doesn't have a login to SQL, that the Administrators group isn't added as a SQL login (shouldn't be), the domain admins group is not added as a SQL login, but that won't prevent someone who's determined. You cannot prevent someone who has local admin on the server from accessing SQL, you can make it hard (by removing their logins), but there's always a way around.

Maybe something like SQLAudit with regular reports to your information security. You can't stop them, but you can certainly track any attempts at access. In several places I've worked, trying to work around restrictions is a 'career-limiting move'

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Yuckon SSChasing Mays Points: 645 More actions · Answer 2

Exactly the answer I was looking for, thanks 🙂

Nice to see the same expert answering my questions all these years on!

Thank you Gila Monster

dan-572483 SSCoach Points: 15870 More actions · Answer 3

One way is to have data encryption/decryption at the application level. However this has it's disadvantages - namely limiting the viewing of the data to the application (no reporting apps) and the inability to use SQL indexes based on encrypted data.