Implementing Security feature enhancements_Microsoft SQL Server 2008 R2 Administration Cookbook-QQ阅读女生短篇网

上QQ阅读APP看书，第一时间看更新

Implementing Security feature enhancements

Any data platform or database that stores sensitive information needs a robust infrastructure to control the data access in a secure manner. SQL Server 2008 R2 has inherited the 'secure-by-default' features (with several configurable features) in the areas such as platform (Operating System), architecture (32-bit and 64-bit), database objects (data layer), and application (connectivity).

In case the data is related to the financial services sector, then certain levels of criteria certification are essential for the verification of extensive security to access the layers of SQL Server, which is called Common Criteria Certification. The objective within Common Criteria Certification covers the evaluation of Information Technology (IT) products to improve security, availability, and efficiency. The various levels of security can be implemented on authentication, access privileges, database permissions, and data encryption.

The security architecture internals are classified into authentication, validation, and rights management. All of these internals are managed by three other internal objects that are called as logical entities—Principals, Permissions, and Securable:

Principals: They are the key objects that grant permissions on a database user, database role, or application role.
Permissions: They define the access rights for the Principal (or grantor) for DCL operations.
Securable: A database user is a database-level securable contained within the database that is its parent in the permission hierarchy. Principals that have CONTROL permission on a securable can grant permission on that Securable.

Further, the authentication levels on instance level database security can be managed by using logical namespace which are schemas. SQL Server 2008 R2 has a new security feature called Extended Protection (EP), which enhances the protection and handling of credentials when authenticating the network connections using Integrate Windows Authentication (IWA). This feature is introduced on Windows Server 2008 R2 and Windows 7 Operating Systems. This feature is only supported by SQL Server 2008 R2 version onwards by the SQL Server Native Client (SQLNCLI), but is not supported by the other SQL Server client providers.

The required action to implement this feature is to perform changes to the specific server and client applications, which use IWA to ensure that they opt in to this new technology. Upon installation, Extended Protection for Authentication (EPA) is controlled on the client through the use of registry keys and on the server configuration it is specific to the application. In this recipe, we will cover the elements of SQL Server security internals that will play an important role in designing the security model for your SQL Server 2008 R2 installation and multiple instance management.

Getting ready

To implement the EPA feature on an SQL Server 2008 R2 instance, it is easy to set using SQL Server Configuration Manager from the SQL Server 2008 R2 program group on the server. In order to use the EPA features on service-binding and channel-binding, the SQL Server Connection settings must be used. The configuration settings data changes when you implement this feature.

How to do it...

The following steps show how to ensure that the EP feature is used effectively on both the server and client application connections. To enable Extended Protection for the SQL Server Database engine, complete the following steps:

Go to the Start menu, choose All Programs and point to Microsoft SQL Server 2008 R2.
Open Configuration tools and click SQL Server Configuration Manager.
Expand the SQL Server Network Configuration option, which will present the protocols for installed instances.
Select the SQL Server 2008 R2 instance and then right-click on the Protocols for <SQL Server 2008 R2> instance to choose properties, as seen in the following screenshot:

The previous operation presents the properties for the selected SQL Server 2008 R2 instance. In this recipe, our aim is to set the Extended Protection feature on the instance.
As we see in the following screenshot, go to the Advanced tab to select the appropriate security feature setting:

Note

The Extended Protection value is configured on the instance level, for this recipe we will choose the two options that are available: Allowed and Required.
Choose the Allowed option to work for a client operating system that supports extended protection features. This selection is advantageous if the network is equipped with a mixed environment where the multiple operating systems are used by the client applications.
Choose the Required option to work for the client operating system that supports the extended protection feature. This selection is used to develop the most secure connection setting to secure the database server.
From the protocol's properties, click on the Flags tab. Select Yes to set Force Encryption. This option sets a self-signed certificate process that will increase security, but it does not provide protection against identity spoofing by the server.
All data transmitted across a network between SQL Server and the client application will be encrypted using the self-signed certificate.

As seen in the preceding screenshot, the two feature options—which are advanced settings to set both service-binding and channel-binding—will be affected when the selected instance is restarted.

How it works...

The Extended Protection (EP) feature is managed using the Windows Operating System's User Access Control (UAC) methods. The UAC reduces the number of programs that run with elevated privileges, thereby helping to prevent users from accidentally changing the system settings that reduce any unprecedented attacks by unknown sources. In order to manage the security validations, security internals will authenticate three areas, namely: Principals, Securables, and Permissions.

Tip

By default, the improvements from SQL Server 2008 and Windows Server 2008 operating system decrease the surface and the attack area from SQL Server with an artifact of 'least privileged' policy and additional separation of Windows Administrators and SQL Server System Administrator (SA).

By default, the local Windows group BUILTIN\Administrator is no longer allowed to access the SQL Server instance with an elevated privilege of sysadmin with that of a fixed server role on the new SQL Server 2008 R2 installations.

To avoid any interruption to the application functionality, which depends on the elevated privileges on SQL Server 2008 R2 instance using BUILTIN\Administrator local group, you must grant the permission explicitly after the installation is finished, or add the required Windows Administrators group login during the setup at the point of the Account Provisioning tab, as shown in the next screenshot:

The Extended Protection feature is enabled by default. The feature of Extended Protection for Authentication is handled by the Operating System that enhances the protection to handle the credentials when authenticating network connections by using Integrated Windows Authentication. By default, SQL Server supports the service-binding and channel-binding that reduces the service attacks.