Preventing System Admins From Accessing Database (1 Viewer)

[DalynSoft]

Hi Listers,

I have an SQL database which will be installed on a client’s server.

Is there a simple way to prevent the client’s server system administrators from accessing the database (specifically changing security settings or accessing the stored procedures and functions) but still allow me to have full control over the database?

I am looking at pointers at this stage for me to investigate (eg if I install a separate instance of SQL Server just for this application can I have control over the security?)

 

[GPGeorge]

Interesting proposition. You want to deploy a database on a server owned by someone else. However, you do not want employees of that entity to have control over this database, which is on a server they own? Do I understand that correctly?

Would you approve the reverse? Would you allow one of your clients to install a SQL Server instance on YOUR computer, such that they control it and you can't make any changes to it?

Perhaps I'm missing the point, but I'm not quite convinced your client's employees, especially those tasked with managing their company's data infrastructure, are so unreliable that you need to implement this strategy. What would the concerns be? What are you trying to prevent?

That said, I know that, if you get their IT departments approval, you should be able to install an instance of a SQL Server with an SA account for which you have the credentials for, but for which their IT people do not. The key is, as I understand it, convincing that client's IT department this is in their best interest.

Maybe if I better understand the dynamics of the situation, it would not seem so unusual.

 

[The_Doc_Man]

From an operational viewpoint, you cannot reasonably expect this to work very well. One of the issues regarding any database is the ability to perform a full backup, which requires someone (usually the BackupOperator intrinsic account or someone designated to have the same privileges) to have the ability to trigger and execute the backup procedure, whatever it is. Unless, of course, you will do your own backups during non-operational hours. Don't know for sure about SQL Server, but on ORACLE that means the ability to tell the DB engine to enter "backup" mode while the backup is running and then leave that mode when done. And for ORACLE, that has specific implications about transaction handling. I'm only guessing for SQL Server, but it wouldn't surprise me to find they had something analogous to ORACLE's "backup mode" operations.

Then, if your site actually HAS a security team, I can tell you their answer... NO. Without access to the files, they will be unable to run a security scan checking for injected code - and SQL injection is a well-known security breach pathway. The IT security team will, probably by directive from the higher-ups in the company, have FULL ACCESS to everything on site to check for intrusions and for compliance with security standards.

From a whole-file viewpoint, you CANNOT prevent the SysAdmin staff from accessing the files. From a file-internal-structure viewpoint, some level of access might be blockable - until the SA exercises the right of the SA to take over ownership of a given file. At which point, a lot of forbidden actions suddenly become trivial. OR until the SA decides to do a Run As to launch some script that, because it has the right credentials, will be able to do whatever it was that you didn't want done.

Finally, if you are in a small company, many folks wear multiple hats. For smaller installations, the SA might also be the chief DBA. For a larger company, there still will be many hats to wear (just more heads on which to distribute them), but that actually would work AGAINST you since upper management will more likely believe in distribution of duties and won't LET you take full responsibility over operational issues if the database in question is important to the company's operation.

This is a case where technically, you might be able to do what you want, but politically it is extremely unlikely. And I hate to break this to you, but us tech nerds lose EVERY TIME to the political wonks. It's just the way of the world.

 

[tvanstiphout]

Interesting proposition. You want to deploy a database on a server owned by someone else. However, you do not want employees of that entity to have control over this database, which is on a server they own? Do I understand that correctly?

Would you approve the reverse? Would you allow one of your clients to install a SQL Server instance on YOUR computer, such that they control it and you can't make any changes to it?

Perhaps I'm missing the point, but I'm not quite convinced your client's employees, especially those tasked with managing their company's data infrastructure, are so unreliable that you need to implement this strategy. What would the concerns be? What are you trying to prevent?

That said, I know that, if you get their IT departments approval, you should be able to install an instance of a SQL Server with an SA account for which you have the credentials for, but for which their IT people do not. The key is, as I understand it, convincing that client's IT department this is in their best interest.

Maybe if I better understand the dynamics of the situation, it would not seem so unusual.

> you should be able to install an instance of a SQL Server with an SA account for which you have the credentials for
Ha! There is the little-known gem that users with full control of the server {e.g. Domain Administrators) can always take control. See for example how to reset SQL Server sa password - Stack Overflow

 

[tvanstiphout]

Hi Listers,

I have an SQL database which will be installed on a client’s server.

Is there a simple way to prevent the client’s server system administrators from accessing the database (specifically changing security settings or accessing the stored procedures and functions) but still allow me to have full control over the database?

I am looking at pointers at this stage for me to investigate (eg if I install a separate instance of SQL Server just for this application can I have control over the security?)

> or accessing the stored procedures and functions
Look up "WITH ENCRYPTION" clause in BOL.

 

[Isaac]

Hi Listers,

I have an SQL database which will be installed on a client’s server.

Is there a simple way to prevent the client’s server system administrators from accessing the database (specifically changing security settings or accessing the stored procedures and functions) but still allow me to have full control over the database?

I am looking at pointers at this stage for me to investigate (eg if I install a separate instance of SQL Server just for this application can I have control over the security?)

It seems like during the install, you could grant permissions to only SQL AUTH (not windows auth, and not Administrators), and create a login account, I only have experience installing sql server express but it does seem like there is a screen where this would be possible during the install process.

However, along with everyone else, it's a strange request. I do sort of understand it, because I can actually imagine the possibility of a client telling me "I want YOU, (Isaac), to manage this database, and I don't want you to allow anyone here - including our IT dept - to be able to monkey with the configuration of this tool at all" - in other words, the exact same thing we would all normally do to protect against curious users making changes, except you're adding in the IT dept. I can imagine this, although somewhat rare, happening.

 

[KitaYama]

Would you approve the reverse? Would you allow one of your clients to install a SQL Server instance on YOUR computer, such that they control it and you can't make any changes to it?

However, along with everyone else, it's a strange request.

We purchased an out of box accounting solution a while back. During install process, it installed sql server express, then the main application was installed.
Sql server is locked and none of our admins are able to access the database or tables. We can install another instance of server if we need, but no one can access the instance the application installed, nor can even login to it.

I don’t understand why everyone is surprised by the idea of installing a server on someone else‘s machine and prevent the admins to have access to it. To me, it’s common sense to protect your property.

 

[Isaac]

Well, ideally I'd want the cooperation of the IT/admin department. but I agree that, failing that, exclusion could be a possible solution.

 

[The_Doc_Man]

We purchased an out of box accounting solution a while back. During install process, it installed sql server express, then the main application was installed.
Sql server is locked and none of our admins are able to access the database or tables. We can install another instance of server if we need, but no one can access the instance the application installed, nor can even login to it.

I don’t understand why everyone is surprised by the idea of installing a server on someone else‘s machine and prevent the admins to have access to it. To me, it’s common sense to protect your property.

You don't understand it, but I do. I'm not saying that the case you describe is miliary-related, but that would NEVER happen with a U.S. Navy server because ONLY a particular kind of administrator EVER has permission to install something. It's a security thing. You DO NOT under ANY circumstances install something on a machine where the IT guys won't be able to touch it. This isn't a tech issue, it is a politics and regulations issue.

EDITED by The_Doc_Man to add emphasis to the key phrases.

 

[KitaYama]

You don't understand it, but I do. I'm not saying that the case you describe is miliary-related, but that would NEVER happen with a U.S. Navy server because ONLY a particular kind of administrator EVER has permission to install something. It's a security thing. You DO NOT under ANY circumstances install something on a machine where the IT guys won't be able to touch it.

No offence but it seems that it's you who don't understand.

1. OP is not in the navy and his client is not the most strict organization in the world. So, the reply to his question SHOULD not be navy based, and I think few people care that how navy manage their computers.
2. You have never purchased a boxed software that needs a sql server to see that even God can not touch the server. IT and admins can not even reach the login screen. If you haven't seen it in the navy, it means they never purchase boxed software. They create their own to have full control over it.
3. OP's client has purchased his software and he has full copy right over his software. It's the law. So it's legal to prevent his software from anyone else. Admins, IT Team, backup team, security team, you name it.
   If the client can not accept it, they can go and learn how to make the software they need.

To me it's like you say : "I've bought Microsoft Office. My IT and security team needs to see what is inside and have control over it."
Microsoft will simply laugh at you. You have purchased the right to use it, not to see how it's built. Just like OP's software and his database. No one, (I say it again), No one has the right to see the table structure, relations, views, stored procedures, etc.

You can argue for your point and how navy works for ever, but the law gives full ownership right to the software writer, and based on this law, any software manufacturer, locks his property from admins and IT. If IT can not accept this simple fact, go write your own. Don't buy mine.

 

[Isaac]

I have to admit I see KY's point. While I never specifically sought to keep I.T. from accessing my database design, I definitely sought to keep "everyone in general" from it - which I suppose would have included I.T., unless they had a need and asked me nicely, (or told me with some authority), then I would collaborate with them to do it together.

I think the aspect of all this that we're not used to, @KitaYama , is someone just specifically calling out and mentioning the administrators by group especially. But sure, it's common for a db developer to expect nobody except them to be messing with the guts of the thing, I suppose, common enough anyway. I see both sides.

 

[DalynSoft]

Thanks everyone for your input. My scenario is more "Microsoft product" rather than software specifically developed for a client (and definitely not military grade requirements ). We are concerned that the clients may "sell" the application to others. In preventing the stored procedures from being available this reduces considerably the opportunities for our code logic to be shared.

Installing a separate instance seems to be the way to go. We would be wanting to work with the IT department and enable them to be able to do backups.

@tvanstiphout - My understanding is that if I removed the sa account and set up a separate sysadmin account then this prevents resetting the sa password. I will look in more detail into the link you provided.

 

[theDBguy]

@tvanstiphout - My understanding is that if I removed the sa account and set up a separate sysadmin account then this prevents resetting the sa password. I will look in more detail into the link you provided.

I read the link and my understanding is that anyone with admin rights to the server will be able to reset the SA password on the DB instance. I could be wrong though.

 

[DalynSoft]

But does it replace the sa user if it has been removed from the database?

 

[The_Doc_Man]

No offence but it seems that it's you who don't understand.

1. OP is not in the navy and his client is not the most strict organization in the world. So, the reply to his question SHOULD not be navy based, and I think few people care that how navy manage their computers.
2. You have never purchased a boxed software that needs a sql server to see that even God can not touch the server. IT and admins can not even reach the login screen. If you haven't seen it in the navy, it means they never purchase boxed software. They create their own to have full control over it.
3. OP's client has purchased his software and he has full copy right over his software. It's the law. So it's legal to prevent his software from anyone else. Admins, IT Team, backup team, security team, you name it.
   If the client can not accept it, they can go and learn how to make the software they need.

To me it's like you say : "I've bought Microsoft Office. My IT and security team needs to see what is inside and have control over it."
Microsoft will simply laugh at you. You have purchased the right to use it, not to see how it's built. Just like OP's software and his database. No one, (I say it again), No one has the right to see the table structure, relations, views, stored procedures, etc.

You can argue for your point and how navy works for ever, but the law gives full ownership right to the software writer, and based on this law, any software manufacturer, locks his property from admins and IT. If IT can not accept this simple fact, go write your own. Don't buy mine.

For your comment #1: My point isn't that this is a Navy-like situation. My response is more of the order of "Is your company paranoid about what gets installed?" This is NOT a techie problem so much as it is a political or corporate policy problem. Let me say it this way: If the company rules allow it, it will happen. If the company rules don't allow it, don't get too accustomed to it. Military or not doesn't matter. What is the corporate environment? You talk about Microsoft laughing at you. But suppose you ARE Microsoft and something you bought doesn't work right... do you think for even a heartbeat that the full force of MS's attorneys won't take sudden interest in you?

For point #2, I have actually dealt with Navy-purchased boxed software that wanted to install stuff. We tried to get it to meet government standards and it would not, so we de-installed the whole thing and returned it to the vendor with a "Did not meet requirements" report. Yes, more than once the Navy commissioned creation of a "roll your own" package. Been there, done that, wore out that T-shirt until it was ragged.

For point #3, the legalities of who can control something have to include whether the software meets the requirements and that may include who will maintain the software when an upgrade or fix is required. Within those guidelines you DO have certain legal remedies. The law doesn't allow me to steal someone else's technology, true and I understand that. But if something doesn't meet contractual performance standards, there are certain remedies. Let's just say it is not ALL cut-and-dried. Sometimes there are these nasty little loopholes. I've run across more than a couple of those nasty little loopholes in 28 1/2 years of Navy experience.

 

[theDBguy]

But does it replace the sa user if it has been removed from the database?

There's no way for me to test this, but my understanding is if you left SA in, then they can reset its password. Or, if you took out that account, then they can add anyone with admin rights anyway.

 

[spaLOGICng]

Hi Listers,

I have an SQL database which will be installed on a client’s server.

Is there a simple way to prevent the client’s server system administrators from accessing the database (specifically changing security settings or accessing the stored procedures and functions) but still allow me to have full control over the database?

I am looking at pointers at this stage for me to investigate (eg if I install a separate instance of SQL Server just for this application can I have control over the security?)

Yeah, remove them from any of the Admin Permission Roles.

 

[DalynSoft]

Yeah, remove them from any of the Admin Permission Roles.

There has already been a lot of discussion around this. Do you mean from the SQL Instance, or just the database? If from just the database would they not be able to add an admin user if they have admin rights for the server instance?

 

[spaLOGICng]

There has already been a lot of discussion around this. Do you mean from the SQL Instance, or just the database? If from just the database would they not be able to add an admin user if they have admin rights for the server instance?

You said Database, therefore I refer to Database lever permissions. If the System Admins already have Security Admin at the Server Level, there is nothing you can do. The owner and SA of the Server should ALWAYS have access to every Object and Role on the Server.

 

[Josef P.]

Note: And if someone has the rights to copy the backup file, they can restore the database in their own server instance in which they have SA rights.