Security Enhancements (Database Engine)
Security enhancements in the SQL Server Database Engine include provisioning during setup, new SEARCH PROPERTY LIST permissions, new user-defined server roles, and new ways of managing server and database roles.
Provisioning During Setup
To enhance role separation, BUILTIN\administrators and Local System (NT AUTHORITY\SYSTEM) are not automatically provisioned in the sysadmin fixed server role. Local administrators can still access the Database Engine when in single user mode.
SQL Server now supports Managed Service Accounts and Virtual Accounts when installed on Windows 7 or Windows Server 2008 R2. For more information, see Configure Windows Service Accounts and Permissions.
The protection of operating services under a per-service SID is now extended to all operating systems. For more information, see Configure Windows Service Accounts and Permissions.
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('');
The new permissions are as follows:
New GRANT, DENY, and REVOKE permissions to CONTROL/VIEW DEFINTION/TAKE OWNERSHIP/REFERENCES/ALTER on a search property list are available.
New GRANT, DENY, and REVOKE permissions to ALTER ANY SERVER ROLE, CREATE SERVER ROLE, and CONTROL/VIEW DEFINTION/TAKE OWNERSHIP/ALTER on a server role.
New GRANT, DENY, and REVOKE permissions to ALTER ANY AVAILABILITY GROUP, CREATE AVAILABILITY GROUP, and CONTROL/VIEW DEFINTION/TAKE OWNERSHIP/ALTER on an availability group.
New GRANT, DENY, and REVOKE permissions to the CREATE SEQUENCE permission.
New GRANT, DENY, and REVOKE permissions to the ALTER ANY EVENT SESSION permission.
New Role Management
User-defined server roles are now available. To manage user-defined server roles use CREATE SERVER ROLE, ALTER SERVER ROLE, and DROP SERVER ROLE. To add and remove members from all server roles, use ALTER SERVER ROLE … WITH ADD MEMBER. sp_addsrvrolemember and sp_dropsrvrolemember are deprecated.
ALTER ROLE is modified to add or remove members from roles by using ADD MEMBER syntax. sp_addrolemember and sp_droprolemember are deprecated.
IS_ROLEMEMBER is added to check the membership of database roles.
Default Schema for Groups
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. For more information about default schemas, see CREATE USER.
SQL Server Audit Enhancements
Support for server level auditing is expanded to include all editions of SQL Server. Database level auditing is limited to Enterprise, Developer, and Evaluation editions.
SQL Server Audit is now more resilient to failures to write to the audit log. For example, if the target directory is on a remote share and the network goes down, SQL Server Audit will now be able to recover once the network connection is re-established. In addition, a new option has been introduced to fail an operation that would otherwise generate an audit event to be written to a failed audit target. For more information, see the FAIL_OPERATION option for the ON_FAILURE event in CREATE SERVER AUDIT.
Previously, Audit logs could have an indeterminate number of log files or else be rolled-over after a predefined number. A new option has been introduced to cap the number of audit files without rolling over, in order to allow customers to control the amount of audit information collected without losing audit records. For more information, see the MAX_FILES option in CREATE SERVER AUDIT.
When possible, the audit log provides additional Transact-SQL stack frame information. In many cases, auditors can now determine whether a query was issued through a stored procedure or directly by an application.
SQL Server audit specifications now support a user-defined audit group. Audited events can be written to the audit log by using the new sp_audit_write (Transact-SQL) procedure. User-defined audit events allow applications to write custom information to the audit log, such as the name of the application user who has connected in cases where a common login is used to connect to SQL Server.
New columns are added to sys.server_file_audits, sys.server_audits, and sys.fn_get_audit_file to track user-defined audit events.
SQL Server Audit now supports the ability to filter audit events before they are written to the audit log. For more information, see the WHERE clause in CREATE SERVER AUDIT and ALTER SERVER AUDIT.
New audit groups support the monitoring of contained database users.
The new audit options have been added to the audit dialog boxes in Management Studio.
Database Engine Access is Allowed Through Contained Databases
Access to contained databases is permitted through contained database users which do not require logins. SQL Server system administrators should understand how contained databases change the SQL Server security model. For more information, see Security Best Practices with Contained Databases.
Hashing Algorithms
The HASHBYTES function now supports the SHA2_256, and SHA2_512 algorithms.
Further Deprecation of RC4
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.
Certificate Key Length
When creating certificates, the maximum length of private keys imported from an external source is expanded from 3,456 to 4,096 bits.
Service Master Key and Database Master Key Encryption changes from 3DES to AES
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. For more information about regenerating the SMK, see ALTER SERVICE MASTER KEY (Transact-SQL) and ALTER MASTER KEY (Transact-SQL).
Certificates Can be Created from Binary
CREATE CERTIFICATE (Transact-SQL) has the FROM BINARY option to allow specifying the binary description of an ASN encoded certificate. New functions CERTENCODED (Transact-SQL) and CERTPRIVATEKEY (Transact-SQL) can be used to extract a binary description of an existing certificate.