Re: SQL Server 2005 Service Accounts Questions / Seeking Recommendations

• Previous message: Uri Dimant: "Re: how to give a user permission to modify a view definition"
• In reply to: Remus Rusanu [MSFT]: "Re: SQL Server 2005 Service Accounts Questions / Seeking Recommendations"
• Next in thread: Remus Rusanu [MSFT]: "Re: SQL Server 2005 Service Accounts Questions / Seeking Recommendations"
• Reply: Remus Rusanu [MSFT]: "Re: SQL Server 2005 Service Accounts Questions / Seeking Recommendations"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]

Date: Tue, 22 Nov 2005 13:52:12 -0500

Remus,

Thanks for this helpful information.

If I understand you correctly I have misinterpreted the docs. Regarding Sql
Service Broker,
the account running the SQL Server Service can be Local System, Network
Service, or other domain accounts.

Further, my thinking is that the account shouldn't be a machine local
account because then we have problems connecting to an AD 'as' that
identity, unless the local machine is somehow a
trusted domain to AD (though I cannot imagine how that would survive a
security audit, unless the local machine was itself some AD server, but then
you likely wouldn't want
to run SQL on it anyway).

Given then, that you run SQL Service as Local System (for example, a common
Sql Server 2000 configuration), are there any Sql Server 2005 features that
are disabled merely due
to the Service using Local System as its identity? What about Network
Service?

Thanks again,

Howard Hoffman

"Remus Rusanu [MSFT]" <Remus.Rusanu.NoSpam@microsoft.com.nowhere.moon> wrote
in message news:%23Cd7stu7FHA.3636@TK2MSFTNGP09.phx.gbl...
> Howard,
> I'm only going to address the Service Broker issues (and some EXECUTE AS
> issues, which are actually the root cause for SSB).
>
> When running as Local Service (not LocalSystem), the SQL Server cannot
> contact the active directory to get user groups membership. The EXECUTE AS
> infrastructure needs to do this when using domain Windows users/logins and
> this causes everything that uses this infrastructure to fail when domain
> Windows logins are involved. Service Broker uses this infrastructure quite
> heavily, both for activation (each time a stored proc is activated by SSB)
> and for dialog security (each time a dialog is accepted by the target
> service).
> You must understand that this is not a design fault, but a requirement.
> Failing to retrieve user information for AD can result in serious security
> problems, like not honoring DENYs.
>
> Also, Service Broker networking endpoints are unable to authenticate using
> NTLM/Kerberos authentication when running as Local Service.
> There are workarounds:
> - use certificate based authentication for broker endpoints (will use
> SChannel instead of NTLM/Kerberos)
> - use SQL logins/users for everything involving EXECUTE AS
> - use certificates for Service Broker dialog security, with the addendum
> that those certificates must be owned by SQL users.
>
> However, the recommandation is to install as an account that can connect
> to the AD. Both Localsystem and NETWORK SERVICE are fine.
>
> --
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> HTH,
> ~ Remus Rusanu
>
> SQL Service Broker
> http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx
>
>
> "Howard Hoffman" <HowardH@community.nospam> wrote in message
> news:OZF$n1r7FHA.3388@TK2MSFTNGP11.phx.gbl...
>> I've installed SQL Server 2005 Developer Edition on a couple of boxes.
>> During Setup, I selected 'Local System' and then later used SQL
>> Configuration Manager / Administrative Tools->Services to change the
>> Service accounts to a specific, non-built-in, local user.
>>
>> Due to some domain policies at my shop, I'm unable to grant these local
>> accounts the 'log on as service' right such that the right persists
>> across reboots. I work around the issue by manually starting the
>> services via Administrative Tools -> Services after a reboot (this is
>> quite a pain).
>>
>> If I had created this local account prior to running SQL Server 2005
>> Setup, would Setup have been able to permanently grant that account the
>> 'Log On As System' right?
>>
>> Are there existing articles or white papers that discuss why MS
>> recommends (or requires) the Service accounts to be non-built-in
>> accounts? For example, I think I read somewhere that the Service Broker
>> feature cannot be enabled if the SQL Server service *is* running in Local
>> System. I'm aware of the MSDN articles here
>> (http://msdn2.microsoft.com/en-us/library/ms176021.aspx) and here
>> (http://msdn2.microsoft.com/en-us/library/ms143504.aspx).
>>
>> Thanks in advance,
>>
>> Howard Hoffman
>>
>>
>>
>>
>>
>
>

• Previous message: Uri Dimant: "Re: how to give a user permission to modify a view definition"
• In reply to: Remus Rusanu [MSFT]: "Re: SQL Server 2005 Service Accounts Questions / Seeking Recommendations"
• Next in thread: Remus Rusanu [MSFT]: "Re: SQL Server 2005 Service Accounts Questions / Seeking Recommendations"
• Reply: Remus Rusanu [MSFT]: "Re: SQL Server 2005 Service Accounts Questions / Seeking Recommendations"
• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ] [ attachment ]