SQL Server 2005 security question 3

[takaoki]

How can I "lock down" a database? That is, the database has very sensitive information. But anyone who is an admin on the server can potentially add a login and map that login to the database.

How do I get around this??

Thanks

 

[JayKusch]

If the others have "Admin" privs, there is really no way to lock it down.

In otherwords, whatever you lock down ... they can unlock and do as they please.

You need to restrict the use of the admin account.



Thanks

J. Kusch

 

[Ovatvvon]

Do you think Database Encryption would work? It would take more administration and more system resources, but perhaps would work. If you use transparent encryption, it would not work, but perahaps other kinds in SQL 2005 / 2008? I haven't worked with encryption yet, so I'm not sure; I've only heard about it, and am thinking it may be an option. Otherwise, J.Kusch is right, you'd need to restriction the access to the physical box.


-Ovatvvon :-Q

 

[fuzzyocelot]

If the only admins you're worried about are those who have access to the actual server, then how about removing the built-in administrator account on SQL Server and requiring SQL authentication? You'd have to make sure it wouldn't break anything but it could help. Just a thought.

 

[Ovatvvon]

Ya know, I was just thinking...this is silly. If they're admin's on the box, they should be trustworthy. If they're not trustworthy, they shouldn't be admin's. Am I thinking about this wrong here?


-Ovatvvon :-Q

 

[mrdenny]

Ovatvvon,
No you aren't thinking that wrong. Admins should be trustworthy. If you have admins which aren't get rid of them.

Fuzzy,
Why would you use SQL Authentication after removing the buildin\Administrators group from the server? SQL Authentication is much less secure than using Windows Auth as OLE passes the username and password in plain text when it's logging in when using SQL Auth.

Not to mention most people will not share their domain login with other employees, but they have no problem sharing other system passwords.

takaoki,
Setup Windows logins into the SQL Server for anyone who needs sysadmin rights (A domain group is the easiest way to do this and make sure that you don't loose access to the server). Once you have done this and made the group a sysadmin in the SQL Instance, remove the BUILTIN\Administrators group from having sysadmin rights.

If you are worries about your DBAs seeing your data, there isn't anything you can do about that.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[fuzzyocelot]

Good points, MrDenny. I wasn't thinking straight. Windows Authentication is more secure. Sorry about that.

 

[takaoki]

Mr Denny,

Does removing the BUILTIN/Administrators group prevent Windows 2003 Admins from having sysadmin rights in SQL Server?

Thanks

 

[mrdenny]

Yes removing the BUILTIN\Administrators will prevent Windows Admins from having sysadmin rights to the SQL Server.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[takaoki]

Thanks Mr Denny,

Is it safe to remove BUILTIN\Administrators? I seem to recall reading an article somewhere that listed a bunch of caveats related to this. I'd have to dig it up.

 

[mrdenny]

Yes it is safe to do.

If you use full text indexing you need to add the NT AUTHORITY\Local System account into the server with sysadmin rights as this is the account which the full text indexing service runs under, and SQL 2000 and older do not support running this service under an account other than the local system.

If you have a cluster make sure that the account which monitors the cluster is setup in the server so that it can connect or your instance will start bouncing between the nodes.

Make sure that your account is setup as a sysadmin before going this otherwise you'll lock your self out of the instance.

Those are pretty much the big gotchas.

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog