Managing and Monitoring SQL Server 2000 Security

Posted on
Share on Google+Share on LinkedInShare on FacebookShare on RedditTweet about this on TwitterEmail this to someone

These questions are derived from the Self-Test Software Practice Test for Exam # 70-228 – Installing, Configuring, and Administering Microsoft SQL Server 2000 Enterprise Edition.

 

Objective: Managing and Monitoring SQL Server 2000 Security
SubObjective: Configure mixed security modes or Windows Authentication

 

(Single Answer, Multiple Choice)

 

You administer a Windows NT 4.0 network that consists of an account domain named Central and multiple resource domains that trust the Central domain. You install SQL Server 2000 on a Windows 2000 Server computer that is located in the Sales resource domain. The MSSQLServer and SQLServerAgent services are assigned to run under the security context of a user account from the account domain. Later, you notice that the services are not starting.

What should you do?

 

 

  1. Assign the Log on as a service user right on the PDC for the Central domain.
  2. Assign the Log on as a service user right on the SQL Server computer.
  3. Assign the Log on as a service user right on the PDC for the Sales domain.
  4. Assign the Act as part of the operating system user right on the PDC for the Central domain.
  5. Assign the Act as part of the operating system user right on the SQL Server computer.
  6. Assign the Act as part of the operating system user right on the PDC for the Sales domain.

 


Answer:
B. Assign the Log on as a service user right on the SQL Server computer.

Tutorial:
The MSSQLServer and SQLServerAgent services must run under the security context of a user account that has the Log on as a service user right. This user right is applied at the computer level and can be assigned to any user account that is visible to that computer. This user right must be assigned on the SQL Server computer. If you were to assign the Log on as a service user right on a domain controller, then the user account would have this right on all domain controllers in that domain. In this scenario, SQL Server 2000 has been installed on a member server in the Sales domain; therefore, any user account that resides in the same domain or in any trusted domain, including Central, is visible to the SQL Server computer and can be assigned the Log on as a service user right on that computer.

If users who are not members of the sysadmin fixed server role are required to run the xp_cmdshell extended stored procedure, then you should assign the Act as part of the operating system user right to the account used by the MSSQLServer service. However, this right is not necessary for the services to start. Membership in the local Administrators group is not a requirement for the MSSQLServer service in a Windows NT 4.0 domain. However, membership in the local Administrators group is required by the SQLServerAgent service in order to enable users other than sysadmin members to create CmdExec and ActiveScript jobs, to use autorestart and to use run-when-idle jobs. Membership in the Domain Admins group is not required at all. By default, members of the Domain Admins group are assigned to the local Administrators group on each computer in a domain.

You can configure the services to run under the security context of the local System account; this account is assigned all the necessary rights to enable the services to run. However, the local System account does not have network access rights in Windows NT domains. Therefore, in Windows NT domains, you should configure the MSSQLServer and SQLServerAgent services to run under the security context of the local System account only if SQL Server will not be communicating with instances of SQL Server on other computers.

Generally, the assignment of the Log on as a service user right to the domain user account is transparent to an administrator. Failure of the MSSQLServer and SQLServerAgent services to start may occur when the Log on as a service user right is manually revoked. In a Windows NT 4.0 network, the services may fail to start when a SQL Server computer is moved from one domain to another domain that is not trusted. The domain user account that is used by the MSSQLServer and SQLServerAgent services then becomes invalid on the SQL Server computer.

Reference:
1. SQL Server Books Online – Contents – Installing SQL Server – Overview of Installing SQL Server 2000- Setting up Windows Services Accounts

2. SQL Server Books Online – Contents – Installing SQL Server – Basic Installation Options – Services Accounts

3. SQL Server Books Online – Contents – Troubleshooting – Server and Database Troubleshooting – Troubleshooting MSSQLServer or SQLServerAgent Services User Accounts

Share on Google+Share on LinkedInShare on FacebookShare on RedditTweet about this on TwitterEmail this to someone
cmadmin

ABOUT THE AUTHOR

Posted in Archive|

Comment:

Leave a comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>