Analysis Services Configuration - Account Provisioning

Beginning in SQL Server 2005, significant changes were implemented to ensure that SQL Server was more secure than previous versions. Changes included a “secure by design, secure by default, and secure in deployment” strategy designed to help protect the server and its databases from security attacks.

SQL Server 2008 continues the security hardening process by introducing more changes to the server and database components. The changes introduced in SQL Server 2008 help decrease the surface and attack areas for the server and its databases by instituting a policy of least privileges and increases separation of Windows NT administration and SQL Server administration. Some of these measures include the following:

  • The ability to provision one or more Windows principals into the Sysadmin server role inside SQL Server. This option is available during SQL Server Setup for new installations of SQL Server 2008.

  • The Surface Area Configuration (SAC) tool has been removed. It was replaced by policy-based management and changes in the SQL Server Configuration Manager tool.

These changes will affect your security planning for SQL Server, and help you create a more complete security profile for your system.

Considerations for Running SQL Server 2008 on Windows Vista and Windows Server 2008

On Windows versions earlier than Windows Vista and Windows Server 2008, members of the local Administrators group do not need their own SQL Server logins and they do not have to be granted administrative rights inside SQL Server. They connect to SQL Server as the built-in server principal BUILTIN\Administrators, and they have administrative rights inside SQL Server because BUILTIN\Administrators is a member of the sysadmin fixed server role.

On Windows Vista and Windows Server 2008, these mechanisms are available only to administrative users who are running with elevated Windows permissions, which is not recommended. By default, administrators perform most actions as a standard user on Windows Vista and Windows Server 2008 operating systems. As a result, you should create a SQL Server login for each administrative user, and add that login to the sysadmin fixed server role during installation of a new instance of SQL Server 2008. You should also do this for Windows accounts that are used to run SQL Server agent jobs. These include replication agent jobs.

The following considerations apply when you install and run SQL Server 2008 on Windows Vista and Windows Server 2008.

Issues Caused by User Account Control in Windows Vista and Windows Server 2008

Windows Vista and Windows Server 2008 include a new feature, User Account Control (UAC) that helps administrators manage their use of elevated permissions. By default, on Windows Vista and Windows Server 2008 administrators do not use their administrative rights. Instead, they perform most actions as standard (non-administrative) users, temporarily assuming their administrative rights only when it is necessary.

UAC causes some known issues. For more information, see the following Web pages on TechNet:

Windows Vista and Windows Server 2008: User Account Control

Windows Vista and Windows Server 2008 User Account Control Step by Step Guide

Options

Specify SQL Server Administrators - You must specify at least one system administrator for the instance of SQL Server. To add the account under which SQL Server Setup is running, click the Current User button. To add or remove accounts from the list of system administrators, click Add or Remove and then edit the list of users, groups, or computers that will have administrator privileges for the instance of SQL Server.

When you are finished editing the list, click OK, then verify the list of administrators in the configuration dialog box. When the list is complete, click Next.