Enterprise Manager Security

Question

Enterprise Manager Security

james.taylor@fma.uk.com

Newbie

Points: 4
More actions
May 15, 2003 at 1:53 am

#80725

I have a company intranet which provides company timesheets project reporting and other admin tools.
Each employee in the company is able to add data and have read only access to this information.
The SQL database uses Domain security and therfore all members of the domain have the select,update,delete rights on the respective databases.
My problem is that many developers have Enterprise Manager and the fact that they have access to the SQL, as stated above, Yhey are able to subscribe to the SQL Server and have first hand access to the Sensitive data.
I have not yet found how / if it is possible to restrict the registration of SQL Servers in enterprise manager to a select members of the domain.
Any help or ideas will be useful
Thanks
James Taylor
james.taylor@fma.uk.com

--------------------------------------------------------------------------------

James Taylor
Systems Engineer
E-mail: mailto:James.Taylor@fma.uk.com

Viewing 15 posts - 1 through 15 (of 16 total)

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

Frank Kalis SSC Guru Points: 111183 More actions · Answer 1

Hi,

I don't know if it is possible to restrict the registration of SQL Servers in EM. But there is another thing you should keep in mind. Many developers are at least local admins to their machines. Some are even domain admins. Users with these NT privileges are by default members of the SQL Server login BUILTIN\Administrators. This login is by default dbo to every db on the server. See where this is going to?

So even, if you are able to restrict registration in EM, they just need a front-end to connect to the server and do whatever they like.

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

james.taylor@fma.uk.com Newbie Points: 4 More actions · Answer 2

Thanks for the help

I Think that i shall just have to encript all my SPs and just hope for the best with my security settings

--------------------------------------------------------------------------------

James Taylor
Systems Engineer
E-mail: mailto:James.Taylor@fma.uk.com

K. Brian Kelley SSC Guru Points: 114572 More actions · Answer 3

quote:
The SQL database uses Domain security and therfore all members of the domain have the select,update,delete rights on the respective databases.
My problem is that many developers have Enterprise Manager and the fact that they have access to the SQL, as stated above, Yhey are able to subscribe to the SQL Server and have first hand access to the Sensitive data.

I don't think encrypting the stored procedures will solve your problem. It sounds like too many people have too many rights into your production databases.

While it may be true that anyone can connect to any given SQL Server using Enterprise Manager (or Query Analyzer or Microsoft Access or Crystal Reports, etc), the reason they can is because they have login rights. However, simply giving them login rights doesn't mean they can get to the data.

Within the databases permissions need to be tightened down. That sounds like where the problem is. Without knowing exactly how the database is architected, it's hard for anyone to give specific comments, but basically it sounds like some tightening is needed. The rule of least privilege: give them only what they need to successfully perform their jobs.

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley

Frank Kalis SSC Guru Points: 111183 More actions · Answer 4

quote:
I don't think encrypting the stored procedures will solve your problem. It sounds like too many people have too many rights into your production databases.

This might save the code, but the problems are the rights on the individual tables

quote:
While it may be true that anyone can connect to any given SQL Server using Enterprise Manager (or Query Analyzer or Microsoft Access or Crystal Reports, etc), the reason they can is because they have login rights. However, simply giving them login rights doesn't mean they can get to the data.

But how do you prevent a developer with admin rights or even an admin from login without removing BUILTIN/Administrator, which is a thing that really should be carefully thought over.

quote:
Within the databases permissions need to be tightened down. That sounds like where the problem is. Without knowing exactly how the database is architected, it's hard for anyone to give specific comments, but basically it sounds like some tightening is needed. The rule of least privilege: give them only what they need to successfully perform their jobs.

For the "normal" user this is quite ok, but the users in question are developers. In our company most developers are something between normal user and admins, which makes the situation complicated. I think sometimes you have to trust in the integrity of these people that the don't make nonsense

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

K. Brian Kelley SSC Guru Points: 114572 More actions · Answer 5

quote:
But how do you prevent a developer with admin rights or even an admin from login without removing BUILTIN/Administrator, which is a thing that really should be carefully thought over.

If it's a production server, developers shouldn't typically have such rights. This will typically be one of the first things flagged in any security audit. Products that blur the line really get heavy scrutiny.

As far as domain admins are concerned, there's not a whole lot you can do here. Keep in mind that even if you were to remove BUILTIN\Administrators, they always have a way in: your user account. Can you say reset password and log in as you?

Of course, with proper auditing, that should be detected, but after the fact. An administrator who is intent on doing a data grab and then fleeing will be successful. That's where the trust comes in. If you can't trust your domain admins with sensitive data, you've got bigger problems than with your database (think email from CEO that the CEO didn't write as one example).

But as a general rule, developers shouldn't have escalated rights on production systems. Because even if you were to obfuscate your stored procedure code using WITH ENCRYPT, any savvy developer is going to do a Google search to find a crack. There are two: one requires sysadmin rights and the other simply requires ALTER PROC rights. The first is by dOMNAR and the second is by shoeboy. Variations of shoeboy's code I believe are in the script library here.

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley

Frank Kalis SSC Guru Points: 111183 More actions · Answer 6

quote:
If it's a production server, developers shouldn't typically have such rights. This will typically be one of the first things flagged in any security audit. Products that blur the line really get heavy scrutiny.
But as a general rule, developers shouldn't have escalated rights on production systems.

so far, so good, but...to my knowledge this scenario is possible. Consider a NT User who is local admin to his machines, has no rights to production Server A and has EM installed on his machines. Although he cannot reach Production Server via NT Explorer, he can reach the SQL Server DB's on Production Server A via EM. Please, correct me if I'm wrong, because this issue had given me plenty of headaches some time ago

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

K. Brian Kelley SSC Guru Points: 114572 More actions · Answer 7

quote:
so far, so good, but...to my knowledge this scenario is possible. Consider a NT User who is local admin to his machines, has no rights to production Server A and has EM installed on his machines. Although he cannot reach Production Server via NT Explorer, he can reach the SQL Server DB's on Production Server A via EM.

No. The user in question can do anything he wants on his system. He doesn't have any rights on another system simply because he's the administrator of his own.

The user has to be able to log in to the production SQL Server using one of two login methods:

1) SQL Server login (if mixed-mode is enabled)

2) Windows authentication

In order to be able to do #1, the user has to have the username and password. With #2, the user's Windows account has to be granted the ability to logon to the SQL Server box in question.

At this point you've logged in, nothing more. What server roles the user has and what databases the user has access to and what permissions within those databases will determine what a user can and cannot do through any client tool, be it Enterprise Manager, Query Analyzer, MS Access, what have you.

Simply being an admin on one's own box doesn't confer you any additional rights on a separate SQL Server box.

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley

Frank Kalis SSC Guru Points: 111183 More actions · Answer 8

quote:
Simply being an admin on one's own box doesn't confer you any additional rights on a separate SQL Server box.

Sorry for asking, but are you sure on this? Is there any additional material (maybe in BOL) on this?

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

K. Brian Kelley SSC Guru Points: 114572 More actions · Answer 9

Yes, I'm positive. Your rights on your system don't grant you additional rights on a separate system.

Okay, what I'm about to say is at a very high level, but is basically the gist of Windows authentication. Keep in mind that when you talk to a system on the network, your domain credentials are communicated. Being a member of the local Administrators group on your box is not part of those domain credentials. Now, you may be a member of a domain group that is a member of your local Administrators group (for instance, Domain Admins), and the domain group will be known by the system you're communicating with, but the fact that you're part of the local Administrators group isn't relevant.

The security section in Books Online (contents tab):

Administering SQL Server | Managing Security

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley

Frank Kalis SSC Guru Points: 111183 More actions · Answer 10

Thanks Brian,

very sophisticated indeed, but understood. Now I know where to dig deeper on our network.

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

Frank Kalis SSC Guru Points: 111183 More actions · Answer 11

Hi Brian,

verified your statement. The user in question is local admin to his box, no access under his account to Server a, but as member of an NT User Group with local admin rights to Server A....

Thanks again for pointing me in the right direction

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

K. Brian Kelley SSC Guru Points: 114572 More actions · Answer 12

That makes sense. Hope you didn't pull too much hair out looking for it. First place I always check is the administrators group on the SQL Server box. Over time I've seen a lot of groups put in it for "convenience" and typically if someone is too lazy to put the developer in right the first time, the person never cleans up later.

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley

Frank Kalis SSC Guru Points: 111183 More actions · Answer 13

quote:
That makes sense. Hope you didn't pull too much hair out looking for it. First place I always check is the administrators group on the SQL Server box. Over time I've seen a lot of groups put in it for "convenience" and typically if someone is too lazy to put the developer in right the first time, the person never cleans up later.

It HAS driven me nuts for some 2 weeks, then I gave up until yesterday

Cheers,

Frank

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

K. Brian Kelley SSC Guru Points: 114572 More actions · Answer 14

In that case, I'm glad my comments helped you find the little beast!

K. Brian Kelley

http://www.truthsolutions.com/

Author: Start to Finish Guide to SQL Server Performance Monitoring

http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1

K. Brian Kelley
@kbriankelley