How to perform index maintenance on Azure SQL Database – Reviewed

A few months, I wrote a blog post on how to perform index maintenance on Azure SQL Database.
I thought my solution with Ola Hallengren maintenance scripts worked fine, however, I got some comments on the post and on Twitter that there was an issue with my solution.

After analyzing and retesting it again, it turns out that there was indeed an issue. During the setup, I tried to create the IndexOptimize stored procedure in the master database but that is not allow.
So, I created a management database and created the stored procedures in that DB. Now I’ve noticed that when you do a select * from sys.databases in the user database, it only returns the database itself and the master…
It’s also documented here https://msdn.microsoft.com/en-us/library/ms178534.aspx#Anchor_1

This was an issue because the index optimize stored procedure is also using sys.databases to determine the databases. So, the runbook was working fine, except it was only doing an index optimize in my management DB and nothing more…

I’ve contacted Ola on this and he told me that sys.databases is not the only issue. The problem is also that Azure SQL Database does not support three-part-naming (DatabaseName.SchemaName.ObjectName). That means that it is not possible to have a stored procedure in one database, and do something in another database.

Workaround

After investigating, I’ve found a possible workaround for this issue.
The first step that you have to do is creating the IndexOptimize stored procedure in every user database.
Secondly, I’ve modified my runbook so that the selection of the user databases is not done with the stored procedure but inside the runbook.

I’m first going to connect to the master database to retrieve a list of all the online user databases.
Once I’ve got it, I connect to each of the user databases separately in a loop, and execute the index maintenance procedure.

This solution seems to be working so far. I know it is not (yet) as flexible as Ola’s scripts but it is certainly a good step in the right direction.
You can find the new script on the original post.

Good luck with it and if you have any issues with it, please let me know.

Pieter

How to perform index maintenance on Azure SQL Database

It’s been a long time since I wrote a blog post and I finally found some time 😀 I’ve played a lot recently with the new Azure SQL Database V12 version. It’s really cool because it has a lot of improvements if you compare it to v11. With the V12 version, you have almost the same functionalities like an on premise database and it consists also new functionalities like row-level security, dynamic data masking and the query store. Just like an on premise database, you also have to maintain your indexes and statistics because indexes can also get fragmented on an Azure SQL Database. With an on premise server or a VM in Azure, most DBA’s schedule an index maintenance job under the SQL Server Agent. However, with Azure SQL Database, you have to think inside the database, which means you don’t have any server functionalities, including SQL Server Agent. Luckily, the Azure platform provides the Azure Automation, which can be used as a replacement for the SQL Server Agent. In this blog post, I’m going to explain how you can schedule your index maintenance job or any other job that needs to be scheduled.

Create a new Azure Automation Account

First of all, you have to begin with creating a new Azure Automation Account. Go to the portal and select the Automation tab. Click the Create button to create the new Automation Account. Fill in the name of the account and the region. Choose the region that is the closest to you. r

Create an Azure Automation credential asset

As prerequisite, create an Azure Automation credential asset that contains the username and password for the target Azure SQL DB logical server. Click on the Automation Account that you have just created and select Assets. In this section, select the button “Add Setting” at the bottom.

Select the option “Add Credential”


Select the Credential type “Windows PowerShell Credential” because we’re going to use this credential in the PowerShell Workflow. Give the Credential a name.

Specify the username and the password that you will link to the credential. This will be the user that will connect to your SQL Azure Database.

Click on OK and wait until the credential is created.

Install your maintenance scripts

Make sure that you have installed your maintenance scripts/procedures on all your Azure Databases. In my example, I’ve been using the maintenance scripts of Ola Hallengren. For the index maintenance, I have to install the scripts IndexOptimize.sql and CommandExecute.sql. Make sure you download the latest version because Ola fixed an issue with the index optimize on Azure SQL Database on July 19th 2015. There is a small issue with the scripts. Ola uses cross database stored procedures, which is not supported in Azure DB at the moment. So, the @Database parameter will not work correctly. Please also check the comments of this blog post. You have to implement a workaround in the runbook.

Import the Maintenance Runbook

We have now setup all the prerequisites so we can start with creating a runbook in our Azure Automation account. A runbook is a PowerShell Workflow that needs to be created or imported. You can actually compare it to configuring a job step in the SQL Server Agent job. The runbook contains the SQL Scripts that need to be executed for the index maintenance and will be scheduled later on. Select your Automation Account and go to the runbook tab. Click on the button “Import” at the bottom to upload your PowerShell Workflow. Select your PowerShell script that you have created and upload it.

Here is my script that I have used.

<#
.SYNOPSIS
 Perform index maintenance

.DESCRIPTION
 This runbook provides an example of how Azure Automation can be used to accomplish common SQL Agent tasks in the cloud. 
 As prerequisite, please create an Azure Automation credential asset that contains the username and password for the target Azure SQL DB logical server ($SqlServerName).
 Make sure that you have installed the scripts IndexOptimize.sql and CommandExecute.sql of Ola Hallengren (https://ola.hallengren.com/downloads.html)
 The check for the MAXDOP value in the IndexOptimize.sql script is using sys.dm_os_sys_info, which is currently not supported
 So be sure you disable that check otherwise it will return an error.
 
.EXAMPLE
 SQLServerIndexMaintenance
 
.NOTES
 AUTHOR: Pieter Vanhove
 LAST EDIT: October 20, 2015 
#>

workflow SQLServerIndexMaintenance
{
 param
 (
 # Fully-qualified name of the Azure DB server 
 [parameter(Mandatory=$true)] 
 [string] $SqlServerName,
 
 # Credentials for $SqlServerName stored as an Azure Automation credential asset
 # When using in the Azure Automation UI, please enter the name of the credential asset for the "Credential" parameter
 [parameter(Mandatory=$true)] 
 [PSCredential] $Credential
 )
 
 inlinescript{
 
 # Setup credentials 
 $ServerName = $Using:SqlServerName
 $UserId = $Using:Credential.UserName
 $Password = ($Using:Credential).GetNetworkCredential().Password
 
 
 # Create connection to Master DB
 $MasterDatabaseConnection = New-Object System.Data.SqlClient.SqlConnection
 $MasterDatabaseConnection.ConnectionString = "Server = $ServerName; Database = Master; User ID = $UserId; Password = $Password;"
 $MasterDatabaseConnection.Open();
 
 # Create command to query the current size of active databases in $ServerName
 $MasterDatabaseCommand = New-Object System.Data.SqlClient.SqlCommand
 $MasterDatabaseCommand.Connection = $MasterDatabaseConnection
 $MasterDatabaseCommand.CommandText = 
 "
 select name from sys.databases
 where state_desc='ONLINE'
 
 "
 # Execute reader and return tuples of results <database_name, SizeMB>
 $MasterDbResult = $MasterDatabaseCommand.ExecuteReader()
 
 # Proceed if there is at least one database
 if ($MasterDbResult.HasRows)
 {
 # Create connection for each individual database
 $DatabaseConnection = New-Object System.Data.SqlClient.SqlConnection
 $DatabaseCommand = New-Object System.Data.SqlClient.SqlCommand
 
 # Iterate through each database under $ServerName
 while($MasterDbResult.Read())
 {
 $DbName = $MasterDbResult[0]
 
 
 # Apply conditions for user databases (i.e., not master DB)
 if($DbName -ne "Master")
 {
 # Setup connection string for $DbName
 $DatabaseConnection.ConnectionString = "Server=$ServerName; Database=$DbName; User ID=$UserId; Password=$Password;"
 $DatabaseConnection.Open();
 
 # Create command for a specific database $DBName
 $DatabaseCommand.Connection = $DatabaseConnection
 
 Write-Output "Perform index maintenance on $DbName"

 # ExampleTable is a place holder for a table that holds a large volume of less important and expendable data
 # that can be truncated to save space on the database.

 $DatabaseCommand.CommandText ="
 EXECUTE dbo.IndexOptimize
 @Databases = '" + $DbName + "',
 @FragmentationLow = NULL,
 @FragmentationMedium = 'INDEX_REORGANIZE,INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
 @FragmentationHigh = 'INDEX_REBUILD_ONLINE,INDEX_REBUILD_OFFLINE',
 @FragmentationLevel1 = 5,
 @FragmentationLevel2 = 30,
 @UpdateStatistics = 'ALL',
 @OnlyModifiedStatistics = 'Y'
 "
 Write-Output $DatabaseCommand.CommandText
 $NonQueryResult = $DatabaseCommand.ExecuteNonQuery()
 
 # Close connection to $DbName
 $DatabaseConnection.Close()
 
 }
 } 
 
 # Close connection to Master DB
 $MasterDatabaseConnection.Close() 
 } 
 }
}

It’s based on the script that I have found on Codeplex. As you will notice, I have specified 2 parameters. $SqlServerName: this is the server name where we want to perform the index maintenance $Credential: This is the username that will be used to connect to the SQL Server. This will be linked to the credential that we have created in step 2 The workflow is first going to connect to the master database to retrieve all the online databases. As mentioned the install maintenance script part, I had to find a workaround because you cannot use cross DB stored procedures on Azure DB, which are used by the Ola’s IndexOptimize script. Once I get the list of all the databases, I connect to each of the DB’s separatly and execute the index optimize. Please not that the @Databases parameter should contain the current DB name. This solution is not (yet) as flexible as Ola’s solution but it’s a good start. Once the import is done, you will notice that the column Authoring still has a “New” Status. The runbook still needs to be published (see next chapter)

Test your Workflow and Publish

Before you can actually start using this Workflow, you have to publish it, however, I recommend to first test this if everything works fine. Once the Runbook has been created, you can click on it and go the Author section


In the “Draft” section you can see the Workflow that you have just imported. Click on the button “Test” at the bottom to test your Runbook. Before the Runbook is actually executed, you have to provide the 2 parameters. In my example, the Credential will be the AzureDBCredential that I have created in step 2. The name of my SQL Server is called pvhv12.database.secure.windows.net. This is the “SQL Server” where my databases are running on.


If all goes well, you should see an empty output pane with a status: COMPLETED
If not everything goes well, you will notice the errors in the output pane. As soon as you have tested the job and everything works fine, you can publish the runbook and start using it.

Schedule the runbook

The last step is to schedule the published runbook. This is actually the same like in the SQL Server Agent. You have to link a schedule to the job or in this case the runbook. In the runbook, click on the section “Schedule” and select “Link to a New Schedule”
Type in the name of your schedule. Again, the same principle as the name of a job schedule of the SQL Server Agent
Secondly, you need to configure the schedule. Currently you can choose between One time, Hourly or Daily. In my example, I want my Index Maintenance on a daily basis starting at 23:00.

In the final step, you have to specify the runbook parameters that will be used when the job is executed. This will be the same values that I have used in the test phase. Once the schedule is created, your index maintenance will be done on a daily basis.

Verify job history

Just like in the SQL Server Agent, you can also verify the job history of a Runbook. You can select “Jobs” in your runbook to verify the job history. When you click on one of the jobs, you can even see the error output, job summary and the parameters that have been used.

Summary

So summarize I will make a comparison between the Azure Automation and the SQL Server Agent Job.

Azure Automation SQL Server Agent Job
Create an Azure Automation Account Create an SQL Server Agent Job
Create a runbook Create a Job Step
Test the runbook Start Job at Step
Publish the runbook Save the job
Schedule the runbook Schedule the SQL Server Agent Job
View jobs of the runbook View History

 

Remove Backup files from Azure Storage Part 2

In April this year I’ve posted a script to delete your backup files from Windows Azure Storage.
This week, I’ve got a question from Mark, how you could use this script in a SQL Agent Job on an Azure VM. I mentioned in my previous blog post that you should download the Azure PowerShell cmdlets before you could use the script. But Mark always got the following error when he was trying to download the installation file.


In this blog post I will explain more into detail how you can configure the script correctly in a SQL Server Agent job and how you should install the Azure Powershell Cmdlets.

Startup your VM on Azure and connect to the instance with the SSMS.
Before we install anything extra on the VM, let’s configure a job with the Removal Script that you can find in my previous post. Don’t forget to change the storage account, the access key and the container in the script into your configuration.
Your job step should look like this. Choose the Type=Powershell and not T-SQL.


When you execute the job manually, you will probably get the following error message The term ‘New-AzureStorageContext’ is not recognized as the name of a cmdlet…


This is because we didn’t install the Azure PowerShell cmdlets yet. You can download the cmdlets here. Now, when you try to download this on the Azure VM, you will get the following error:


This is because your browser settings don’t allow you to download files. To fix this, open your Internet Explorer settings and select Internet Options


Select the security tab and click the button Custom level

Scroll down to Downloads and select the option Enable under “File download”


Click OK, close the settings and restart your browser. Now you should be able to download the file to install the Azure PowerShell Cmdlets


Save it and Run the file

First of all you will get this nice welcome screen…

Wait until it is finished and you see the next screen.
Just click Install

You will see that it will install the Microsoft Azure Powershell. Click “I accept” in the next screen

…and wait till the installation is finished

Click finish at the end

In the final screen you should actually see that the Azure Powershell Cmdlets are installed

Ok… that’s it!!!

Go back to the SQL Agent Job and execute it again. Normally the job should execute successfully!

To be 100% sure that it’s working, go to the Management portal of Windows Azure and verify the container if the files have been removed.

Have fun with it!

Tracking login and user changes in SQL Server

Did you ever wanted to track SQL Server logins and database user changes on all your SQL Servers? At one of my customers, the IT guys are sysadmins on the SQL Servers. In fact, the IT department is managing the SQL Servers when I’m not around. One day, “somebody” removed a user and of course, nobody did it 😉

First thing that came up to me was implementing SQL Server Audit. Good solution but not all versions of SQL Server are supporting SQL Server Audit. SQL Server 2008 and R2 are supporting SQL audit only in Enterprise Edition and there is no audit at all on SQL Server 2005. So, I had to come up with a solution that worked on these versions as well. My solution is based on this blog post of Aaron Bertrand (twitter | blog) but I’ve extended it.

Server Audit

I’ve implemented SQL Server Audit on all SQL Server 2008 and R2 Enterprise servers and SQL 2012 servers. Unfortunately no SQL 2014 yet, but you should also implement SQL Audit on this version The audit is dumping all the audit information into a audit log file on the server.

USE [master]
GO

CREATE SERVER AUDIT [Audit Login Changes]
TO FILE 
(	FILEPATH = N'ENTERYOURFILEPATHHERE'
	,MAXSIZE = 1024 MB
	,MAX_FILES = 10
	,RESERVE_DISK_SPACE = OFF
)
WITH
(	QUEUE_DELAY = 1000
	,ON_FAILURE = CONTINUE
	,AUDIT_GUID = '33e055a9-1924-47c3-9798-e8875a5bcd42'
)
ALTER SERVER AUDIT [Audit Login Changes] WITH (STATE = ON)
GO

 

I defined following Audit Action Types because I wanted to know all changes made on logins, roles and users.

Audit Action Type Description
DATABASE_ROLE_MEMBER_CHANGE_GROUP Raised whenever a login is added to or removed from a database role
SERVER_ROLE_MEMBER_CHANGE_GROUP Raised whenever a login is added or removed from a fixed server role
DATABASE_PERMISSION_CHANGE_GROUP Raised for any database permission
SERVER_OBJECT_PERMISSION_CHANGE_GROUP Raised whenever a GRANT, REVOKE, or DENY is issued for a server object permission
SERVER_PERMISSION_CHANGE_GROUP Raised when a GRANT, REVOKE, or DENY is issued for permissions in the server scope, such as creating a login
DATABASE_PRINCIPAL_CHANGE_GROUP Raised when users, are created, altered, or dropped from a database
SERVER_PRINCIPAL_CHANGE_GROUP Raised when server principals are created, altered, or dropped

More information on the Audit Action Types can be found here.

USE [master]
GO

CREATE SERVER AUDIT SPECIFICATION [Audit Login]
FOR SERVER AUDIT [Audit Login Changes]
ADD (DATABASE_ROLE_MEMBER_CHANGE_GROUP),
ADD (SERVER_ROLE_MEMBER_CHANGE_GROUP),
ADD (DATABASE_PERMISSION_CHANGE_GROUP),
ADD (SERVER_OBJECT_PERMISSION_CHANGE_GROUP),
ADD (SERVER_PERMISSION_CHANGE_GROUP),
ADD (DATABASE_PRINCIPAL_CHANGE_GROUP),
ADD (SERVER_PRINCIPAL_CHANGE_GROUP)
WITH (STATE = ON)
GO

Event Notifications

As mentioned before, we cannot use SQL Audit for SQL 2008 and R2 Standard Edition or SQL 2005. As an alternative, you can setup event notifications which will capture messages via Service Broker. The scripts below are based on the scripts of Aaron but I’ve added more events to it as I wanted to trace more than just “change password”

Create the following table in the msdb database

USE [msdb];
GO

CREATE TABLE dbo.SecurityChangeLog
(
    ChangeLogID			 int IDENTITY(1,1),
	LoginName            SYSNAME,
    UserName             SYSNAME,
    DatabaseName         SYSNAME,
    SchemaName           SYSNAME,
    ObjectName           SYSNAME,
    ObjectType           VARCHAR(50),
    DDLCommand           VARCHAR(MAX),
	EventTime			 DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
	CONSTRAINT PK_ChangeLogID PRIMARY KEY (ChangeLogID)
);

 

Setup the queue

CREATE QUEUE SecurityChangeQueue;
GO

CREATE SERVICE SecurityChangeService ON QUEUE SecurityChangeQueue
  ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]);
GO

 

Setup the event notificiation. If you check the “FOR”-clause, you will notice that these are the same actions as defined in the SQL Audit Specification.

CREATE EVENT NOTIFICATION CreateLoginNotification
    ON SERVER WITH FAN_IN
    FOR CREATE_LOGIN,ALTER_LOGIN,DROP_LOGIN,CREATE_USER,ALTER_USER,DROP_USER,ADD_SERVER_ROLE_MEMBER,DROP_SERVER_ROLE_MEMBER,ADD_ROLE_MEMBER,DROP_ROLE_MEMBER
    TO SERVICE 'SecurityChangeService', 'current database';
GO

 

Install the following stored procedure to log all the event notifications into the table we’ve just created. You might notice in the loop that I’m checking the version of SQL Server for some events. This is because the event notification content is different for SQL 2008 (R2) and SQL 2005.

USE [msdb];
GO
CREATE PROCEDURE [dbo].[LogSecurityChange]
WITH EXECUTE AS OWNER
AS
BEGIN
    SET NOCOUNT ON;
	DECLARE @version int
    DECLARE @message_body XML;
	set @version = (SELECT convert (int,REPLACE (LEFT (CONVERT (varchar, SERVERPROPERTY ('ProductVersion')),2), '.', '')))

    WHILE (1 = 1)
    BEGIN
       WAITFOR 
       ( 
         RECEIVE TOP(1) @message_body = message_body
         FROM dbo.SecurityChangeQueue
       ), TIMEOUT 1000;

       IF (@@ROWCOUNT = 1)
       BEGIN
		if CONVERT(SYSNAME, @message_body.query('data(/EVENT_INSTANCE/EventType)')) in ('DROP_USER','CREATE_USER','ALTER_USER') or @version>9
		BEGIN
			INSERT dbo.SecurityChangeLog(LoginName,UserName,DatabaseName,SchemaName,ObjectName,ObjectType,DDLCommand) 
			SELECT CONVERT(SYSNAME, @message_body.query('data(/EVENT_INSTANCE/LoginName)')), 
				CONVERT(SYSNAME, @message_body.query('data(/EVENT_INSTANCE/UserName)')),
				CONVERT(SYSNAME, @message_body.query('data(/EVENT_INSTANCE/DatabaseName)')),
				CONVERT(SYSNAME, @message_body.query('data(/EVENT_INSTANCE/DefaultSchema)')),
			    CONVERT(SYSNAME, @message_body.query('data(/EVENT_INSTANCE/ObjectName)')),
		        CONVERT(VARCHAR(50), @message_body.query('data(/EVENT_INSTANCE/ObjectType)')),
		        CONVERT(VARCHAR(MAX), @message_body.query('data(/EVENT_INSTANCE/TSQLCommand/CommandText)'))
		END
		ELSE
		BEGIN
			INSERT dbo.SecurityChangeLog(LoginName,UserName,DatabaseName,SchemaName,ObjectName,ObjectType,DDLCommand) 
			SELECT CONVERT(SYSNAME, @message_body.query('data(/EVENT_INSTANCE/LoginName)')), 
				CONVERT(SYSNAME, @message_body.query('data(/EVENT_INSTANCE/UserName)')),
				CONVERT(SYSNAME, @message_body.query('data(/EVENT_INSTANCE/DatabaseName)')),
				CONVERT(SYSNAME, @message_body.query('data(/EVENT_INSTANCE/SchemaName)')),
			    CONVERT(SYSNAME, @message_body.query('data(/EVENT_INSTANCE/ObjectName)')),
		        CONVERT(VARCHAR(50), @message_body.query('data(/EVENT_INSTANCE/ObjectType)')),
		        CONVERT(VARCHAR(MAX), @message_body.query('data(/EVENT_INSTANCE/EventType)')) + ' ' + 
				CONVERT(VARCHAR(MAX), @message_body.query('data(/EVENT_INSTANCE/RoleName)')) + ' FOR ' +
				CONVERT(VARCHAR(MAX), @message_body.query('data(/EVENT_INSTANCE/LoginType)')) + ' ' +
				CONVERT(VARCHAR(MAX), @message_body.query('data(/EVENT_INSTANCE/ObjectName)'))
		END
       END
    END
END

 

Last step is modifying the queue so that it will use the stored procedure and starts tracking the login and user changes.

ALTER QUEUE SecurityChangeQueue
WITH ACTIVATION
(
   STATUS = ON,
   PROCEDURE_NAME = dbo.LogSecurityChange,
   MAX_QUEUE_READERS = 1,
   EXECUTE AS OWNER
);
GO

Bring it all together

All the information is now logged on each individual server. I want all the audit log information in one central database so that the customer can run reports on it. First of all, I’ve created a database on one of the SQL Servers and created a table that will store all the audit logs of all the servers.

CREATE TABLE [dbo].[SecurityChangeLog](
	[ChangeLogID] [int] IDENTITY(1,1) NOT NULL,
	[ServerName] [sysname] NOT NULL,
	[LoginName] [sysname] NOT NULL,
	[DatabaseName] [sysname] NOT NULL,
	[ObjectName] [nvarchar](128) NULL,
	[ObjectType] [varchar](50) NULL,
	[DDLCommand] [nvarchar](max) NULL,
	[EventTime] [datetime2](7) NULL,
 CONSTRAINT [PK_ChangeLogID] PRIMARY KEY CLUSTERED 
(
	[ChangeLogID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

 

Next step is retrieving all the log information from each server. I was thinking of using an SSIS package for this but it looked more easily to use Powershell instead. The script below is based on the central management server that I’m using. First I’m retrieving all the SQL Servers where I have to collect the audit data. This is stored in the table msdb.dbo.sysmanagement_shared_registered_servers_internal of the central management server. Make sure that you change YOURGROUPID with the appropriate groupid of your CMS and YOURCMS with the instance name of your CMS.

Once we have the list, I execute a script GetAuditLog.sql against each instance to retrieve the audit information.

The final step will write the retrieved audit information into the database. Make sure you change the YOURDATABASENAME and YOURSQLSERVERINSTANCE in the script to your database name and SQL instance.

Schedule a SQL Agent Job to execute the powershell script on a daily basis.

 

#Set-ExecutionPolicy -ExecutionPolicy Unrestricted
Import-Module “sqlps” -DisableNameChecking
cls
$InputFile = "YOURFILEPATH + GetAuditLog.sql"

#Get the list of servers from the Central Management Server
$InstanceNameList = Invoke-Sqlcmd -Query "select server_name from msdb.dbo.sysmanagement_shared_registered_servers_internal 
                                          where server_group_id=YOURGROUPID" -ServerInstance "YOURCMS"
                                         
Clear-variable -Name results
Clear-variable -Name record

#Get Audit information
foreach($instanceName in $instanceNameList) 
{ 
    write-host "Executing query against server: " $instanceName.server_name
    $results += Invoke-Sqlcmd -InputFile $InputFile -ServerInstance $instanceName.server_name
}
#Save the Audit Log into the database
foreach($record in $results)
{
#write-host "ServerName: " $record.ServerName " " $record.DDLCommand
Invoke-Sqlcmd -Query "insert into [YOURDATABASENAME].[dbo].[SecurityChangeLog] ([ServerName],[LoginName],[DatabaseName],[ObjectName],[ObjectType],[DDLCommand],[EventTime])  
                      Values('$($record.ServerName)','$($record.LoginName)','$($record.DatabaseName)','$($record.ObjectName)','$($record.ObjectType)','$($record.DDLCommand)','$($record.EventTime)')" -ServerInstance "YOURSQLSERVERINSTANCE" 
}

 

Below you can find the SQL script that is used to retrieve the audit information in the powershell script. Dependent on the version we will read the audit file or the event notification table. I’m only selecting the logs of the last day because the powershell is scheduled to run on a daily basis.

USE MSDB
GO
declare @AuditLog  table (
LoginName sysname,
DatabaseName sysname,
ObjectName nvarchar(128),
ObjectType varchar(50),
DDLCommand nvarchar(4000),
EventTime datetime)
declare @LogFilePath varchar(256)
declare @version int
set @version = (SELECT convert (int,REPLACE (LEFT (CONVERT (varchar, SERVERPROPERTY ('ProductVersion')),2), '.', '')))
IF (@version=10 and (select convert(varchar,SERVERPROPERTY('Edition'))) not like '%Standard%') OR (@version=11)
BEGIN
	select @LogFilePath=log_file_path from sys.server_file_audits
	where name='Audit Login Changes'
	IF @LogFilePath IS NOT NULL
	BEGIN
		set @LogFilePath=@LogFilePath +'*.sqlaudit'
		insert into @AuditLog
		SELECT session_server_principal_name,database_name,object_name,m.class_type_desc,statement,event_time FROM sys.fn_get_audit_file (@LogFilePath,default,default) f
		INNER JOIN sys.dm_audit_class_type_map m on m.class_type=f.class_type;
		select serverproperty('Servername') as ServerName,LoginName,DatabaseName,ObjectName,ObjectType,replace(DDLCommand,'''','''''') as DDLCommand,EventTime from @AuditLog
		where EventTime>dateadd(dd,-1,getdate())
	END
END
ELSE
BEGIN
	IF exists (select name from msdb.sys.tables where name='SecurityChangeLog')
		select serverproperty('Servername') as ServerName,LoginName,DatabaseName,ObjectName,ObjectType,replace(DDLCommand,'''','''''') as DDLCommand,EventTime from msdb.dbo.SecurityChangeLog
	    where EventTime>dateadd(dd,-1,getdate()) and DDLCommand<>''
END

Report

To make it easy for the customer to retrieve the data, I’ve created this report.

Security ChangesI know that this solution is not really secure because people can just stop the audit, disable the queue or delete the records from the tables but I think this is a good start to track the login and user modifications.

Have fun!

Configure Cluster MSDTC with Powershell

Today, I configured a new SQL Server Failover Cluster with command line. The goal is to have a fully automatic installation of a SQL Server Failover Cluster.  By using Powershell, I was already able to add the MSDTC resource.  My next challenge was to configure it automatically Knipogende emoticon
I figured out that the MSDTC settings were in the registry under HKEY_LOCAL_MACHINEClusterResources. The problem was that the folders below this registry key are having an ID, so I had to find a way to know which one is used for MSDTC.
Well, I managed to configure it with the following script.
Import-Module FailoverClusters;
$InstanceName=$args[0]
$msdtcKey = Get-ChildItem “Registry::HKEY_LOCAL_MACHINEClusterResources” | Where-Object {([string](get-itemproperty -path “Registry::$_”).”Name”) -match “MSDTC-SQL Server ($InstanceName)”}
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTC” -name “AllowOnlySecureRPCCalls” -value 0
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTC” -name “TurnOffRpcSecurity” -value 1
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTCSecurity” -name “NetworkDTCAccess” -value 1
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTCSecurity” -name “NetworkDTCAccessClients” -value 1
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTCSecurity” -name “NetworkDTCAccessInbound” -value 1
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTCSecurity” -name “NetworkDTCAccessOutbound” -value 1
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTCSecurity” -name “NetworkDTCAccessTransactions” -value 1
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTCSecurity” -name “XATransactions” -value 1
stop-ClusterResource “MSDTC-SQL Server ($InstanceName)”
start-ClusterResource “MSDTC-SQL Server ($InstanceName)”

Let’s have a detailed look
$msdtcKey = Get-ChildItem “Registry::HKEY_LOCAL_MACHINEClusterResources” | Where-Object {([string](get-itemproperty -path “Registry::$_”).”Name”) -match “MSDTC-SQL Server ($InstanceName)”}
First, I get all the child items of the registry HKEY_LOCAL_MACHINEClusterResources.  In each of the child items, I’m searching for a name that matches “MSDTC-SQL Server (<MyinstanceName>)”
Of course, you should change the “match” parameter when you have used another name for your MSDTC. Note: The parameter $InstanceName should be the name of your SQL Server Instance
As a result, I get the correct key!
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTC” -name “AllowOnlySecureRPCCalls” -value 0
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTC” -name “TurnOffRpcSecurity” -value 1
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTCSecurity” -name “NetworkDTCAccess” -value 1
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTCSecurity” -name “NetworkDTCAccessClients” -value 1
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTCSecurity” -name “NetworkDTCAccessInbound” -value 1
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTCSecurity” -name “NetworkDTCAccessOutbound” -value 1
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTCSecurity” -name “NetworkDTCAccessTransactions” -value 1
Set-ItemProperty -path “Registry::$msdtcKeyMSDTCPRIVATEMSDTCSecurity” -name “XATransactions” -value 1
I have used that key to set the correct registry values, according to my customer needs.
More information can also be found on
stop-ClusterResource “MSDTC-SQL Server ($InstanceName)”
start-ClusterResource “MSDTC-SQL Server ($InstanceName)”
Finally, you need to stop and start the cluster resource in order to take the changes into effect.
Note: The parameter $InstanceName should be the name of your SQL Server Instance
Have fun with it!