Login Permission

Question

Login Permission

lebi

Old Hand

Points: 318
More actions
October 3, 2004 at 7:40 pm

#84209

My client wants the security of the SQL Server to be tight and he only wants the sa and other logins which normally using the web application to login to the sql server. So I remove the builtin/administrator group from the logins and create a domain user account as the MSSQLServer login service. But when I tried to associate the domain user to the MSSQLServer service, it can't be started and have an error of 'Access is denied'. So I decided to add it to the local administrator group of the machine, and it finally started. Then I tried it to associate also the domain user to the SQL Server Agent as the login service, it fails and have an error on the event viewer that says that the user must be a member of sysadmin role. But if I will add this user from the sysadmin role, the domain user will have enough rights to add himself to another database that is prohibited to him to view sensitive data. And this can be done by some domain administrator that can just changed the password to the Active directory and under the SQL Server Agent login service. Is there any other way of securing the logins?

Viewing 6 posts - 1 through 5 (of 5 total)

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

K. Brian Kelley SSC Guru Points: 114642 More actions · Answer 1

The user account used to run SQL Server must have sysadmin fixed server role membership, as does the user account used to run SQL Server Agent. What a lot of folks do is they create a special account used exclusively for the SQL Server service, one that no one else uses.

Pick a password no one can remember (or better yet, use a random password generator) and write it down. You'll need this password only long enough to set the SQL Server and SQL Server Agent services up to run under it.

Use Enterprise Manager to set the service account(s) for SQL Server and SQL Server Agent. This will ensure the account(s) have the rights they need. Yes, they do require sysadmin rights, but EM will take care of this for you.

Verify the SQL Server and SQL Server Agent services start correctly.

Take the password, put it in an envelope, and lock it up. There should be some procedures on how to retrieve the password. For instance, approval by the DBA manager, etc. This ensures that on a daily basis no one can use the account for their own purposes but in an emergency situation you can log in as the account.

K. Brian Kelley
@kbriankelley

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 2

You might also want to check Active Directory as well. Pull up the service account for SQL Server and check the information there by selecting the 'Account' tab and then clicking on the 'Log on to...' button and possibly the 'Logon hours' button as well.

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

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

Since it's a service account, you don't want to alter the logon hours. You may, however, want to secure what systems it can logon to. However, since the concern is that someone will use the service account to connect to SQL Server, it would already have the ability to log on to the server needing protection.

K. Brian Kelley
@kbriankelley

Yelena Varshal SSC-Dedicated Points: 34286 More actions · Answer 4

Hello,

It is more of the administrative then a technical issue. In the similar case we kept Builtin/Administrators but removed support groups from Windows Administrators group. There was only Administrator, and 1 login for each: server support, DBA and the developer.The customer has to be explained that someone has to be trusted to at least support the server. SQL Server and Agent were started on LocalSystem account.

Do you really have a need for SQL Server and Agent to be started on the Domain Account? You may achieve the same tasks by using the external jobs. For example, you need Agent started on the Domain Account to be able to copy backups off the box. In this case, let Agent to back up to the box and let some other external job move files

By the way, do you password-protect backups? without that everyone can restore backups on another box.

Yelena

Regards,Yelena Varsha

lebi Old Hand Points: 318 More actions · Answer 5

Thanks for all of your inputs. I guess it's boil down to have a trust in your Administrator and the needed confidence in him in keeping the security at domain level, keeping in mind that it is one of the reason why you hired him.