Creating service accounts should setup do it for you? - SimonS Blog on SQL Server Stuff

Creating service accounts should setup do it for you?

How many people actually create accounts for the SQL Server services to use? I would expect that once you step out of the enterprise the majority wouldn't be. Even though its bet practice.

Why do I think that? Because its generally a pain. If installing the full suite, you have the engine, analysis services, reporting services, integration services, the browser and sql agent all needing service accounts.

So thats 6 accounts, you then need to assign the relevant service to the relevant group.

Then you need to add the SQL Agent user access to the SQL Server.

So what do people do, I suspect most either, use their own account (which is likely to be a domain account and/or a local admin), the local admin account, the network service and the local system account. None of which follow the principle of running with least priviledges.

That results in lots of setups that are running with high elevated priviledges and/or situations where the network or local system accounts have ben used which result in certian features not working, and thus casing confusion and annoyance.

Since the setup fr SQL has change in CTP5 and the service account selection is very different some MVPs have been discussing the options. Hugo Kornelis came up with a great idea. Why doesn't setup create the accounts for us. I remember IIS used to do that to avoid a highly priviledge uer being used as the default IIS account. So why can't SQL.

Here is Hugo's suggestion on conect, https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=310609. If you think its a good idea please vote. Even though the item is still closed, if we get enough votes MS should reconsider it.


-
Published 17 November 2007 08:26 by simonsabin
Filed under: , ,

Comments

17 November 2007 09:56 by barryd

# re: Creating service accounts should setup do it for you?

BizTalk does it for you. I even think the latest SharePoint does.

I think you're being a bit unfair to Local and Network Service though, they're reasonably locked down by default; indeed that's what they're specifically designed for.

17 November 2007 17:20 by Hugo Kornelis

# re: Creating service accounts should setup do it for you?

Hi Simon,

Thanks for generating some extra publicity for my suggestion.

The suggestion was indeed closed as "won't fix" within hours of creating it (I've never had feedback from a connect submission that quickly!), but I have reopened it. Currently 11 votes with a 4.55 average, all within just a little over 24 hours after opening it - I like getting this amount of support! <g>

Folks, if you like the suggestion - let Microsoft know by voting. Even though they do their best to hide it, they DO consider the feedback they get from connect.

(Oh, and if you dislike the suggestion, then you should also let MS know by voting against it).

Best, Hugo

19 November 2007 03:33 by Dan Jones

# re: Creating service accounts should setup do it for you?

This is a thorny issue. In my experience customers either run with a built-in account (Local System or Network Service) or a domain account. Not many run with a local account. After all, in a larger shop you don't want a bunch of these local accounts "running" around. Changing passwords on a monthly or quarterly basis would be a real pain.

Setup could attempt to create a domain account, but not many SQL Admins have the proper access to create domain accounts.

During the development of SQL2K5 we had numerous conversations about this and decided it just wouldn't be used by all that many customers to make it worth while. But 11 votes on a Connect item is HUGE so it brings me pause to rethink my position.

Ok, I've thought about it and I still conclude that it's just not a scenario that would be used by all that many people. The best solution is still to run under a domain account and change passwords often. Oh, and don't provision the Built-in Administrators group. In SQL2K8 we will no longer provision this group by default.

Cheers, Dan

19 November 2007 14:22 by simonsabin

# re: Creating service accounts should setup do it for you?

Firstly trying to find the exact permissions required for each service account and the significance if the permission isn't granted, is not too easy to find in BOL. Secondly, you don't have BOL when you install you server because thats what you're installing. I also think that in a domain its a nightmare, because you have GPOs and trying to resolve why a service account doesn't have the permission can be a nightmare.

One of Dans arguments is that domain accounts allow for easy change of password. Well maybe, however you still have to go to each machine to change the service to use the new password.

Surely its just as easy to change the password for the local user and then change the service as well.

I personally don't like changing domain account passwords for services, if you have 200 servers you have to restart all of them for the new password to take affect. Managin that isn't fun. Whereas using local accounts you don't have any of those pains.

I still think either setup or a setup tool would be good that means users can create the accounts from the seup process if need be, without having to go through a distracting process or firing up a number of other applications to setup the account and then grant it the right permissions

19 November 2007 17:58 by Dan Jones

# re: Creating service accounts should setup do it for you?

Starting with SQL2K5 you don't have to restart the services or the machine to change passwords. I agree that changing passwords across an Enterprise is difficult and we (SQL Server) currently do little to make it easier. You could write a utility app using the SQL Server WMI provider to do it for you. This could also plug-in to the Registered Servers API to retrieve a list of machines.

If you use local accounts you have a whole host of issues with managing passwords. The password on different machines can become out of synch which would make it more difficult to do bulk password changes. Plus you have to rely on people creating the account the same on every machine (name, description, etc). If you need to access off machine resources (for back up or loading) you may encounter various permission differences between machines.

Domain accounts generally don't run into the same set of issues.

Now local accounts do have their place but from my experience most shops use domain accounts.