sql security

Question

sql security

Richard Cranston

Hall of Fame

Points: 3680
More actions
January 25, 2008 at 9:33 am

#119725

I am very very new to sql security.
I now have a new database 'ADP_Extract' which I want to deny access for our network domain administrator account 'internalcpwdomain\administrator'.
Only the 'SA' and sql login 'adpusr' for that database should be granted access.
Is the network domain administrator account part of the Builtin admin accounts?
see attachment.
Also, know any great tutorials on learning sql security for beginners?

Viewing 8 posts - 1 through 7 (of 7 total)

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

SQL ORACLE One Orange Chip Points: 27807 More actions · Answer 1

I do not think you can.

Builtin\Administrators is a group with local admin administration provilege. All users in this group have sa privilege by default, including networking domain administrators. Unless you can remove this group, otherwise you cannot stop them to access your databases.

Normally, Builtin\Administrators group should be removed for security reason. If so, you can remove all other internet accounts accessing your databases.

I now have a new database 'ADP_Extract' which I want to deny access for our network domain administrator account 'internalcpwdomain\administrator'.

I have not seen any good book about security. I learned from practice.

KenpoDBA SSCrazy Eights Points: 9811 More actions · Answer 2

Yeah, that's totally doable. Just remover the builtin\administrators group from SQL and you'll be all set. Only logins that you give explicit rights will be able to access anything. The one thing I can tell you though is to make sure that you give the accts your sql services are running under sa rights. If those accts don't have sa rights, then you won't be able to start those services. So give your service accts sa rights, then delete builtin\admins from sql and you're all set.

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:

Richard Cranston Hall of Fame Points: 3680 More actions · Answer 3

sql services are setup for local account currently; how do I verify the local account has sa rights? Does the local account represent who I log into the server; which is the domain administrator?

I have other sql servers services where the accounts are the domain administrator as this account has rights to another windows server which is used as the target backup specified in maint plan jobs. Once this server goes into production, I think I would have to do this also so the backup job will run.

If I remove the group for Builtin/Admin, how would that effect my other Databases? Is it easy enough to add back the Builtin/Admin group?

KenpoDBA SSCrazy Eights Points: 9811 More actions · Answer 4

All good questions:

1. I believe if you want to run the service under local, then you'd have to give sa to 'Local Service'. However, it's best to create a named local user acct and run the service under that instead.

2. Running SQL under domain admin is an extremely bad idea. It not only gives your domain admins access to all your cool SQL stuff, but it severely compromises your DB security should something get broken into. Run each server under a different acct. I would create separate domain accts for each SQL box and give each one the rights on the other boxes that it needs. This also helps when you have to change a password, right... cause if you have the same acct on all your boxes and the password gets changed you have to pull downtime for all of them at once to change it in the service. If you have a separate acct for each box then you can change them one at a time and not worry how it effects the other boxes.

Since your reply isn't posted in here I can't tell if I'm missing anything. Did I forget to answer anything?

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:

KenpoDBA SSCrazy Eights Points: 9811 More actions · Answer 5

Oh yeah... I forgot. You CAN add builtin\admins back in if you need.

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE:

Richard Cranston Hall of Fame Points: 3680 More actions · Answer 6

I want to thank everyone for the quick replies and suggestions.

I did have a new domain network account 'sqlBackup' created awhile back; but when I attached that new account to the sql services, it did not start. I will have to try that again. How do I verify the 'sqlBackup' has 'sa' rights?

Thanks again for everyone's help.

KenpoDBA SSCrazy Eights Points: 9811 More actions · Answer 7

Well, you'll just look in the sysadmin group. If it's in there, you're good.

Also, any acct you want to use for a service has to have OS rights. Logon as a service is one of them. If you just wanna shotgun it, just make that sqlbackup acct a windows admin on the box and it'll startup as the service just fine. If you want to snipe the rights I don't have that data right here with me, but it's easy enough to lookup.

Watch my free SQL Server Tutorials at:
http://MidnightDBA.com
Blog Author of:
DBA Rant – http://www.MidnightDBA.com/DBARant

Minion Maintenance is FREE: