Preventing sa user from tampering with data

Question

I want to make sure that nobody, including the sa user, can tamper with my application's data, or even read it for that matter. Is this possible? For example, if the data is encrypted using SQL Server's facilities, can the sa user decrypt it, just because he is the sa user? I would rather achieve it without encryption, but if that is the only way, so be it.

Answer 1

Hello,

The "Sa" account do have admin permission and can access all databases / data and there is no way to restrict it.

You can use the encoding functions of SQL Server using a secret key, then users could only decrypt data if they would have this key; without the key decrypt will fail even if the user do have admin permissions.

But then you have to implement the encrypt/decrypt in every data access in your application and indexing encrypting data will be an issue = not possible.

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 2

Also, though you cannot block access by members of the sysadmin fixed server role, you can audit sysadmin access. Set up SQL Server audit, and write the audit to a location that the members of the sysadmin fixed server role cannot overwrite. This can be used to expose inappropriate sysadmin actions.

---

Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

Answer 3

Noted, although in my case the DBA would also be at least a local administrator and possibly a domain administrator.

Answer 4

SQL Server do have it's own permission system which is independent of any OS permission like domain admin's.

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 5

Do you mean that it would be possible for SQL Server Audit to "write the audit to a location that the members of the sysadmin fixed server role cannot overwrite", even if these SQL sysadmin users are also Windows domain administrators? 

Answer 6

Sorry, but you question is not clear for me.

By default domain admin don't have any access permissions to SQL Server, only if you (the DBA) grant them; in SQL Server 2005 this was different, here all local/domain admins automatically have had permissions.

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 7

Hello Olaf,

And SQL Server 2000 also, I believe. In the environment in which my app is going to run, I believe that the DBAs and domain admins are the same person(s), whether by default or not. I am currently using SQL Server 2012 Express on my machine, and it is possible that Express may be used in production. 

I am not familiar with this topic at all. My assumption was that the SQL Server audit log would be written in the file system, in which case I do not see how it would be possible to protect it from the DBA if he is also a domain administrator.

Answer 8

SQL Server 2000 doesn't have Server Audit. Anyhow, in general terms, even if yo have a domain admin that can delete and audit, you will see that the audit had been deleted! Ie. tampered with, so to speak.

---

Tibor Karaszi, SQL Server MVP | web | blog

Answer 9

True. Thanks, Tibor.

Answer 10

By default domain admin don't have any access permissions to SQL Server, only if you (the DBA) grant them; in SQL Server 2005 this was different, here all local/domain admins automatically have had permissions.

However, if you are admin on the machine, you can always start SQL Server in single user mode and have access that way.

It is not clear to me why John wants to lock these people out, but if it about intellectual property, I recommend a license agreement. Or host the application yourself or in the cloud.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 11

Hello Erland,

By manipulating the data, a technical person with access to the tables could collude with a user of my app to steal money. Right now, this can be detected within 24 hours, and that is probably good enough because we have transaction limits, etc., but I am looking for ways to prevent the creation of bogus transactions, or earlier detection of such transactions.

I am a programmer. I would not be any kind of admin (Windows or DBA) on the server.

Answer 12

However, if you are admin on the machine, you can always start SQL Server in single user mode and have access that way.

Sure; but if you restart SQL Server in single user mode, some users will note this as I guess ;-)

---

Olaf Helper

[ Blog] [ Xing] [ MVP]

Answer 13

If you only consider yourself a programmer, maybe then you should not dig too deep into this on your own. It is eventually something for a security officer to make decisions on, although you may of course be asked for input.

Having people who are adminstrators and have a high level of access is a matter of trust. But trust may not always be sufficient. There are sites where no one knows the full password. Two persons may have one half of each. There are sites where you cannot work alone on a production server, but there will always be someone looking over you etc.

It goes without saying that such arrangements are quite expensive.

---

Erland Sommarskog, SQL Server MVP, [e-mail]

Answer 14

Erland, I agree with all that you say here. Personally, I consider the risk in this case to be low, and even if there were an incident, the damage would be limited. However, I was asked a specific question. These facts will be presented to the business, and I will continue to work on ways to detect if not prevent fraudulent transactions.