SQL11UPD02-TSCRIPT-05

This wiki is a transcript of a previously recorded video.

Related content assets: 

  • Presentation: Introducing SQL Server 2012 Security and Auditing Improvements (SQL11UPD02-DECK-03
  • Video: Introducing SQL Server 2012 Security and Auditing Improvements (SQL11UPD02-REC-05)

Slide: Title Slide

Hey folks – this is Joe Sack and I’m a Principal Consultant with SQLskills. This is the security and auditing module for SQL Server 2012 – so a brief introduction on what’s been changed in SQL Server 2012 – a few of the security-related features.

Slide: Improved Security Manageability and Separation of Duties

And we’re going to start by talking about, first of all, default schema for groups and also user-defined server roles. I’ll also talk a little bit about server auditing mid-way through this presentation.

Slide: What Happens When…

So to set some context, let’s consider a situation where you have Windows Groups, Windows Active Directory Groups that you’re using for security – so you’re not creating separate logins for an individual and instead you’re creating a Windows Group – let’s say Accounting or Finance. So in a situation, let’s say I’m Joe User and I’m coming in through the Finance Group and I have permissions to a specific database, and I don’t fully qualify my object references. Let’s say I create a new object or I’m trying to reference it through a SELECT – in those scenarios, I don’t have a default schema associated with that group. So if I create a new object, it’s going to be prefixed with my Windows Active Directory login name. Or if I’m referencing it through a SELECT, I might not be selecting the right version of that table if it’s the same name with a different schema.

So SQL Server 2012 – they actually have a default schema for the Groups now. So when you are going in through a Group, you can actually be routed to a default schema. If you have multiple schemas, often people are members of multiple Groups, the schema with the lowest principal_id - and you can look at that principal_id in the catalogs view – so sys.server_principals, you can look at the lower of the principal_ids will be the default schema that you’re associated with. And then if you’re creating new objects that will be associated with that schema, instead of having a situation where it’s joe.objectname, instead of Accounting.objectname.

Slide: Default Schema for Groups

And this slide just shows a visualization – so we have Contoso\SQLDevs default schema dbo. So the overall goal is just making sure people are going against the right objects, that we’re not creating schemas that we didn’t intend to create, and overall just easing the administration of managing that. So you’re still following the best practices by using Groups – and not having to create a bunch of logins that have the default schemas instead.

Slide: What Happens When…

So server-level permissions - so this is something for those of you in areas where compliance is extremely important in your organization, let’s say PCI compliance. If you have an auditor that says – I want you to have more separation of duties, there’s too many people with sysadmin permissions – you now this additional option in SQL Server 2012 to create a user-defined server role. And in 2008 you didn’t have that option – in the databases you had the ability to create defined user roles, so you didn’t have to add somebody to dbowner, but when it came to SQL Server instance-level permissions, you had a choice of the fixed server roles, so you might add somebody to sysadmin, or creating that principal and then assigning them granular permissions directly instead of efficiently having one Group that you can add and remove members from and then you just manage the permissions at that principal… at that group.

Slide: User-Defined Server Roles

So that’s now an option in SQL Server 2012 – you can create it, the syntax is very straight forward, and this next slide shows a visualization – it’s cut off a little bit from my screenshot but CREATE ENDPOINT and ALTER ENDPOINT are the two permissions that you would see pointing up to AGAdmins. So server-level permissions assigned to a group named AGAdmins and then you can add and remove permissions accordingly.

Slide: Creating a User-Defined Role

And I mentioned syntax is pretty straightforward – it’s CREATE SERVER ROLE and you can GRANT, REVOKE, DENY and then in terms of adding members it’s ALTER SERVER ROLE … ADD MEMBER. There’s REMOVE MEMBER syntax, there’s the ability to rename the role – so if you want to see a quick example of that there will be a separate demo video that covers that specific scenario.

Slide: Monitoring Server Role Changes

One other thing I wanted to show you is just that you can create, for example, a DDL trigger than can monitor, for example, ADD_SERVER_ROLE_MEMBER, DROP_SERVER_ROLE_MEMBER and so if people are managing that principal or being removed or added to it, you can actually trigger on that. So in this example we’re saying if anybody removes or adds a member, go ahead and insert into Audit.dbo.SvrRoleChanges and show that event.

Slide: Improve Auditing

So pretty straightforward.

Alright, now, there have been some changes around server auditing. So those changes include increasing which SKUs support it, and also some changes around the resilience of the server audit process. And then additional feature about user-defined audit events, so if you want to create server-level audit events, you can do so. And then record filtering, which I’ll talk a little bit about, and then the new T-SQL stack information.

So in that example, and I don’t go into too much detail on this particular bullet point, basically if you have a situation where you’re auditing, let’s say the access of a table xyz, and you’re selecting against that table – let’s say you have a scenario where someone’s doing a straight ad hoc query against it, and then let’s take a scenario where someone has a stored procedure that’s doing that actual select. So you call procedure xyz and then select from that table. Basically that T-SQL stack information will show you the origins of that stored procedure call. So it’s just a little bit more information to help provide the context of where that audited event is coming from.

Slide: What Happens When…

So 2008, 2008 R2 server audit – it was available, but it was available on Enterprise and Datacenter Editions, and Developer as well. And Standard, Web, and Workgroup Editions didn’t support it and the thing about that was, if you wanted to use native SQL Server tools for you auditing process, that pretty much meant you were using SQL Trace, especially for the amount of events that you might be interested in – you would have to go with SQL Trace as your solution.

Now SQL Server 2012 brings that support to all SKUs. So now you have the server audit option for all SKUs. The database audit specifications are still for Enterprise and Datacenter, or actually in this case Enterprise as I think Datacenter’s no longer applicable in this scenario but you do have that increased support for server audit.

Slide: Audit Support on all SKUs

The real benefit here is that SQL Trace, while very useful, has much more overhead and server audit is really based on Extended Events architecture so it’s extremely lightweight and it’s also flexible because you can create separate audit events and you could, say, go one to the security log and one to a binary file.

So it offers some flexibility, it’s lightweight and so that increased support now allows you to have a cohesive strategy across all of your different versions as opposed to saying ok, we’re going to use SQL Trace over here and server audit over here.

Slide: What Happens When…

In terms of resilience, we’ll talk a little bit about that. In SQL Server 2008, you could have situations where write failures could silently lose audit records so that was a possibility. And another aspect in 2008/2008 R2 is that you had two options if there was an issue with  your server audit – so  you could either shut down your SQL Server instance. Imagine you production instance, if something goes wrong with your auditing and you absolutely cannot lose that event, you would shut down the SQL Server instance. And then the second option was to continue, but the continue meant you were moving past that event that you didn’t audit, or you didn’t track.

So SQL Server 2012 adds a little bit of behind-the-scenes resilience around the automatic recovery – so for example, recovery from file or network errors. And then the other aspect is that there’s a third option, it’s called FAIL_OPERATION.

So let’s say I have a specific event – let’s say CREATE LOGIN – and I’m saying I have FAIL_OPERATION configured. And let’s say my log file has filled up and I don’t have rollover set up for whatever reason, or I created some kind of constraints that no longer allow that audit record to be generated when I’m creating a login. Now if you have the FAIL_OPERATION you can say look, that login is going to fail, you can’t create it unless I can log it. So it adds an additional layer of protection – I have a feeling you’re going to start seeing some blog posts about considerations about how to use the FAIL_OPERATION further because you have to be careful to make sure that you’re auditing in such a way that you don’t have a lot of failures otherwise you might be blocking mission-critical activities that you don’t want to block. But it is an option as opposed to losing the records altogether – so something for you to be aware of.

Slide: User-Defined Audit Event

Other aspect is you have the sp_audit_write system stored procedure that allows you to create your own user-defined auditable events. And so if you look here the different parameters are, you have the user_defined_event_id that you can customize, whether or not the activity succeeded from an application perspective, so a zero or a one – and then the actual user-defined information. So you can add this as an event that you would track.

Slide: Record Filtering

The other thing is if you’re familiar with Extended Events, and you’ve worked with it, you know that there are predicate expressions – so there are ways to filter through predicates – and server audit now has expanded that support as well. So you can actually add Extended Events like syntax or it’s actually the same definition to your server audit. So if there are specific rows that you’re interested in, but you want to filter it off based on some of the columns that get returned into your server audit data, you now have that option.

Slide: Summary

So that was a brief summary about the SQL Server 2012 security and auditing enhancements and so just to recap – so you have user-defined server roles, so you can now have separation of duties in your server roles where you don’t have to give somebody too much and you also don’t have to manage it one principal at a time. You now have this bucket that you can add and remove member from and you can grant and deny various permissions, or revoke, what have you.

And the second thing is schema options, so now if I’m using Windows Groups, Active Directory Groups, for permissions – which is definitely a great practice – now I have a default schema that I can associate with it.

And then you have the auditing improvements – so first of all its expanded out so you can have server audits across all SKUs, and then there’s more resilience options, there’s user-defined events and then there’s enhanced filtering.

So I hope you’ve found this useful and thanks for watching, and if you get a chance, check out the demo as well.


Return to SQL Server 2012 Developer Training Kit BOM (en-US)