kerberos SQL service accounts

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

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@kbb.com> wrote in message
news:9F3952AB-EBD1-400D-AD16-F378D0BA364D@microsoft.com...
> 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
>
>
>

Also, on the delegation question, you only need to enable delegation on the
SQL service account if SQL will be making a call to a remote system on
behalf of a remote user it is impersonating.

It is much more common to have a web front end require delegation rights to
allow it to query SQL on a user's behalf, but it is certainly possible that
SQL itself might make such a remote call to another backend.

I suggest you make sure they tell you what they need to delegate to. You
can enforce this in your own policy by using constrained delegation
exclusively (you are 2003 native, so this is available). Constrained
delegation says that a service can only delegate to other specific services
(based on the SPN of the target). It is much more secure than unconstrained
delegation (the only method available in Win2K) and is also self-documenting
in that once you have configured it, you are certain to know exactly what
services are delegating to what other services and are certain that they
cannot delegate to anything but those services.

In some cases, unconstrained my really be necessary, but in most cases
constrained should be possible. Make them figure what they need to delegate
to.

--
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@kbb.com> wrote in message
news:9F3952AB-EBD1-400D-AD16-F378D0BA364D@microsoft.com...
> 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
>
>
>

thank you very much for the explanation it was most helpful. I have question
regarding contrained delegation. We do have SQL servers that need to make a
remote call to another SQL server, both SQL servers in question are running
there services as a domain user account. If i want to use constrained
delegation, i know i set it up on the service account, but what account do i
point the service account to use for contrained delegation? is it the other
service account that is on running on other seperate SQL server?
"Joe Kaplan" <joseph.e.kaplan@removethis.accenture.com> wrote in message
news:uuZVOT3lJHA.2064@TK2MSFTNGP05.phx.gbl...
> Also, on the delegation question, you only need to enable delegation on
> the SQL service account if SQL will be making a call to a remote system on
> behalf of a remote user it is impersonating.
>
> It is much more common to have a web front end require delegation rights
> to allow it to query SQL on a user's behalf, but it is certainly possible
> that SQL itself might make such a remote call to another backend.
>
> I suggest you make sure they tell you what they need to delegate to. You
> can enforce this in your own policy by using constrained delegation
> exclusively (you are 2003 native, so this is available). Constrained
> delegation says that a service can only delegate to other specific
> services (based on the SPN of the target). It is much more secure than
> unconstrained delegation (the only method available in Win2K) and is also
> self-documenting in that once you have configured it, you are certain to
> know exactly what services are delegating to what other services and are
> certain that they cannot delegate to anything but those services.
>
> In some cases, unconstrained my really be necessary, but in most cases
> constrained should be possible. Make them figure what they need to
> delegate to.
>
> --
> 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@kbb.com> wrote in message
> news:9F3952AB-EBD1-400D-AD16-F378D0BA364D@microsoft.com...
>> 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
>>
>>
>>
>

It goes like this:

Given service with host name "SQL1" configured to run under account
SERVICEACC1 needs to make a remote call to another SQL box with host name
"SQL2" with SQL running under domain account SERVICEACC2 using delegation
(on behalf on an impersonated caller):

On the account SERVICEACC1 (which should have an SPN like MSSqlSvc/SQL1:1433
and possibly an FQDN version as well), it should have "Trusted for
delegation" "to specific services" with MSSqlSvc/SQL2:1433 as the target.
It will look basically like that in ADUC. Note that ADUC only shows the
delegation tab for accounts that have a the servicePrincipalName attribute
set.

In LDP, you'll see that that SERVICEACC1 has:
servicePrincipalName: MSSqlSvc/SQL1:1433
userAccountControl includes flag "UF_TRUSTED_FOR_DELEGATION"
msds-AllowedToDelegateTo has "MSSqlSvc/SQL2:1433"

SERVICEACC1 will just have:
servicePrincipalName: MSSqlSvc/SQL2:1433

Since it is not delegating to anything, it does not need to be trusted for
delegation or have an "allowed to delegate to" attribute.

Make sure that only one account in the entire forest has
servicePrincipalName equal to either of those SPNs or you will have Kerb
errors.

HTH!

--
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@kbb.com> wrote in message
news:Oxbkpm3lJHA.4252@TK2MSFTNGP02.phx.gbl...
> thank you very much for the explanation it was most helpful. I have
> question regarding contrained delegation. We do have SQL servers that need
> to make a remote call to another SQL server, both SQL servers in question
> are running there services as a domain user account. If i want to use
> constrained delegation, i know i set it up on the service account, but
> what account do i point the service account to use for contrained
> delegation? is it the other service account that is on running on other
> seperate SQL server?
> "Joe Kaplan" <joseph.e.kaplan@removethis.accenture.com> wrote in message
> news:uuZVOT3lJHA.2064@TK2MSFTNGP05.phx.gbl...
>> Also, on the delegation question, you only need to enable delegation on
>> the SQL service account if SQL will be making a call to a remote system
>> on behalf of a remote user it is impersonating.
>>
>> It is much more common to have a web front end require delegation rights
>> to allow it to query SQL on a user's behalf, but it is certainly possible
>> that SQL itself might make such a remote call to another backend.
>>
>> I suggest you make sure they tell you what they need to delegate to. You
>> can enforce this in your own policy by using constrained delegation
>> exclusively (you are 2003 native, so this is available). Constrained
>> delegation says that a service can only delegate to other specific
>> services (based on the SPN of the target). It is much more secure than
>> unconstrained delegation (the only method available in Win2K) and is also
>> self-documenting in that once you have configured it, you are certain to
>> know exactly what services are delegating to what other services and are
>> certain that they cannot delegate to anything but those services.
>>
>> In some cases, unconstrained my really be necessary, but in most cases
>> constrained should be possible. Make them figure what they need to
>> delegate to.
>>
>> --
>> 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@kbb.com> wrote in message
>> news:9F3952AB-EBD1-400D-AD16-F378D0BA364D@microsoft.com...
>>> 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
>>>
>>>
>>>
>>
>

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@removethis.accenture.com> wrote in message
news:%23lMfEQ3lJHA.5112@TK2MSFTNGP03.phx.gbl...
> 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@kbb.com> wrote in message
> news:9F3952AB-EBD1-400D-AD16-F378D0BA364D@microsoft.com...
>> 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
>>
>>
>>
>

That makes perfect sense. Thank You!
"Joe Kaplan" <joseph.e.kaplan@removethis.accenture.com> wrote in message
news:uRf5ft3lJHA.5028@TK2MSFTNGP04.phx.gbl...
> It goes like this:
>
> Given service with host name "SQL1" configured to run under account
> SERVICEACC1 needs to make a remote call to another SQL box with host name
> "SQL2" with SQL running under domain account SERVICEACC2 using delegation
> (on behalf on an impersonated caller):
>
> On the account SERVICEACC1 (which should have an SPN like
> MSSqlSvc/SQL1:1433 and possibly an FQDN version as well), it should have
> "Trusted for delegation" "to specific services" with MSSqlSvc/SQL2:1433 as
> the target. It will look basically like that in ADUC. Note that ADUC only
> shows the delegation tab for accounts that have a the servicePrincipalName
> attribute set.
>
> In LDP, you'll see that that SERVICEACC1 has:
> servicePrincipalName: MSSqlSvc/SQL1:1433
> userAccountControl includes flag "UF_TRUSTED_FOR_DELEGATION"
> msds-AllowedToDelegateTo has "MSSqlSvc/SQL2:1433"
>
> SERVICEACC1 will just have:
> servicePrincipalName: MSSqlSvc/SQL2:1433
>
> Since it is not delegating to anything, it does not need to be trusted for
> delegation or have an "allowed to delegate to" attribute.
>
> Make sure that only one account in the entire forest has
> servicePrincipalName equal to either of those SPNs or you will have Kerb
> errors.
>
> HTH!
>
> --
> 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@kbb.com> wrote in message
> news:Oxbkpm3lJHA.4252@TK2MSFTNGP02.phx.gbl...
>> thank you very much for the explanation it was most helpful. I have
>> question regarding contrained delegation. We do have SQL servers that
>> need to make a remote call to another SQL server, both SQL servers in
>> question are running there services as a domain user account. If i want
>> to use constrained delegation, i know i set it up on the service account,
>> but what account do i point the service account to use for contrained
>> delegation? is it the other service account that is on running on other
>> seperate SQL server?
>> "Joe Kaplan" <joseph.e.kaplan@removethis.accenture.com> wrote in message
>> news:uuZVOT3lJHA.2064@TK2MSFTNGP05.phx.gbl...
>>> Also, on the delegation question, you only need to enable delegation on
>>> the SQL service account if SQL will be making a call to a remote system
>>> on behalf of a remote user it is impersonating.
>>>
>>> It is much more common to have a web front end require delegation rights
>>> to allow it to query SQL on a user's behalf, but it is certainly
>>> possible that SQL itself might make such a remote call to another
>>> backend.
>>>
>>> I suggest you make sure they tell you what they need to delegate to.
>>> You can enforce this in your own policy by using constrained delegation
>>> exclusively (you are 2003 native, so this is available). Constrained
>>> delegation says that a service can only delegate to other specific
>>> services (based on the SPN of the target). It is much more secure than
>>> unconstrained delegation (the only method available in Win2K) and is
>>> also self-documenting in that once you have configured it, you are
>>> certain to know exactly what services are delegating to what other
>>> services and are certain that they cannot delegate to anything but those
>>> services.
>>>
>>> In some cases, unconstrained my really be necessary, but in most cases
>>> constrained should be possible. Make them figure what they need to
>>> delegate to.
>>>
>>> --
>>> 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@kbb.com> wrote in message
>>> news:9F3952AB-EBD1-400D-AD16-F378D0BA364D@microsoft.com...
>>>> 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
>>>>
>>>>
>>>>
>>>
>>
>

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@ameritech.nospam.net> wrote in
message news:OUmUY%233lJHA.1292@TK2MSFTNGP02.phx.gbl...
> 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@removethis.accenture.com> wrote in message
> news:%23lMfEQ3lJHA.5112@TK2MSFTNGP03.phx.gbl...
>> 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@kbb.com> wrote in message
>> news:9F3952AB-EBD1-400D-AD16-F378D0BA364D@microsoft.com...
>>> 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
>>>
>>>
>>>
>>
>
>