Introducing SQL Server 2008.pdf

Introducing

SQL

Server ®

2008

Peter DeBetta

Microsoft ®

Chapter 1

Security and Administration

Introduction

With all the complexity of today’s IT shops and with stronger security measures being re-

quired by both the high-tech industry and by government regulations, we are going to need

some new tools to help us make our way through the new world of IT. SQL Server 2005 made

leaps and bounds in advances for security and management, providing the ability to more

easily manage and secure servers with features such as certiﬁcate and key-based encryption

and more full-featured tools. SQL Server 2008 takes these measures even further, provid-

ing the ability to more easily manage and secure database servers, with new features such

as policy-based management, external key management, server and database auditing, and

transparent data encryption.

Policy-Based Management

Have you ever had to ensure that only Windows logons or groups were added to Microsoft

SQL Server, or that xp_cmdshell was disabled, or that no stored procedure names started

with “sp_”? Did you ever have to do this to more than one server in your enterprise? I have,

and it was always such a hassle to go from server instance to server instance, querying sys-

tem objects, checking various conﬁguration settings, and scouring through all sorts of places

to ensure that your SQL Server instances were all compliant. That process has changed in SQL

Server 2008.

Policy Management in SQL Server 2008

Yes, it’s true. SQL Server 2008 introduces a new feature known as the Policy-Based

Management. This framework allows you to deﬁne policies on a variety of objects and then

either manually or automatically prevent changes based on said policies. Management is also

very simple using SQL Server Management Studio (preferred), or you can write your own

code to manage policies. But I am getting ahead of myself. Let’s start at the beginning.

This management framework allows you to easily and proactively manage a variety of poli-

cies, ranging from security to metadata. It does this in two ways:

It allows you to monitor changes to these policies, with options to manually check poli-

cies, check policies on schedule, check policies on change and log violations, or check

policies on change and prevent the change if the policy is violated.

Introducing SQL Server 2008

It allows you to manage one or more SQL Server instances on a single server or across

multiple servers.

Rather than waiting for something to go awry, you can set policies based on your server

speciﬁcations and then have the framework proactively prevent changes based on these

policies or inform you via policy logs when these policies are being violated. The ability to

prevent certain changes depends on the type of feature, or facet, for which you are creating

a policy. For example, if you want to ensure that xp_cmdshell is never turned on for any sever

you are managing, you can create a policy and have it inform you when a change occurs or

even have it check for changes on a schedule, but you cannot prevent it from being changed.

The ability to prevent changes varies from facet to facet.

Policy-Based Management in SQL Server Management Studio

The practice of creating and enforcing policies is easily achieved using SQL Server

Management Studio. Policy-Based Management is accessed primarily by the Policy

Management node in Object Explorer, which can be found under the Management node of

the SQL Server instance, as shown in Figure 1-1.

FIGURE 1-1 Policy Management in Object Explorer

Within this node of Object Explorer, you ﬁnd the three base items of the framework: Policies,

Conditions, and Facets. Although not shown as a node, Policy Category Management can

also be accessed from here by right-clicking on the Policy Management node of Object

Explorer and choosing Manage Categories. So what does each of the objects do to help you

Chapter 1 Security and Administration

implement policy-based management? Let’s dig into each of them in more detail and dis-

cover how they are used.

Policy-Based Management Objects

Policy-Based Management uses ﬁ ve different objects to manage policies: facets, conditions,

policies, targets, and categories.

Facets

Facets are the base units of this framework. Facets are types of objects, such as a Surface

Area feature, server, logon, database, user, and so on. Each facet has a set of predeﬁ ned

properties against which conditions can be created.

As of the Community Technology Preview 6 (CTP6) release, there are a total of 47 facets, with

a whopping 1,492 total properties. SQL Server Management Studio has a list of these facets

under the Facets node in Objects Explorer (found under Management, Policy Management).

Alas, if you want to see each list of properties, you need to open each facet’s properties in-

dividually. If you want a quick list of all facets and properties, however, you can use the SQL

Server Management Objects (SMO) to iterate through all available facets and properties, as

shown here:

FacetInfoCollection fic = PolicyStore.Facets;

IEnumerable<FacetInfo> fic_sorted = from fic_i in fic

orderby fic_i.DisplayName

select fic_i;

Int32 pcount;

foreach (FacetInfo fi in fic_sorted)

{

Console.WriteLine(“FACET: “ + fi.DisplayName);

IEnumerable<PropertyInfo> fi_sorted = from fi_i in fi.FacetProperties

orderby fi_i.Name

select fi_i;

pcount = 0;

foreach (PropertyInfo pi in fi_sorted)

{

if (pcount++ > 0)

Console.Write(“, “ + pi.Name);

else

Console.Write(pi.Name);

}

Console.WriteLine();

Console.ReadLine();

}

Console.WriteLine(“---End of List---”);

Console.ReadLine();

Introducing SQL Server 2008

Facets by themselves cannot do anything in establishing policies. They can be used by condi-

tions, however, to deﬁne what rules you want to create and against which servers, databases,

or other objects the policies should check.

Conditions

A condition is an expression that deﬁnes the desired state of a facet. You express a condition

by setting a facet property, a comparative operator, and a value. Each property condition’s

state is set according to its respective data type. For example, the Name property of the

Stored Procedure facet is of type String and can have a condition operator of equal (=), not

equal (!=), LIKE, NOT LIKE, IN, or NOT IN. Thus it can be compared with a string or a list of

strings. The SQL Mail property of the Surface Area facet is of data type Boolean, and thus it

has only the equality and inequality operators and can only be set to a value of true or false.

Note There is an advanced expression editor (the Advanced Edit dialog box) available if you

need to create a specialized condition check. For example, you can check that the name of a

table doesn’t equal the schema name or that all tables have a primary key. The advanced expres-

sion editor allows a lot of ﬂexibility, but when used in a condition, its respective policy can only

be executed On Demand.

Both the ﬁeld and expression value can be set using the advanced expression editor. In addition

to providing a custom expression, it also provides an explanation of the available functions and

a description of the facet properties. So if you are not sure what the property represents, you do

not need to go to the facet and open it; you can simply click the ellipsis button (…) and examine

the properties from there.

Furthermore, a condition can also only contain properties from a single facet type. For ex-

ample, you can create a condition that states “SQL Mail is disabled and Database Mail is

disabled” because both of these properties are part of the Surface Area facet. You cannot,

however, create a condition that states “stored procedure names must begin with ‘pr’ and

xp_cmdshell is disabled” because these two properties are part of two different facets (the

Stored Procedure facet and Surface Area facet, respectively).

You can, however, create multiple conditions based on the same underlying facets. So you

can create a condition that states “SQL Mail is disabled and Database Mail is disabled,” and

you can create a second condition that states “SQL Mail is disabled and Database Mail is en-

abled.” Of course, you wouldn’t want to have both policies on the same server because one

of the policies will always be in violation.

SQL Server 2008 comes with an assortment of predeﬁned conditions that you can imme-

diately put into use. For example, one of my favorites is the condition named Auto Shrink

Disabled, which can be used by a policy to ensure that databases do not enable the auto

shrink option. Figure 1-2 shows this particular condition in the Open Condition window.

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika: