December 7, 2006 at 9:40 am
Hi,
I have recently started working in SQL Server (2005) and I have inheritted a system that is in its infancy (put together by a number of developers). I am in the process of cleaning it up and trying to secure it before we get too far down the road and there will be a lot more work involved.
My first problem is there are far too many logins on the system. My ideal plan would be to have 4 or 5 logins (windows authentication) max:
a. Myself (domain account) with sysadmin privileges
b. 1 group login that all users use to connect via frontend... I am in the process of cleaning this login up to minimise the access it has but this will be an ongoing task throughout the project.
c. sa which will be used ONLY in a scenario where I am not available and access is necessary.
d. I have created a login for the SQL Agent service account. This account is also a sysadmin and is owner of all the jobs on the database. Are there any disadvantages to using the service a/c in this way??
e. A number of delivered groups for the different services etc.
f. BUILTIN\Administrators has been disabled.
My question is I guess is this a typical security config?
Is it ok to have the service account as a login and owning the jobs?
Of the following delivered logins, which are absolutely necessary and which can be disabled/removed?
distributor\admin
NTAUTHORITY\NETWORK SERVICE
NTAUTHORITY\SYSTEM
server\FTEUser
server\SQLUser
server\SQLAgentUser
I would appreciate any advice/pointers anybody could provide as I am keen to get the access/security side of things nailed down in the early stages of the project.
Thanks Guys!!!!
Gary.
December 8, 2006 at 2:14 am
Having a domain user for the agent is the best practice. Just make sure it sin't a local/domain admin as you don't want people to create harmful jobs with unlimited access. If you do need to preform admin work on the local machine or the network (access files, etc...) you can use proxy accounts.
I think you can let go of the NTAUTHORITY\NETWORK SERVICE and the NTAUTHORITY\SYSTEM logins.
The rest of your ideas sound solid to me.
Adi
December 8, 2006 at 3:52 am
You might want to consider the use of roles and groups.
A role is a SQL Server level principle which can be assigned rights.
You then assign the role to a logon to give the logon the same rights.
This means you can assign multiple logins the same rights easily.
A group is a windows groups which can have multiple windows users assigned as members.
You then assign the group a SQL Server login (windows authentication) and all users in the group will gain access through group membership.
December 8, 2006 at 5:57 am
Of the logins you've listed, the only two native to SQL Server 2005 are the NT Authority \ Network Services and NT Authority \ System logins. Because they are native to SQL Server 2005, I would NOT delete or disable them.
I can't seem to find a good article on what would happen if you disabled the two NT Authority accounts or what they are used for. Just be careful if you do get rid of them, because I have a feeling they're important for something. Possibly for windows authentication.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply