New and Enhanced Features - SQL Server 2012 (What's New in SQL Server 2012) - Part 3

Management and Security

SQL 2012 is a big step forward for Microsoft Corporation - the company is positioning itself to be a leader in availability and in the growing area of big data. 

SQL Server 2008 R2 came equipped with numerous capabilities for organizations to leverage including, Transparent Data Encryption (TDE) to protect data at rest, Extensible Key Management (EKM) to fulfill data and key separation, Kerberos Authentication to achieve the strongest authentication, and SQL Server Audit, Policy-Based Management, and change data capture for fulfilling compliance requirements.

SQL Server 2008 R2 was unquestionably robust from a security perspective and a leader in the database-platform industry, with the least amount of vulnerabilities and least amount of security patches required to maintain the system.

SQL Server 2012 increases SQL Server’s popularity as it delivers several security enhancements to help organizations improve their control of data access while maintaining the highest level of data protection and compliance.

1.      User-Defined Server Roles: DBAs have always had the ability to create custom database role, but never server wide. For example, if the DBA wanted to give a development team read/write access to every database on a shared server, traditionally the only ways to do it were either manually, or using undocumented procedures. Neither of which were good solutions.

Now, the DBA can create a role, which has read/write access on every DB on the server, or any other custom server wide role.

2.      Distributed Replay: Once again this is answer to a feature that Oracle released (Real Application Testing). However, and in my opinion where the real value proposition of SQL Server is, in Oracle it is a (very expensive) cost option to Enterprise Edition.

With SQL, when you buy your licenses for Enterprise Edition, you get everything. Distributed replay allows you to capture a workload on a production server, and replay it on another machine. This way changes in underlying schemas, support packs, or hardware changes can be tested under production conditions.

3.     Enhanced Auditing Features: Audit is now available in all editions of SQL Server. Additionally, users can define custom audit specifications to write custom events into the audit log. New filtering features give greater flexibility in choosing which events to write to the log.

4.     Contained Database Authentication: Moving databases from one instance of the SQL Server Database Engine to another instance of the Database Engine is simplified by introducing contained databases. Users in a contained database are no longer associated with logins on the instance of SQL Server. Many other dependencies on the instance are also removed.

For more information, visit the link: http://msdn.microsoft.com/en-us/library/ff929071.aspx.

A fully contained database includes all the settings and metadata required to define the database and has no configuration dependencies on the instance of the SQL Server Database Engine where the database is installed.

In previous versions of SQL Server, separating a database from the instance of SQL Server could be time consuming and required detailed knowledge of the relationship between the database and the instance of SQL Server.

Partially contained databases in SQL Server 2012 make it easier to separate a database from the instance of SQL Server and other databases.

5.       Default Schema for Window Group: You can now define a default schema for a Windows group. When an object is created by a Windows user and when a default schema is not specified, SQL Server no longer automatically creates a schema.

By assigning default schemas to Windows groups, organizations can simplify database schema administration and database schema management. Equally important, the possibility of delegating a schema to the wrong users is frustrated when users change groups. On the flip side, if an incorrect schema is used, query errors are prevented with the new default schema for groups. Finally, unnecessary implicit schema creation is prevented.

A frequent question is raised when discussing schemas. What happens if users are affiliated with more than one Windows group? If no default schema is defined for a user account, SQL Server reviews the sys.principal table and chooses the group with the lowest principal ID as the default schema.

6.       Audit Enhancement: With more and more organizations governed by some form of regulatory compliance, the SQL Server product group responsible for security, decided to invest in and enhance the existing audit capabilities affiliated with servers and databases. Enhancements are seen in the following areas:

• Audit supported on all SKUs
• Improved resilience
• User-defined audit event
• Record filtering
• T-SQL stack information

7.       New Permissions: There are 19 new permissions available in the Database Engine.

To see all permissions execute the following statement: 

SELECT * FROM sys.fn_builtin_permissions('');

• A Securable is a resource that someone might want to access (like the HR Folder).
• A Principal is anything that might want to gain access to the securable (like Hari).
• A Permission is the level of access a principal has to a securable (like Read).

8.       Cryptography Changes: With time security become one of the major part for any organization, now organizations want highest forms of security when it comes to using encryption to protect their data.

With SQL Server 2012, the product group responsible for security has greatly enhanced SQL Server cryptography; therefore, organizations can deploy SQL Server with the highest level of confidence when achieving compliance. The major cryptography enhancements include the following:

• Advanced Encryption Standard (AES): AES is a specification for encryption that supersedes DES as the industry standard. SQL Server 2012 uses the AES encryption algorithm to protect the service master key (SMK) and the database master key (DMK). AES is a newer encryption algorithm than 3DES used in earlier versions.

After upgrading an instance of the Database Engine to SQL Server 2012 the SMK and DMK should be regenerated in order to upgrade the master keys to AES.

• Certificate Key Length: When creating certificates, the maximum length of private keys imported from an external source is expanded from 3456 bits to 4096 bits.
• Hashing Algorithms: In cryptography, SHA-2 is a set of cryptographic hash functions developed by the National Security Agency (NSA). With regard to SQL Server 2012, the HASHBYTES function now supports the SHA2_256 and SHA2_512 algorithms.

Note: The RC4 algorithm is only supported for backward compatibility. New material can only be encrypted using RC4 or RC4_128 when the database is in compatibility level 90 or 100. (Not recommended.) Use a newer algorithm such as one of the AES algorithms instead. In SQL Server 2012 material encrypted using RC4 or RC4_128 can be decrypted in any compatibility level.

• Binary Support: You can create certificates from bytes when using the Transact-SQL CREATE CERTIFICATE procedure. The FROM BINARY option allows you to specify the binary description of an ASN-encoded certificate.

9.   Tight Integration with SharePoint and Active Directory: SharePoint and SQL Server are two tightly-coupled technologies to deliver business productivity, business intelligence, and reports to organizations.

New SharePoint and Active Directory security modules have been introduced to better secure end-user reports shared and published in SharePoint.

Enhanced security models provide control at row and column levels and allow organizations the ability to better achieve the following:

• Enforce password policies
• Use roles and proxy accounts
• Provide security-enhanced metadata access
• Enhance security features with execution context

10.   Database Authentication Enhancements: When working with previous versions of SQL Server, a user needed to log in within the Database Engine to authenticate to a database.

The login could be a Windows User account, Windows group account, or a SQL Server account. At times, this dependency caused authentication issues - especially with database portability.

For example, if a database was migrated or failed over from one SQL Server instance (source) to another SQL Server instance (target), a database administrator had to ensure that all logins on the source SQL Server instance existed on the target SQL Server instance. If the login did not exist on the target instance, a user, group, or application would no longer be able to authenticate

SQL Server 2012 addresses these authentication and login dependency challenges by introducing Contained Database Authentication to enhance compliance, authorization, and portability of user databases. Contained Database Authentication allows users to be authenticated directly into a user database without logins that reside in the Database Engine. This feature facilitates better portability of user databases among servers because contained database have no external dependencies.

Contained Database Authentication is a server wide property and is very straightforward to enable. It can be enabled and disabled via the Advanced Server Properties page in SQL Server Management Studio or with Transact-SQL.

Creating Users: As mentioned earlier, a contained database is an excellent way to decouple the user and database from the SQL Server Database Engine; therefore, an organization can achieve database portability by moving databases between instances of SQL Server.

Note there are some special considerations for contained databases. For example, when connecting to a contained database, if the user does not have a login in the master database, the connection string must include the contained database name as the initial catalog. The initial catalog parameter is always required for a contained database user with a password.

11.  Resource Governor Enhancement: The enhancements to the Resource Governor enable you to more effectively govern performance in multi-tenancy environments like private cloud.

       The enhancements include support for 64 resource pools, greater CPU usage control, and resource pool affinity for           partitioning of physical resources and predictable resource allocation.