SQL Server 2012 : Validating Server Configuration (part 1) - The Need for a Policy, Create Policy on a Local Server

7/19/2014 9:31:15 PM

Chances are if you work for a company that has gone through a compliance certification, you realize that it takes a lot of time and effort. Once you are considered compliant, staying in compliance is important. As a DBA, you may be asked to ensure server settings remain unchanged. Some of the properties that you need to ensure might be that databases must be encrypted or might be making sure a highly privileged function such as xp_cmdshell is disabled. SQL Server has a feature called policy-based management (PBM) that makes enforcing compliance easy. With PBM, you can create, deploy, and validate policies that enforce the configuration that you have defined.

The Need for a Policy

PBM models objects such as databases, tables, and logins within the SQL Server instance and provides a hierarchical structure of these managed targets. Each target has many characteristics. For example, the database target has properties such as autoclose, whether the database is encrypted, and so on. These targets have specific properties that are exposed through facets. When you create a policy, it is based on a facet. A facet describes some characteristics of a specific target that you can create a policy against.

To help illustrate these concepts, I’ll show how to create a policy that will validate your security settings. The scenario for this example is as follows: Recently, a consultant was hired by your company to determine which kinds of things needed to be done to be PCI compliant. The consultant has tasked you with ensuring all servers in your organization match the given configuration:

  • The common criteria mode should be enabled.
  • SQL Server should be in Windows-integrated mode only.
  • The xp_cmdshell extended stored procedure should be disabled.
  • Cross-database ownership chaining should be disabled on every database.
  • You manage 25 production SQL Server instances. Now, go and earn your paycheck!

Create Policy on a Local Server

First, let’s create the policy on the local server to make sure it’s what you want to deploy. To create a new policy, select New Policy from the context menu of the Policy node in the Management tree in Object Explorer. This will launch the Create New Policy dialog box, shown in Figure 1.


Figure 1. Create New Policy dialog box

After typing Server Security Policy in the Name text box, you need to select a check condition. A check condition is a lot like a WHERE clause in a T-SQL statement. It defines what, specifically, the policy is checking. Since you have not created a check condition for your server security settings, you need to select “New condition.” This will launch the Create New Condition dialog box, shown in Figure 2.


Figure 2. Create New Condition dialog box

Since you are interested in ensuring serverwide security settings are enforced, you need to select the Server Security facet. Notice that, when you open the Facet list, you are presented with a plethora of facets. Each one of these facets exposes a variety of parameters that you can use to create policies. Once you select Server Security, you will be able to build your logical expression.

The expression grid shown in Figure 2 builds the requirements. The common criteria switch should be enabled, XP command shell should be disabled, the login mode should be Windows integrated, and cross-database ownership chaining should be disabled. Once you click OK to create this new condition, control will return to the Create New Policy dialog box.

Had the Server Security facet applied to specific targets, such as tables or stored procedures, those options would be presented in the Against Targets list shown in Figure 1. Since the Server Security facet applies only to the SQL Server instance, there are no specific targets to apply this policy against. The next combo box is Evaluation Mode. The default is “On demand,” which means that this policy will be created on the server, and nothing will be done with it unless you explicitly evaluate it. The next option is “On schedule,” which schedules the evaluation of the policy using SQL Server Agent. If you had specified a policy that triggered on DDL events such as enforcing table names to be a certain format, you would see two additional execution modes: “On change: log only” and “On change: prevent.” The log-only option allows a new table with the wrong name to be created; the prevent option rolls back the transaction, inhibiting the table from being created.

Click OK. You can see the new policy created in the Policies node in Object Explorer.

  •  SQL Server 2012 : Encryption (part 2) - Certificate-Based Encryption, Transparent Data Encryption
  •  SQL Server 2012 : Encryption (part 1) - Encryption Primer, Password-Based Encryption
  •  SQL Server 2012 : Auditing in SQL Server (part 3) - Database Audit Specification Object, User-Defined Audit Event
  •  SQL Server 2012 : Auditing in SQL Server (part 2) - Server Audit Specification Object
  •  SQL Server 2012 : Auditing in SQL Server (part 1) - Auditing Objects, Server Audit Object
  •  SQL Server 2012 : Reordering Nodes within the Hierarchy - The GetReparentedValue Method,Transplanting Subtrees
  •  SQL Server 2012 : Querying Hierarchical Tables - The IsDescendantOf Method
  •  Protecting SQL Server Data : Obfuscation Methods (part 4) - Truncation,Encoding, Aggregation
  •  Protecting SQL Server Data : Obfuscation Methods (part 3) - Numeric Variance,Nulling
  •  Protecting SQL Server Data : Obfuscation Methods (part 2) - Repeating Character Masking
    Most View
    Steam Is Rising Watch Out, Consoles! (Part 2)
    Lenovo IdeaPad Yoga 13 - Convertible Laptop (Part 2)
    Restore Your Files
    Desktop Organisation Tools (Part 3) : RocketDock, Dexpot
    Which Is The Real Best-Seller Ultrabook? (Part 2) - Gigabyte U2442, Samsung Series 9 900X, HP Envy 6
    Epson Stylus Photo 1500W A3+ Wi-Fi Printer
    Chillblast Fusion Firebird - Offers The Fastest Framerates
    ASP.NET State Management : The View State of a Page (part 1) - The StateBag Class, Common Issues with View State
    Is Windows 8 Already In Deep Trouble? (Part 2)
    Windows 8 : Managing Windows Update (part 2) - Configuring update settings
    Popular Tags
    Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 Adobe Indesign Adobe Flash Professional Dreamweaver Adobe Illustrator Adobe After Effects Adobe Photoshop Adobe Fireworks Adobe Flash Catalyst Corel Painter X CorelDRAW X5 CorelDraw 10 QuarkXPress 8 windows Phone 7 windows Phone 8 BlackBerry Android Ipad Iphone iOS
    Top 10
    Review : Acer Aspire R13
    Review : Microsoft Lumia 535
    Review : Olympus OM-D E-M5 Mark II
    TomTom Runner + MultiSport Cardio
    Timex Ironman Run Trainer 2.0
    Suunto Ambit3 Peak Sapphire HR
    Polar M400
    Garmin Forerunner 920XT
    Sharepoint 2013 : Content Model and Managed Metadata - Publishing, Un-publishing, and Republishing
    Sharepoint 2013 : Content Model and Managed Metadata - Content Type Hubs