February 8, 2009 at 2:05 pm
Alright guys, I've learnt your advises, and I will try to implement them.
Thank you very much for the discussion.
February 9, 2009 at 8:17 am
I setup Kerberos, so I use a Domain User account.
Part of your choice depends on if you run applications (SQL Server, Analysis Services, Reporting Services, IIS websites, etc) on different servers.
Our network admins prefer us to use Domain User accounts vs. local accounts. So network policy may also be a factor.
Greg E
February 9, 2009 at 8:34 am
I've almost always used domain accounts. Set one up, no rights outside Everyone, which has no rights, and then let SQL Setup (or Config manager) assign rights.
Easier to do that early on.
February 9, 2009 at 8:55 am
VEB (2/4/2009)
If a domain account is compromised, the domain is affected; if a local account is compromised, only a single machine is affected.
Keep in mind that if the same Name and Password are used for these local accounts on multiple machines, if the account is compromised, all the machines can be considered compromised too. So 'local' may not be as local as you would expect.
There also may be policies enforced on the domain, like password expiration or complexity, which wouldn't apply to a local account.
Greg E
February 9, 2009 at 1:48 pm
Steve,
I understand your idea. Excluding the accounts from Domain Users group would make them really rightless.
I know that SQL Server setup or Conf Mgr assign all appropriate rights, so it's not a concern.
Greg,
Yes, I already know that using Kerberos requires domain accounts for SS services - thanks to Andrew.Hatfield who mentioned that earlier. Although I'm not sure I will configure Kerberos authentication.
As for network configuration, within one domain I've got a single SQL Server machine with 2 SS instances - one for for monitoring apps, and another for developers. Monitoring apps (Dell OpenManage, HP SIM, MOM, antivirus network control center) are spread among 2 machines. Developers' web-apps are spread among another 2 machines with IIS, and they don't use Kerberos authentication. There're no Analysis and Reporting services, it's SS Workgroup Edition.
As I'm also a network admin, I could define network policy - but need reasons for a certain decision.
As there's a single server, using the same username and password for multiple machines is not the case. Moreover, I would never do so. Moreover, I prefer different acconts for defferent services within a single machine.
You also give me a clue to another potential problem - I mean password domain policies. Such an account should be explicitly configured with "password never expires" setting.
Is it right that using Windows Integrated authentication within SQL Server, and assigning permissions to databases for domain user accounts will require SQL Server service to run under domain account?
Does Browser service need to be run under domain account? (AFAIK, all it listens on 1434/UDP and answer to network broadcasts for SQL Server instances.)
Does Agent need domain account? (There will be no linked servers.)
Steve and Greg, I appreciate your participation.
February 9, 2009 at 2:33 pm
We run in mixed mode, mostly due to some legacy apps. You could probably still install / run as Network Service or Local System, even with Windows Authentication only checked.
Running under a domain user account, this also can be 'locked down'. The ID's I use for our setup can only log into the servers they run services on. Also look at Constrained Delegation for being even more specific.
Part of most global policies that may help ease the non-expiring issue - account gets locked out by policy with missed attempts to log in. They can be allowed to expire, but it's just one more thing to track and change. Since no one actually logs in using this, no one would ever see a reminder when to change passwords.
I'd almost imagine from an auditing standpoint, they might prefer a documented Domain User account being created. Usually there are very few that can create this account, where many more might be able to create a local account.
Sounds like you wear a few hats. And ask some good questions.
You may want to think a bit more down the road - and see if SSAS will be used. Or if you may run inot a 'double hop' situation with a web application. Could lead you to a Domain User account being used anyways.
Greg E
February 9, 2009 at 4:06 pm
VEB (2/9/2009)
Steve,I understand your idea. Excluding the accounts from Domain Users group would make them really rightless.
Remember that every account must have at least one primary group. If you are going to remove an account from Domain Users, you must first create another group and be a member of that and assign Primary Group status prior to leaving Domain Users.
Perhaps a SQL Service Account specific group
VEB (2/9/2009)
Greg,Yes, I already know that using Kerberos requires domain accounts for SS services - thanks to Andrew.Hatfield who mentioned that earlier. Although I'm not sure I will configure Kerberos authentication.
If you have Kerberos configured for your host and services, then SQL will use Kerberos automatically with TCP/IP connections. SQL Server 2005 only supports kerberos over TCP/IP. SQL Server 2008 introduces kerberos support over named pipes.
run select auth_scheme from sys.dm_exec_connections where session_id=@@spid to check if your connection is using kerberos
--
Andrew Hatfield
February 9, 2009 at 4:07 pm
VEB (2/9/2009)
Steve,I understand your idea. Excluding the accounts from Domain Users group would make them really rightless.
Remember that every account must have at least one primary group. If you are going to remove an account from Domain Users, you must first create another group and be a member of that and assign Primary Group status prior to leaving Domain Users.
Perhaps a SQL Service Account specific group
VEB (2/9/2009)
Greg,Yes, I already know that using Kerberos requires domain accounts for SS services - thanks to Andrew.Hatfield who mentioned that earlier. Although I'm not sure I will configure Kerberos authentication.
If you have Kerberos configured for your host and services, then SQL will use Kerberos automatically with TCP/IP connections. SQL Server 2005 only supports kerberos over TCP/IP. SQL Server 2008 introduces kerberos support over named pipes.
run select auth_scheme from sys.dm_exec_connections where session_id=@@spid to check if your connection is using kerberos
--
Andrew Hatfield
February 10, 2009 at 3:38 pm
Greg,
Microsoft doesn't recommend using Network Service and, moreover, Local System for SQL Server accounts, as stated in BOL and corresponding articles @ MSDN. (That actually was my start point in investigating this problem.)
Yes, this is also very good idea to restrict domain account to log on only to SQL Server machine.
And yes, you're quite right regarding an issue with expiration of a service account that noone aware of. That's why I prefer using "Password never expires" setting while using a really long and complex password (which doesn't matter since you need to re-type it only once).
Analysis Services will be unlikely used here where I work, as well as "doble-hop web-apps" (devs prefer using a single account to interact between a web-app and a database, so there's no need for constrained delegation, and hence Kerberos, and hence domain service account).
However, there're too many reasons stated in this discussion to use a domain service account for SQL Server (DBE) service, so I'll definetely use it.
Still the questions remain regarding Agent and Browser services.
Andrew,
Thank you for your reminder regarding primary group. I'm aware of it, and use a special Service Accounts group in AD for such purposes.
I ran the query you provided against SS instances, the result was NTLM. I have Win2003 domain environment, that uses Kerberos. As far as I remember, in December there were an article reference in a daily subscription email message, that discussed using Kerberos, and described how explicitly enable Kerberos in SS, as it's initialy disabled.
February 11, 2009 at 7:09 am
The Agent - a Domain User allows easier permissions to resources not on the server. Say you generate a report and save to a fileshare.
Browser - may want to see if you really need it running. Default is Network Service, which I'd leave alone if you want it running. BOL also describes how it's used.
My comment on Network Service / Local System - was meant to say even checking Windows Auth only, you may still be able to install and run. Local System is running as higher than a local admin - definitely a risk, and this has been proved out in past exploits.
Greg E
February 12, 2009 at 3:04 pm
Greg,
Thank you for the valuable answer.
February 13, 2009 at 6:39 am
You're welcome. Lots of good discussion from all.
Greg E
Viewing 12 posts - 31 through 41 (of 41 total)
You must be logged in to reply to this topic. Login to reply