May 29, 2003 at 8:49 am
I tried this morning your interesting security test.
I get three wrong questions among them are two related to the account that starts SQL Server.
In the answer, it's said
"The account that starts SQL Server must have a login in SQL Server with sysadmin rights.
If the account hasn't been created as a login in SQL Server, the service will not start."
That's not true.
It's so easy to prove it.
1) Remove ou deny access to BUILTIN\Administrators account.
2)Create a new windows account
Give to this account the following privileges
-Log on as a service
-Full access on mssqlserver registry key
3)You will notice that's not problem to start SQL/Server with this account.
May 29, 2003 at 11:53 am
I removed the BUILTIN\Administrators account and I was able to start SQL Server service with local system account. Something is not correctly formulated in the question or in the answer?
May 29, 2003 at 12:14 pm
From BOL.
"Requirements for Domain User Account
All domain user accounts must have permission to:
Access and change the SQL Server directory (\Program Files\Microsoft SQL Server\Mssql).
Access and change the .mdf, .ndf, and .ldf database files.
Log on as a service.
Read and write registry keys at and under:
HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer.
-or- for any named instance: HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server.
HKEY_LOCAL_MACHINE\System\CurrentControlset\Services\MSSQLServer.
-or- for any named instance: HKEY_LOCAL_MACHINE\System\CurrentControlset\Services\MSSQL$Instancename.
HKEY_LOCAL_MACHINE\Software\Microsoft\Windows NT\CurrentVersion\Perflib.
In addition, a domain user account must be able to read and write corresponding registry keys for these services: SQLAgent$InstanceName, MSSearch, and MSDTC.
This table shows additional permissions required for certain functionality.
Service Permission Functionality
SQL Server Network write privileges Write to a mail slot using xp_sendmail.
SQL Server Act as part of operating system and replace a process level token Run xp_cmdshell for a user other than a SQL Server administrator.
SQL Server Agent Member of the Administrators local group Create CmdExec and ActiveScript jobs belonging to someone other than a SQL Server administrator.
Use the autorestart feature.
Use run-when-idle jobs.
SQL Server Member of local Power Users or local Administrators group Add and delete SQL Server objects in the Windows 2000 Active Directory."
bouchenafa,
It seems you are right about the account that can be used to start SQL Server doesn't have to be sysadmin. But sysadmin fixed server role is used to performs any activity in SQL Server. If you don't assign sysadmin to the domain user service account, your SQL Server might not be function properly. For example, if you use same account to start SQL Server Agent service, the Agent will not be started. Full-Text search is another example. And also just giving 'logon as a service and full access to registry' is insufficient according to the BOL.
xmas__,
Surely you can use local system account to start SQL Server services. As I mentioned above, without adding 'NT Authority\System' with sysadmin right after removing 'BUILTIN\Administrators', your SQL Server Agent wouldn't be started and full-text will not be populated.
So in order to make SQL Server function properly, The account that starts SQL Server services must have a login in SQL Server with sysadmin rights.
Edited by - Allen_Cui on 05/29/2003 12:16:12 PM
May 29, 2003 at 1:39 pm
I agree that the SQL Agent account needs to to log in SQL/Server but the account starting SQL/Server doesn't have this need.
It's only used to start SQL/Server and doesn't have anything to do within SQL/Server
I mean this account does not only to be member of sysadmin role but does not need any login in SQL/Server at all
Edited by - bouchenafa on 05/29/2003 1:47:22 PM
May 29, 2003 at 2:46 pm
It seems you are right the account exclusivly used to start SQL Server doesn't have to be in SQL Server login. If you remove/deny 'BUILTIN\Administrators' from SQL Server login, full-text will not function properly unless 'NT Authority\System' is granted to access SQL Server with 'sa' right.
I am not sure whether SQL Mail works if you do not grant SQL Server service account to access SQL Server. Some other functions too such as log shipping, replication, linked server etc ......
Edited by - Allen_Cui on 05/29/2003 5:55:03 PM
Edited by - Allen_Cui on 05/29/2003 5:58:24 PM
May 29, 2003 at 7:09 pm
definitely the question was wrong judged and the answer was not complete. or the question was not clear enough.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply