When you install SQL Server on server operating systems, SQL Server makes several modifications to the environment. These modifications include new system services, integrated authentication, new domain/workgroup accounts, and registry updates.
When you install SQL Server on Windows, several services are installed on the server. These services include:
Active Directory Helper. MSSQLServerADHelper adds and removes objects used to register SQL Server and Analysis Server instances. It also updates object permissions related to SQL Server service accounts.
Analysis Services. The Microsoft SQL Server Analysis Services are used for OLAP and data mining. For the default database instance, this service is named Analysis Services (MSSQLSERVER). When multiple instances of SQL Server are installed, you will also see MSSQLSERVER$ instancename, where instancename is the name of the SQL Server instance.
Distributed Transaction Coordinator. The Distributed Transaction Coordinator service coordinates distributed transactions between two or more database servers.
Microsoft Search. The Full-Text Engine for SQL (MSFTESQL) creates full-text indexes and is used with full-text searches on databases. This feature is only available when full-text search is installed as a custom component.
Report Server. The Microsoft Reporting Services create, manage, and deliver reports. For the default database instance, this service is named Report Server (MSSQLSERVER). When multiple instances of SQL Server are installed, you will also see MSSQLSERVER$ instancename, where instancename is the name of the SQL Server instance.
SQL Browser. The SQL browser provides connection details and information to clients.
SQL Server Agent. The SQL Server Agent service is used with scheduling and alerting. For the default database instance, this service is named SQL Server Agent (MSSQLServer). When multiple instances of SQL Server are installed, you will also see SQLAgent$instancename, where instancename is the name of the SQL Server instance.
SQL Server. The SQL Server service is the primary database service. For the default database instance, this service is named SQL Server (MSSQLServer). When multiple instances of SQL Server are installed, you will also see MSSQL$instancename, where instancename is the name of the SQL Server instance.
Note
You will learn more about managing services and configuring service-related options in Chapter 5.
SQL Server security is completely integrated with Windows domain security, allowing for authentication based on user and group memberships as well as standard SQL Server user accounts. These authentication techniques make it much easier to manage access and security. You can:
Combine Windows and SQL Server authentication so users in Windows domains can access the server using a single account and other users can be logged on using a SQL Server login ID.
Use authentication based on Windows domain accounts only, so only users with a domain account can access the server.
When using Windows 2000 and Windows Server 2003, SQL Server services can be configured to log on as Local System or to use Windows logon accounts. There are advantages and disadvantages to each of these techniques:
Local system accounts. This option provides administrative privileges to SQL Server on the local system but no privileges on the network. If the server requires resources on the local server only, use the local system account. Use local system accounts when you want to isolate SQL Server and restrict it from interacting with other servers.
Domain accounts. This option sets the service to use a standard domain account with privileges you configure. Use domain accounts when the server requires resources across the network, when you need to forward events to the application logs of other systems, and when you want to configure e-mail or pager notification.
Any domain user accounts used with SQL Server services must have permission to perform the following tasks:
Additional requirements for the SQL Server (MSSQLSERVER) and SQL Server Agent (MSSQLSERVER) services are as follows:
For SQL Server (MSSQLSERVER) to add and delete SQL Server objects in Active Directory, the service must be a member of the local Power Users or local Administrators group.
For SQL Server (MSSQLSERVER) to run xp_cmdshell for a user other than a SQL Server administrator, the service needs the Act As Part Of Operating System and Replace A Process Level Token privileges.
For SQL Server (MSSQLSERVER) to write to a mail slot using xp_sendmail, the service needs the appropriate network write privileges.
For SQL Server Agent (MSSQLSERVER) to use the autorestart feature or create CmdExec and ActiveScript jobs belonging to someone other than a SQL Server administrator, the service must be a member of the Administrators local group.
Note
Security in SQL Server is managed through logins, server roles, database access permissions, and object permissions. Windows domain accounts can be used for user authentication and log in to SQL Server. You can, for example, specify a Windows account to use for authentication and to log on to SQL Server. You will learn more about SQL Server logins, server roles, and security in Chapter 8.