RE: Win2000 OS Access for SQL Server Admin

From: Mateo (anonymous_at_discussions.microsoft.com)
Date: 03/08/04 

Date: Mon, 8 Mar 2004 13:11:02 -0800

Kevin,

Thanks for the KB reference. I found it useful. I hope
you don't mind answering a couple of other questions.

We have a Win2K server running SQL Server 2000 w/SP3. The
SQL Services are started using the Local System account.
For security reasons, we removed the
BUILTIN\Administrators group. We didn't have any issues
until recently when we added another SQL Server instance.
We also removed the BUILTIN\Administrators group from
this 2nd instance as well. The server was rebooted this
weekend and the SQL Server agent did not startup for the
2nd instance with the following error "[298] SQLServer
Error: 18456, Login failed for user 'NT
Authority\System'. [SQLSTATE 28000]". Per the KB article,
I granted [NT Authority\System] sysadmin privileges and
the SQL Server agent now starts up successfully. I
noticed the SQL Agent Generic Refresher and Alert Engine
are running under the SA account in the 1st instance. In
the 2nd instance, they're running under the System
account.

My questions are, why did this service fail to start and
yet the other SQL services start successfully before
granting the Administrators or the System account
sysadmin privileges? Also, what are the security risks
with granting System sysadmin privileges vs creating a
local windows account and using this to startup the
services? I'd like to use the most secure method.

Thanks for your help,
Mateo, DBA

>-----Original Message-----
>It's difficult to Truly seperate a NT admin from a SQL
Admin. By default
>all NT admins are members of the SQL Sysadmins group.
>You can change this behavior though.
>
>
>See this topic: " Remove the BUILTIN\Administrators
Server Login" on the
>following web site.
>http://www.microsoft.com/technet/prodtechnol/sql/2000/mai
ntain/sp3sec02.mspx
>#XSLTsection127121120120
>
>By default SQL Sysadmins have access to xp_cmdshell
which allows them to
>shell out to the OS. Is this the issue that you are
concerned with?
>
>
>Thanks,
>
>Kevin McDonnell
>Microsoft Corporation
>
>This posting is provided AS IS with no warranties, and
confers no rights.
>
>
>
>.
>

Relevant Pages

  • Re: SQL SERVER AGENT NOT STARTING
    ... The SQL Agent runs under the domain admins account. ... starts and stops running but all other SQL services are running. ... i try to start my SQL SERVER AGENT. ...
    (microsoft.public.sqlserver.server)
  • New Paper: Microsoft SQL Server Passwords
    ... I've written a paper on how users' passwords, or rather their hashes, are ... stored in Microsoft's SQL Server. ... be able to access the SQL Services. ...
    (Bugtraq)
  • Re: configuring sql mail
    ... Are your SQL services running under the domain account ... create an Exchange mailbox for the domain account ... >called sqlmail which I am able to easily log into and send mail through. ...
    (microsoft.public.sqlserver.setup)
  • Re: Very slow Responding SQL2000
    ... all functionality is fine except SQL.. ... Execute the select statements locally in the ... > How did you confirm that SQL Server is very slow? ... Our SQL services are getting ...
    (microsoft.public.sqlserver.server)
  • Re: Creating a new Database Engine
    ... Select SQL Server Configuration manager from SQL Server 2005 program groups. ... There choose Sql Services option and see if SQLServer service ... you may need to run the SQL 2005 setup program and install database engine. ...
    (microsoft.public.sqlserver.setup)