Showing posts with label members. Show all posts
Showing posts with label members. Show all posts

Sunday, March 11, 2012

Best Practice for Domain Account for SQL Services?

Hello,

I've done some searching, but have found no definite answer yet. Our SQL 2005 servers are members of Active Directory Services. We want to run SQL services using an ADS account.

I see 7 SQL services in the SQL Server Configuration Manager: Integration Services, FullText Search, SQL Server, Analysis Services, Reporting Services, Browser, and Agent.

Question: Is it a bad move to run them all using the same domain account? I mean, wouldn't this give, say the Browser service, more privileges than it needs by allowing its account access to the same resources as, for example, the Agent service? What I'm concerned about is a vulnerability in one service compromising another service.

I would like to be able to use one domain account for all 7 services on two SQL servers, but I have a feeling this is a poor choice.

What is the best method for running SQL services using a domain account?

Thanks,

-Tony

Perhaps these articles will help you in your decison process:

Configuration -Service Accounts, SQL Server 2005 - Setting Up Windows Service Accounts
http://msdn2.microsoft.com/en-us/library/ms143691.aspx
http://msdn2.microsoft.com/en-us/library/ms143504.aspx

Configuration -Service Accounts, SQL Server or SQL Server Agent service account
http://support.microsoft.com/kb/283811/en-us
http://msdn2.microsoft.com/en-us/library/ms143691.aspx

Configuration -Service Accounts,Selecting an Account for the SQL Server Agent Service
http://msdn2.microsoft.com/en-us/library/ms191543.aspx
http://support.microsoft.com/kb/907557

Often, SQL Agent needs a higher level of access to network resources than the SQL Server Service. It is a good practice to provide the minimum level of access to local and network resources as is required for the task. It would not be unusual to have multiple domain accounts for the various SQL Services. In fact, for those services that require network resources, some folks will have separate domain accounts for each server. I have seen every SQL Agent service running under a unique account -the theory is if one server is compomised, unique accounts protect the remaining servers from compromise.

It is a balance between amount of security desired vs. amount of effort required to maintain that security. (As always...)

Monday, February 13, 2012

Beginner question - sorry

Suppose I have a group of members and I want they to groups in to different categories. Each member has an ID and I would like each group to have an ID- I guess in programming it would typically done with a 2 dimensional array or something. How does SQL do this? Also each member may belong to more than one group. and also - I though of creating a new table for each group but then I am afraid of the maintenance of so many tables and the groups could quickly go into the thousands and ten thousands. And it really doesn't fit into my design plans also.

Any help or ideas would be appreciated.

Thanks,Hi,

There are a number of solutions to this problem, one would be to create a Groups table, a Users table and a UsersPerGroups table.

Users

UserID | UserName
--------
1 | echo88
2 | taylorza
3 | tmorton

Groups

GroupID | GroupName
--------
1 | Admin
2 | Power User
3 | User

Putting users in groups is as easy as populating the UsersPerGroups table, in this case
echo88 belongs to the Admin and Power User groups
taylorza belongs to the User group
tmorton belongs to no groups

UsersPerGroup

UserID | GroupID
-------
1 | 1
1 | 2
2 | 3

Hope this helps