Re: kerberos SQL service accounts

Local Service can't do Kerberos auth because it has no network credentials, so this would be somewhat limiting in this scenario I think. However, network service and local service have very similar sets of permissions, so they should represent a fairly equal trade in terms of using a low privileged service account. The real distinguishing feature is that one has network credentials and the other doesn't, so your service's need for network credentials or not dictates the use.

I can see cases where a fixed service account is needed. I was mostly just suggesting not to rule out Network Service as a service account as a default since it actually works very well in a lot of use cases and saves a lot of potential manual configuration, with manual SPN changes for Kerb auth being just one.

A computer account can set its own SPNs but won't have permissions to set the servicePrincipalName attribute on any random account in AD. That usually requires DA privilege which you cannot count on having during an install. I'm not even sure if the installer even tries to set the SPN on a different account.

--
Joe Kaplan-MS MVP Directory Services Programming
Co-author of "The .NET Developer's Guide to Directory Services Programming"
http://www.directoryprogramming.net
"Richard Mueller [MVP]" <rlmueller-nospam@xxxxxxxxxxxxxxxxxxxx> wrote in message news:OUmUY%233lJHA.1292@xxxxxxxxxxxxxxxxxxxxxxx
One problem I see is for small customers that have a limited number of servers, they might be in the position where all are DC's. Even though it is not recommended, you can install SQL Server on a DC. In that case, the documentation says you should use a domain account. Domain user accounts are also required (from what I read) if you use server-to-server activities like remote procedure calls, replication, backing up to network drives, and Agent mail features.

When you install SQL Server and specify a domain account for the mssqlserver service, are you saying setup doesn't configure the spn's? If you change the user account, then you need to update the spn's, but I was assuming the setup program would handle this correctly when you install (I only started looking into this).

My documentation (Books Online) recommends not using the Network Service account, but instead use the Local Service account (I'm not sure why). The Local System account has too many privileges. It just seems to me like Microsoft recommends either local user accounts or domain user accounts.

--
Richard Mueller
MVP Directory Services
Hilltop Lab - http://www.rlmueller.net
--

"Joe Kaplan" <joseph.e.kaplan@xxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:%23lMfEQ3lJHA.5112@xxxxxxxxxxxxxxxxxxxxxxx
You'll get the best match of "Kerberos working by default" and good security by deciding to use Network Service as the service account for SQL rather than either System or a fixed domain account.

The reason for this is that when SQL is installed and configured to be run under either Network Service or System, the installer will actually add the appropriate Kerb SPNs to the domain computer account (MSSqlSvc/xxxx:1433, etc.). Thus, if SQL is access on the default port using the default name (either plain host name or FQDN) and SQL runs under either Network Service or System, then Kerb will work because the domain computer account used by the KDC to issue the service ticket will match to the local account.

Using Network Service instead of System gives you a greatly reduced attack surface as Network Service has permissions more similar to a normal local user rather than the full control of everything that System has. Network Service is also designed by default to do things like run service processes and generate audits, so you don't have to do a bunch of extra configuration to make it work as a service account.

If you really really want to use a fixed domain account for SQL and still want Kerb auth, you will need to do much of this manually (although the sec pol stuff can be configured via GPO if you are careful). You will at least have to set the appropriate SPNs. Normally the SQL install process won't have permissions to set SPNs on an arbitrary account. You also need to be very careful to remove existing SPNs from the domain computer account if you will use the same host name or port. Given that these are usually set during install, they will likely need to be removed. Duplicate SPNs will break Kerb and result in errors and failed auth attempts.

I point all this out because I sometimes see a weird bias against using Network Service when in fact it tends to do a very good job at what it is designed for and has the right default behaviors. It also avoids having to create service accounts and manage the passwords for them since no one ever needs to know the password of the Network Service account. Fixed service accounts tend to create problems from a security perspective because they frequently either have non-expiring passwords, making them much less secure, or they have expiring passwords which makes password change operations difficult to manage while still maintaining high uptime. It is a difficult thing to execute well either way.

Best of luck with whatever you end up doing!

--
Joe Kaplan-MS MVP Directory Services Programming
Co-author of "The .NET Developer's Guide to Directory Services Programming"
http://www.directoryprogramming.net
"skip" <shofmann@xxxxxxx> wrote in message news:9F3952AB-EBD1-400D-AD16-F378D0BA364D@xxxxxxxxxxxxxxxx
Hello all

The AD forest and domain are at windows 2003 native mode. The SQL DBA's are being asked to change all SQL service accounts from local system to a domain user account. My question is and if this is not the correct forum for this please politely let me know. Once the SQL service account is changed from local system to a domain user account does SQL start using kerberos authentication? Does the spn for the domain account get registerd in AD automatically? If i have a SQL cluster that has several SQL instance or virtual servers that are running on one of the pyhsical node's in the cluster, what spn gets registered in AD? I would think i would need to regsiter a SPN for the service account that is running on the SQL virtual server or instance and not the physical node?

Example physical node name is irv-idc-ms11 SQL virtual server running on physical node is irv-idc-vs11. Service account name is sqladmin. If i did a query on the service account name (sqladmin) using setspn then if this is correct the output from the command should look like

"MSSQLSvc/irv-idc-vs11"


Last question Delegation. If the SPN's are registered correctly for the service account why must i enable delegation on the service account in AD?

Many thanks for any guidance on this







.

Relevant Pages

  • Re: Delegation problems
    ... What account is running SQL? ... appropriate SPNs. ... then it depends on how you are connecting to SQL. ... I have several other servers running SQL and the SQL ...
    (microsoft.public.dotnet.framework.aspnet.security)
  • Re: Delegation problems
    ... I checked the SPNs and they appear correct on the service account. ... delegate from my web server to the SQL service on the DB server when I ...
    (microsoft.public.dotnet.framework.aspnet.security)
  • Re: kerberos SQL service accounts
    ... When you install SQL Server and specify a domain account for the mssqlserver ... My documentation recommends not using the Network Service ...
    (microsoft.public.windows.server.active_directory)
  • Re: ADAM - Domain Service Account V.S. Network Service
    ... SPNs can be set on ... The domain account would really come in to ... even though I've used the Domain Account in the past. ... any other domain/forest scenarios where Network Service does not ...
    (microsoft.public.windows.server.active_directory)
  • Re: Why must credentials be explictly given when user is already l
    ... NETWORK SERVICE is a built-in account introduced as of WinXP that it used to ... Co-author of "The .NET Developer's Guide to Directory Services Programming" ... AD, authentication is not required by default, so if you accidentally ...
    (microsoft.public.dotnet.security)
Quantcast