Re: SQL Server 2005 Service Accounts Questions / Seeking Recommendations

From: Remus Rusanu [MSFT] (Remus.Rusanu.NoSpam_at_microsoft.com.nowhere.moon)
Date: 11/21/05

  • Next message: Mr Curious: "Enterprise Manager - problem" 
    Date: Mon, 21 Nov 2005 14:32:26 -0800

    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
>
>
>
>
>
  • Next message: Mr Curious: "Enterprise Manager - problem"

    Relevant Pages

    • Re: Cross-database execution permissions with certificates and sch
      ... section that describes the perils of using "EXECUTE AS OWNER", ... You cannot accuse for knowing too much about Service Broker, ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
      (microsoft.public.sqlserver.security)
    • Re: Install Service Broker
      ... Service Broker is tightly integrated into the SQL Server engine and always ... > Database Service, Analysis Service, Report Service, Nitification Service, ... > However, I don't see an install option for installing Service Broker, why? ...
      (microsoft.public.sqlserver.setup)
    • Re: Install Service Broker
      ... It is part of the SQL Server service so it doesn't show up as ... >> However, I don't see an install option for installing Service Broker, ... Because it contains in Database Service? ...
      (microsoft.public.sqlserver.setup)
    • RE: Catch a trigger in .net?
      ... You can have the trigger fire off a process that adds the new request to a ... In SQL Server 2005, you also have the Service Broker, which gives you ... but I'm wondering if it is possible to catch a SQL Server ... > trigger as an event in a vb .net app? ...
      (microsoft.public.dotnet.framework.adonet)
    • Re: SQL Server 2005 Service Accounts Questions / Seeking Recommendations
      ... the account running the SQL Server Service can be Local System, ... Given then, that you run SQL Service as Local System (for example, a common ... > I'm only going to address the Service Broker issues (and some EXECUTE AS ...
      (microsoft.public.sqlserver.security)