Server 2012

Microsoft SQL Server 2012 Security Enhancements Jack Richins - Senior SDE Lead

JACK RICHINS: Hi, I'm Jack Richins, and I'm presenting SQL Server 2012 Security Enhancements today.

We'll be going over and reviewing features that were introduced in SQL Server 2008 to set the context, and then reviewing new features that are being introduced in SQL Server 2012. Along the way, you'll learn howyou can use these features to better reach compliance in SQL Server 2012.

Let's start off with going over a recap of the 2008 features, and then the new features in SQL Server 2012, namely manageability enhancements, audit enhancements, database authentication, and cryptography changes for SQL Server 2012.

In SQL Server 2008, we had a very strong suite of security features that addressedareas in protecting your data, controlling access, and ensuring compliance across your SQL Server deployments with features such as transparent data encryption, extensible key management to help protect your data at rest, Kerberos authentication enhancements to improve the security as you were logging in and authenticating with the system. And SQL Server audit and change data capture, policy-basedmanagement, and common criteria certification to help you reach compliance.

This provided a strong security story for SQL Server 2008, as we provided you means to monitor access to your data, and control access, and reach compliance through features such as transparent data encryption, auditing, and monitoring whether those features were on or off through policy-based management. We allowed yougreater control over your key management through extensible key management, and audit and authorization provided granular control over who had access, and an audit log of who was exercising that access. Login triggers and SSL further protected connections of applications to the SQL Server data stores and remote backup allowed compliance and risk management of protecting your data assets.

So,let's talk about what's new in security in SQL Server 2012.

We've improved manageability of security in SQL Server 2012 with features such as default schema for groups, and user defined server roles.

Default schemas for groups is a new feature in 2012, and a long-standing ask and connect for SQL Server. Previously, you could only define a default schema for an individual user. Now, you canassign a default schema to a group. This eases administration and avoids implicit schema creation, and implicit user creation. It reduces the chances of the wrong schema being used in queries, and reduces the number of users that need to be maintained and monitored.

We've also introduced user-defined server roles. A server role is a server-level principle, kind of like a server group that theadministrator can define. It's a collection of principles that hold a collection of permissions. This can be compared to fixed server roles where it's a securable class, and the permissions can change, so you can have different permissions that match your compliance needs. It increases the flexibility, and manageability, and facilitates compliance. Previously we had database roles, and these aremuch the same. It allows customers to define a role, grant it permission such as control server, and deny it other permissions, such as alter any login.

Here I'm connected to SQL Server 2012. I'm going to create a new server role called XEadmins. This role is intended to provide a means of administering Xevent, a new feature in SQL Server 2008, and one that's been greatly enhanced in 2012. So,let's create that role, and grant it alter any event session, and view server state, so that it can both monitor the system and create event sessions. Now I'm going to create a login to demonstrate using this. I'm going to add that login to the role with the alter server role DDL statement. Now, I'm going to switch and use the UI. If you look over under the database node, under management, there...