DOMAIN vs System Account to Start and Run SQL Server

[Raymond Chiu (gatorback]

BACKGROUND
=========

This choice is configured as a property of the SQL Server(SQL Enterprise
Manager Console) under the Security tab.

The installation instructions show a domain account to start and run SQL
server.

THANK YOU for your insight.



QUESTIONS
========

Q1) Does it matter which domain account is used (i.e. the canonical
DOMAIN\MSProjServ)?

Q2) Can the system account be used? At the expense of....? If not, why?

Q3) What are the pros and cons of using a DOMAIN account to Start and run
SQL Server?

 

[Earl Lewis]

Raymond,

Cons of domain accounts for SQL service login:
==============================
domain accounts are subject to password maintenance rules of the domain, which means expiration rules, which you don't ever want to happen for a service account. Local account rules will ignore the domain rules and allow the service account password to never expire.

Pros of domain accounts for SQL service login:
==============================
can't think of any

The generally accepted best practice here is that you create a local system account on the target machine where SQL server is installed and all the SQL services (SQL, SQLService, DTS, etc...) use this account so you don't ever have to worry about SQL not being able to start and run.

Any of you MCSE gurus have differing opinions?

Earl
BACKGROUND
=========

This choice is configured as a property of the SQL Server(SQL Enterprise
Manager Console) under the Security tab.

The installation instructions show a domain account to start and run SQL
server.

THANK YOU for your insight.



QUESTIONS
========

Q1) Does it matter which domain account is used (i.e. the canonical
DOMAIN\MSProjServ)?

Q2) Can the system account be used? At the expense of....? If not, why?

Q3) What are the pros and cons of using a DOMAIN account to Start and run
SQL Server?

 

[John Sitka]

I inherited an SQL Server that used a local account but as soon as I needed real network functionality for
moving files for log shipping, backup, DTS all of which may utilize jobs; the SQL local system account
fails. There are work arounds by specific permission setting and maintenance of matching local accounts
on other servers. However it's been smooth sailing since dedicating a domain login for the startup accounts
of both the MSSQL AND SQLAGENT Services. How this really effects Project Server I'm not sure.
But I have gained huge efficiencies in my work by using the domain account, as the SQL machine does
much more than Project Server.

So the pro for me is:
I can set up my SQL jobs and administrative scripts that may be called from those jobs
to work across various network clients easily.

Now the Project server logins? Maybe they should be dedicated Windows User accounts for those to...

 

[Earl Lewis]

John,

Good points. But couldn't you run the scheduled jobs and network operations using a separate set of credentials that is a domain authenticated account? In fact, you should perform those operations with a specific domain user account so you know who did what when.

But I see starting/running the services on the server as very distinct and separate functions. You NEVER want the service startups to fail as long as you want to have a truly available solution, especially because of a password expiration that you didn't anticipate.

As always, just my .02.

Earl



I inherited an SQL Server that used a local account but as soon as I needed real network functionality for
moving files for log shipping, backup, DTS all of which may utilize jobs; the SQL local system account
fails. There are work arounds by specific permission setting and maintenance of matching local accounts
on other servers. However it's been smooth sailing since dedicating a domain login for the startup accounts
of both the MSSQL AND SQLAGENT Services. How this really effects Project Server I'm not sure.
But I have gained huge efficiencies in my work by using the domain account, as the SQL machine does
much more than Project Server.

So the pro for me is:
I can set up my SQL jobs and administrative scripts that may be called from those jobs
to work across various network clients easily.

Now the Project server logins? Maybe they should be dedicated Windows User accounts for those to...

 

[John Sitka]

Sure, just it gets pretty scary in there. Depends on what the job does, what kind of procs it may call.
Basically every job has to impart some kind of impersonation to do things. Often these are the kind of
things with very tight security on them. The imerpsonation tree has often left me cussing to the point where
I couldn't figure out what is going on. So I followed the advice of people more sophisticated than I and
utilized a Domain account. I actually think this may be required in some clustering/replication and SAN
implimentations.

 

[Raymond Chiu (gatorback]

Thanks for the insight John.

If I narrow the scope of the task to a local account (SA) to only Starting
SQL server (and using domain accounts to perform any other task), does that
alleviate the problems that you mention below?

I suppose that making this change is trivial, however, with the complexity
of things that can go amiss in project server, I am trying make the right
decisions the first time

Raymond

 

[John Sitka]

That's where my experience dosen't cover Project Server.
Here's some speculation.
To me if the SQL server is dedicated to just supporting that PS application,
you have enough disk space locally, "to tape" backup capabilities locally or
via an outside agent. I don't think there would be to much issue with using a local
system account. It's when all kinds of 'systems' things are running on your SQL
that domain accounts show their might. It's all about reaching other things on the network
by means of "computing stuff" other than uniquely database functionality, Copy files, run other
services, call other procs on different SQL machines within jobs stuff like that. DTS packages
Many of the powerful scripting tools default to the sysadmin role (where it belongs),
You may not want to set execute permissions on those procs (utils or extended procs, and one day .Net)
to anything other than the sysadmin role, Especially not for something run of the mill like a proc with an
XCOPY in it. I like a domain account belonging to the local SQL machine admininistrators group.

So here is what I was looking for. the other side of the arguement from
http://www.sqlservercentral.com/columnists/bkelley/services.asp
It was just the first article I found.
.....I've already spoken briefly when considering each service in turn about permissions and it's not my intent to go into a lot of
detail here. Here are the basics:

Each service can run as the localsystem account, a domain account, or a local user account (account created explicitly on the
server)
Choice of permissions determines the extent of what each service can do
From a security perspective, give a service account only the permissions it needs and no more (e.g., no Domain Admin accounts under
normal circumstances) ....

So while that's accurate, I find I need to scale and support multiple systems quickly and the domain account helps me do that. If I
can find
the best practices reason for using a domain account I'll post it.