December 31, 2008 at 2:15 pm
We're in the process of tightening up our servers, and a big part of it is to create separate domain service accounts for each instance. Currently, all servers run under the same account, which has sysadmin privileges.
I've certainly read enough to tell me that this is not a good thing. What I haven't come across is, any suggestions more specific than 'create separate accounts and give them least privilege'. We get that.
Our SQL instances query databases on other instances a lot, so we use a lot of linked server objects. And we have SSIS packages that pull data from all the servers.
Is there a common list of permissions this service account will most likely need? We want to avoid production outages due to permissions issues of course, and I don't have a good feeling about how well we'll sort out those issues in our dev and QA environments.
Thanks in advance.
January 2, 2009 at 7:42 am
There is no common list like you're seeking. That's why the phrase "least privledge" is used, because what one person does with their code isn't necessarily what other people use.
I recommend starting with your Dev/Test environment and trying different combinations there. At least Production won't go down in the process.
For most Linked Server situations, we use a SQL Login account with read access (db_datareader, db_denydatawriter) to the necessary databases. If we have a situation where a certain process requires read/write, we make it use a different linked server which in turn uses a SQL Login with db_datawriter and db_datareader.
That's one example. Again, it depends on what you're doing with your different setups. And there's no good way to determine what permissions each individual item needs other than testing it in a test environment and seeing what breaks and what doesn't.
EDIT Something I forgot to mention. You can also give your accounts access to only specified databases, which also follows this principal.
January 2, 2009 at 8:42 am
With SQL Server 2005 your service accounts only need to be standard domain users. If you use the install process or SQL Server Configuration to set/change the service accounts the proper local access is granted to the service account.
For each individual instance you need to determine the network resources the instance needs access to and grant it to that account.
For linked servers I also typically use a specific SQL account with only the necessary rights. You could also just setup the service account on the linked servers with the needed rights.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 8, 2009 at 7:07 am
As much as possible, try to adhere to the principal of least privilege. It is difficult to tell you what permissions / privileges are necessary to achieve a set of tasks specific to your environment as each organisation has their own business requirements and their network is configured differently.
However, we can supply some information as a starting point for the minimum set of permissions / privileges for SQL Server service accounts to act.
Setting Up Windows Service Accounts http://technet.microsoft.com/en-us/library/ms143504(SQL.90).aspx from the SQL Server Books Online is a great start.
There are some mistakes however. If you customise your "Access this computer from the network" privilege and remove Everyone, then you will need to specifically state that service accounts for SQL DBE, AGT, SSIS and Analysis Services are listed.
--
Andrew Hatfield
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply