SQL Server 2008 R2 Security Changes
Beginning in SQL Server 2005, significant changes were implemented to make sure that SQL Server is more secure than earlier versions. Changes included a "secure by design, secure by default, and secure in deployment" strategy designed to help protect the server instance and its databases from security attacks.
SQL Server 2008 introduces additional security improvements. SQL Server 2008 also takes advantage of changes in the latest operating systems from Microsoft, such as the User Account Control (UAC) found in Windows Vista and Windows Server 2008. The following improvements in SQL Server 2008 decrease the surface and attack area for SQL Server and its databases by instituting a policy of "Least Privileged" and increase the separation between the Windows Administrators and the SQL Server administrators:
By default, the local Windows Group BUILTIN\Administrator is no longer included in the SQL Server sysadmin fixed server role on new SQL Server 2008 installations.
Important
If your processes or code depend on Windows BUILTIN\Administrator local group access, you must explicitly grant permission to log on to SQL Server. Accounts are no longer automatically granted access to SQL Server because of their membership in the Windows Administrator group. Failure to include at least one user in the sysadmin role will lock you out of the instance of SQL Server. For more information, see Database Engine Configuration - Account Provisioning and Analysis Services Configuration - Account Provisioning.
The Windows groups that are created for use by the SQL Server service, such as SQLServerMSSQLUser$ COMPUTERNAME $ INSTANCENAME and SQLServerSQLAgentUser$ COMPUTERNAME $ INSTANCENAME, are no longer included in the sysadmin fixed server role. Instead, the service account that is used to start the SQL Server service and SQL Server Agent service are individually granted sysadmin rights in SQL Server. When SQL Server is installed on the Windows Server 2008 or Windows Vista operating systems, a service SID is provisioned as a member of the sysadmin fixed server role. For more information, see Setting Up Windows Service Accounts.
The Surface Area Configuration (SAC) tool has been removed, and replaced with the Policy-Based Management feature and changes in the SQL Server Configuration Manager tool.
Support for Kerberos authentication has been expanded and now includes named pipes and shared memory, in addition to TCP/IP.
These changes will affect your security planning for SQL Server, and enable you to create a more comprehensive security profile for the system.
Windows Local Group Changes
Several Windows local groups are created during the SQL Server setup process. The service accounts that were used to start SQL Server services (or service SIDs where available) are put in these local groups. These service groups are used as an access-control mechanism to access SQL Server–owned resources, and appropriate privileges or rights are granted to them during setup. Only the SQL Server service and SQL Server Agent service accounts are granted membership in the sysadmin fixed server role. For more information, see Setting Up Windows Service Accounts.
Surface Control Tools Changes
Introduced in SQL Server 2005, the Surface Area Configuration allowed you to enable or disable features which granted access to SQL Server components and configuration options.
Beginning in SQL Server 2008, the Surface Area Configuration tool has been removed. The features of the Surface Area Configuration tool that control SQL Server behavior have been replaced and greatly enhanced in the Policy-Based Management feature. This feature enables you to create policies for SQL Server components and granularly apply them to principals and roles within SQL Server. For more information about the Policy-Based Management feature, see Administering Servers by Using Policy-Based Management.
The connectivity management features of the Surface Area Configuration tool are available by using the Configuration Manager tool. For more information about this tool, see SQL Server Configuration Manager.
Kerberos Authentication
Beginning in SQL Server 2008, support for Kerberos authentication is expanded to include the named pipes and shared memory protocols. In addition, Kerberos can be used without requiring Windows Active Directory. For more information, see Kerberos Authentication and SQL Server.
Extended Protection for Authentication
Beginning with SQL Server 2008 R2 support for Extended Protection for Authentication by using channel binding and service binding is available for operating systems that support Extended Protection. For more information, see Connecting to the Database Engine Using Extended Protection.