programming4us
programming4us
DATABASE

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

- How To Install Windows Server 2012 On VirtualBox
- How To Bypass Torrent Connection Blocking By Your ISP
- How To Install Actual Facebook App On Kindle Fire
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.

images

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.

images

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.

Other  
  •  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
  •  
    Top 10
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 2) - Wireframes,Legends
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Finding containers and lists in Visio (part 1) - Swimlanes
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Formatting and sizing lists
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Adding shapes to lists
    - Microsoft Visio 2013 : Adding Structure to Your Diagrams - Sizing containers
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 3) - The Other Properties of a Control
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 2) - The Data Properties of a Control
    - Microsoft Access 2010 : Control Properties and Why to Use Them (part 1) - The Format Properties of a Control
    - Microsoft Access 2010 : Form Properties and Why Should You Use Them - Working with the Properties Window
    - Microsoft Visio 2013 : Using the Organization Chart Wizard with new data
    REVIEW
    - First look: Apple Watch

    - 3 Tips for Maintaining Your Cell Phone Battery (part 1)

    - 3 Tips for Maintaining Your Cell Phone Battery (part 2)
    programming4us programming4us
    programming4us
     
     
    programming4us