Scheduled Policy Evaluation on multiple servers using MSX/ TSX in SQL Agent - Microsoft SQL Server Front End Blog - Site Home

One of our customer was looking for ways to schedule a policy evaluation on multiple servers using their existing MSX/TSX infrastructure. I thought it would be better to blog an article that could help others looking for similar information

Before we get into details, here are some basics. Please read reference material. If you have any specific issues, send us an email / post comments in this blog entry.

SQL Agent MSX TSX for distributing jobs to multiple servers - http://msdn.microsoft.com/en-us/library/ms180992.aspx

Policy Based Management - http://msdn.microsoft.com/en-us/library/bb510667.aspx

SQL Powershell : http://msdn.microsoft.com/en-us/library/cc281954.aspx

SQL Agent token substitution: http://msdn.microsoft.com/en-us/library/ms175575.aspx

Note: T-SQL scrript to create a policy, condition and a job is there at http://blogs.msdn.com/cfs-file.ashx/__key/CommunityServer-Blogs-Components-WeblogFiles/00-00-01-40-41-Scripts/1057.EvaluatePolicy_5F00_MSXTSX.sql )

1) Create PBM Policy and condition:

Policy to check if “Integrated security” the only security mode that is is enabled for SQL instance

Create a condition:

-- Create a PBM condition 

Declare @condition_id int

EXEC msdb.dbo.sp_syspolicy_add_condition @name=N'Check if Integrated Security is enabled', @description=N'', @facet=N'IServerSecurityFacet', @expression=N'<Operator>, @is_name_condition=0, @obj_name=N'', @condition_id=@condition_id OUTPUT

  <TypeClass>Bool</TypeClass>

  <OpType>EQ</OpType>

  <Count>2</Count>

  <Attribute>

    <TypeClass>Numeric</TypeClass>

    <Name>LoginMode</Name>

  </Attribute>

  <Function>

    <TypeClass>Numeric</TypeClass>

    <FunctionType>Enum</FunctionType>

    <ReturnType>Numeric</ReturnType>

    <Count>2</Count>

    <Constant>

      <TypeClass>String</TypeClass>

      <ObjType>System.String</ObjType>

      <Value>Microsoft.SqlServer.Management.Smo.ServerLoginMode</Value>

    </Constant>

    <Constant>

      <TypeClass>String</TypeClass>

      <ObjType>System.String</ObjType>

      <Value>Integrated</Value>

    </Constant>

  </Function>

</Operator>'

Select @condition_id

  

GO

Create a Policy, refer to condition created above

  

  

-- Create a PBM policy

Declare @object_set_id int

EXEC msdb.dbo.sp_syspolicy_add_object_set @object_set_name=N'f_ObjectSet', @facet=N'IServerSecurityFacet', @object_set_id=@object_set_id OUTPUT

Select @object_set_id

  

Declare @target_set_id int

EXEC msdb.dbo.sp_syspolicy_add_target_set @object_set_name=N'f_ObjectSet', @type_skeleton=N'Server', @type=N'SERVER', @enabled=True, @target_set_id=@target_set_id OUTPUT

Select @target_set_id



2) Create SQL Agent Job

Create a SQL Agent job with a PowerShell script to evaluate policy; use token substitution to replace server name , instance name during job execution

  

-- Create a job that evaluates policy

USE [msdb]

GO

  

/****** Object:  Job [PolicyCheck Job - Check if Integrated Security is enabled]    Script Date: 11/19/2010 11:35:00 AM ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT

SELECT @ReturnCode = 0

/****** Object:  JobCategory [[Uncategorized (Local)]]]    Script Date: 11/19/2010 11:35:00 AM ******/

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

  

END

  

DECLARE @jobId BINARY(16)

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'PolicyCheck Job - Check if Integrated Security is enabled', 

        @enabled=1, 

        @notify_level_eventlog=0, 

        @notify_level_email=0, 

        @notify_level_netsend=0, 

        @notify_level_page=0, 

        @delete_level=0, 

        @description=N'No description available.', 

        @category_name=N'[Uncategorized (Local)]', 

        @owner_login_name=N'REDMOND\seths', @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

/****** Object:  Step [Check Policy]    Script Date: 11/19/2010 11:35:01 AM ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Check Policy', 

        @step_id=1, 

        @cmdexec_success_code=0, 

        @on_success_action=1, 

        @on_success_step_id=0, 

        @on_fail_action=2, 

        @on_fail_step_id=0, 

        @retry_attempts=0, 

        @retry_interval=0, 

        @os_run_priority=0, @subsystem=N'PowerShell', 

        @command=N'# Note update this variable for a different policy name'$(ESCAPE_SQUOTE(INST))'' -eq ''MSSQLSERVER'') {$instname = ''\DEFAULT''} ELSE {$instname = ''''};'SQLSERVER:\SQLPolicy\$(ESCAPE_NONE(SRVR))'' + $instname'Policy automation is not enabled on instance'' 'There were one or more policy evaluation failures. Please check agent logs and policy evaluation histories'' , 

$policyToEvaluate = "Check if Integrated Security for SQL Server"

  

if ('

  

$policiesPSPath = '

  

# Check if policy Automation is enabled

$policyManagement = Get-Item $policiesPSPath

$policyManagement.Refresh()

  

if( $policyManagement.Enabled -eq $False)

{

    throw "Policy automation is not enabled on instance" 

};

  

# Get specific policy and evaluate

$result = $policyManagement.Policies |  where { $_.Name -eq $policyToEvaluate}   | Invoke-PolicyEvaluation -AdHocPolicyEvaluationMode 2 -TargetServerName $(ESCAPE_NONE(SRVR))

  

# if there were any failures throw

if( $result.Result -eq $False)

{

    throw "There were one or more policy evaluation failures. Please check agent logs and policy evaluation histories"

};

  

# print evaluation results

$result

$result.ConnectionEvaluationHistories

  

'

        @database_name=N'master', 

        @flags=48

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

  

GO



Launch SQL Server Management Studio, in Object explorer verify if policy, condition, job was created

3) Run SQL Agent job – Verify job history and policy history

4) Configure MSX TSX – ( refer http://msdn.microsoft.com/en-us/library/ms180992.aspx )

5) Create Policy and condition on all servers. ( follow step #1) MSX TSX infrastructure does not distribute PBM policies

6) Set Agent job to be distributed to multiple servers – this sets the job as “multi-server” job.

If needed you can also associate a schedule. associated schedule will be distributesdto all Target servers

7) Verify job distribution on all target servers . On Target servers, distributed job will be read-only. If you need to make any changes you must do it on job that is in master server. all job changes will be replicated to all applicable target servers

8) Start the job on MSX (master) server

9) Check job logs on master server – You will see one row per target server

For Demo purpose, “SEATTLE” instance was enabled with mixed mode security and “MIRROR” instance was enabled with “Integrated Security”

10) Detailed Logs – connect to respective instance and look at the job history, policy history

This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm