3

I have inherited a SQL Server box with a series of databases and it has Windows Auth for creation/maintaining the well-baked and established databases. The box itself has many other services which require a user to login as admin to maintain.

I now have to create a new database on the server that only a select few in the company, including IT, will have access to. So I'm kind of chasing my tail on the best place to start.

I want to continue to give the guys the admin level access they require to all the other stuff on this box but limit them on SQL Server options so I can better manage the databases and secure them. What might be the best way to unwind the Windows Authentication method and dole out databases / create opportunities for those users while securing new databases they should not access? Am I even going in the right direction by deviating from the Windows Auth method?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
digital alchemist
  • 43
  • 1
  • 6

3 Answers3

4

One of the primary reasons for creating a second instance on a server is security. By creating a second instance you basically re-start security over again. So this is an option you might consider for your new database. IE Creating a second instance and putting the "secure" database on it.

A few things you should also consider.

First, yes you should unwind your security as possible and give out the minimum security required for any given user/group. This is a best practices thing. Never give out dbo or sysadmin permissions without an explicit reason to do so, and even then question it thoroughly to make sure that there isn't some other way around the problem. Never give out more permissions than are absolutely required.

Second, It is almost impossible to keep the administrator of the server the instance is on out of the instance if they really really want to get in. And I only say "almost" impossible because there may be a way that I don't know about. At the level of administrator for the server, or domain administrator for that matter you have to assume they can be trusted not to try to break in. You probably won't be able to keep them out anyway.

Last but not least if you can move your instance off of a server that has many other services which require a user to login as admin to maintain. This is a security nightmare first of all (as I said above) and second your SQL server will work better on it's own server. I've even heard advice from experts that say you should never remote into the server a SQL Server instance is on. And if you have to remote in definitely don't copy files around while remoted. Generally said the less going on on the server the happier SQL is.

Kenneth Fisher
  • 3,692
  • 19
  • 21
  • I definitely considered the second instance and left out some details to prevent cluttering my question more. In this case the DB to add will be financial in nature and reports will join across DBs they will have access to. I know I can easily do this across DBs on the same instance using an alternate username but wasn't as certain in regards to a secondary instance. I think I am forced to work within the single instance just to save my sanity. My current thinking is changing the admin password, then force remote access as their user name and use those names as the rights to specific DBs. – digital alchemist May 29 '13 at 15:44
  • The single instance path is definitely workable, but probably not as secure. You can still work the two instance path buy using some secured linked servers. Since only a limited number of people can get into the new instance only that limited number of people will have access to the linked servers. The downside is that cross instance queries can be slower and have to be managed better. – Kenneth Fisher May 29 '13 at 16:00
1

You can remove the domain administrators ability to access the SQL Server by removing the BUILTIN\Administrators group from the SQL Server logins.

I wouldn't recommend moving away from Windows Authentication, as you would create a whole new set of security concerns you then would have to deal with.

Xander
  • 479
  • 1
  • 13
  • 25
  • Thanks for this. I'm looking closer at the various reasons those users need that Admin role access to other functions. From there I may just push them to remote in with their specific user/account and give them SQL rights based on their user – digital alchemist May 29 '13 at 15:45
0

Domain admins can

  • add themselves to any group (local or domain) that has has access to SQL Server
  • change the service account policies and log in with that
    • change SQL server to use a service account in case it uses a built in account
  • use any user account that has SQL Server access
    • change password to allow this
  • Do anything in the domain. At. All.

SQL Server always has Window Auth switched on so it is always available to Domain Admins

If it's that sensitive then it needs to be in separate domain or standalone or something.

gbn
  • 422,506
  • 82
  • 585
  • 676