Category: SQL Server

Hopefully you already know everything about your SQL estate, including what services are installed and what’s running them, either because it’s so small you just know, or, preferably, you have some kind of CMDB. But what if you don’t? Powershell comes to the rescue, of course.

This post details a script to capture basic information – service name, account, start mode and status – about the database engine, Agent and any SSRS/SSAS/SSRS installs. It requires remote WMI connectivity and, for SSIS, SMB access to enabled administrative shares on the drives where the service is installed.

(Note – if all you’re after is the Database Engine and/or SQL Agent info, and you’re running SQL 2008 R2 SP1 or higher, no need to carry on reading – just use the sys.dm_server_services DMV instead, it’s easy.)

To begin we need some kind of data source containing both the hostnames and instance names (MSSQLSERVER if default instance) of the servers in the estate – as we’ll be connecting to the hostname but making checks using the instance name. Either stored in a table;

$instancesquery = "SELECT Hostname, InstanceName
                    FROM dbo.Instances"

$instances = Invoke-Sqlcmd -ServerInstance SQL2017A -Database 'TestDB' -Query $instancesquery

or from another source, say, a csv file – in this case an example of a single instance, which I’ll use as an example source for the rest of the post;

$instances = Import-Csv -Path "C:\temp\servers.txt"

Then we can loop over the servers and use the Get-WmiObject Win32_Service cmdlet. For the database engine, Agent, SSRS and SSAS this is straightforward – we’re filtering on the service name looking for a reference to the instance, then disregarding any CEIP services and pulling out a few pieces of information. For example, a snippet of the script checking for a SSAS install;

$SSAS = Get-WmiObject Win32_Service -ComputerName $instance.Hostname | Where-Object -Property DisplayName -EQ "SQL Server Analysis Services ($($instance.InstanceName))" | Where-Object -Property DisplayName -NotLike "*CEIP*" | Select-Object DisplayName, StartName, State, StartMode
Write-Output $SSAS

SSIS is a bit trickier. You may have noticed that, unlike for the Database Engine, SSRS or SSAS, the instance name is not part of the service name;

So we can’t just filter on the service name to check which instance the service is installed as part of. Instead we need to find the MsDtsSrvr.ini.xml file associated with the service (by using the service PathName), open it up as XML (this is where the requirement for SMB and administrative share access arises) and look at the DtsServiceConfiguration.TopLevelFolders.Folder.ServerName element;

$ConfigXMLPath = ($SSIS.PathName.Substring(0,$SSIS.PathName.IndexOf('MsDtsSrvr.exe')) + 'MsDtsSrvr.ini.xml') -replace '"', ''

$ConfigXMLPath = "\\$($instance.Hostname)\" + $SSIS.PathName.Substring(1,1) + '$\' + $ConfigXMLPath.Substring(3)

[xml]$ConfigXML = Get-Content -Path $ConfigXMLPath

if($ConfigXML.DtsServiceConfiguration.TopLevelFolders.Folder.ServerName -like "*$($instance.InstanceName)*")
{
    Write-Output $SSIS
}
elseif(($ConfigXML.DtsServiceConfiguration.TopLevelFolders.Folder.ServerName -EQ ".") -and ($instance.InstanceName -eq 'MSSQLSERVER'))
{
    Write-Output $SSIS
}

An example output from the whole script, ran on a single server;

This shows I have the database engine and agent running from MSAs, and two extra services installed – SSAS running as a local service and SSIS running as an MSA, all set to auto-start and only SSIS not currently running.

The entire skeleton script is below (github link), using the CSV file example for retrieving host/instance names as a base and simply writing the output to console – it’s easily modifiable to change the source and/or save the output to a file or table.

<#       
                
$instancesquery = "SELECT Hostname, InstanceName       
                
                   FROM dbo.Instances"       
                
       
                
$instances = Invoke-Sqlcmd -ServerInstance SQL2017A -Database 'TestDB' -Query $instancesquery       
                
#>       
      
$instances = Import-Csv -Path "C:\temp\servers.txt"       
                          
foreach ($instance in $instances)                     
{       
    Write-Output "Connecting to $($instance.Hostname)\$($instance.InstanceName).."       
                         
    try       
    {               
        $test = Get-WmiObject Win32_Service -ComputerName $instance.Hostname -ErrorAction Stop                 
    }       
    catch       
    {       
        Write-Output "Error connecting remotely to WMI"       
                
        continue       
    }       
                  
    # Database Engine                
    $DatabaseEngine = Get-WmiObject Win32_Service -ComputerName $instance.Hostname | Where-Object -Property DisplayName -EQ "SQL Server ($($instance.InstanceName))" | Select-Object DisplayName, StartName, State, Startmode       

    Write-Output $DatabaseEngine       
                

    # SQL Agent               
    $SQLAgent = Get-WmiObject Win32_Service -ComputerName $instance.Hostname | Where-Object -Property DisplayName -EQ "SQL Server Agent ($($instance.InstanceName))" | Select-Object DisplayName, StartName, State, Startmode       

    Write-Output $SQLAgent       
                  
    # SSAS       
    $SSAS = Get-WmiObject Win32_Service -ComputerName $instance.Hostname | Where-Object -Property DisplayName -EQ "SQL Server Analysis Services ($($instance.InstanceName))" | Where-Object -Property DisplayName -NotLike "*CEIP*" | Select-Object DisplayName, StartName, State, StartMode       

    Write-Output $SSAS       

    # SSRS       
    $SSRS = Get-WmiObject Win32_Service -ComputerName $instance.Hostname | Where-Object -Property DisplayName -EQ "SQL Server Reporting Services ($($instance.InstanceName))" | Where-Object -Property DisplayName -NotLike "*CEIP*" | Select-Object DisplayName, StartName, State, StartMode       
                
    Write-Output $SSRS       

    #SSIS       
    $SSISinstances = Get-WmiObject Win32_Service -ComputerName $instance.Hostname | Where-Object -Property DisplayName -Like "SQL Server Integration Services*" | Where-Object -Property DisplayName -NotLike "*CEIP*" | Select-Object DisplayName, PathName, StartName, State, StartMode       
                

    try       
    {       
        foreach($SSIS in $SSISinstances)       
        {          
            $ConfigXMLPath = ($SSIS.PathName.Substring(0,$SSIS.PathName.IndexOf('MsDtsSrvr.exe')) + 'MsDtsSrvr.ini.xml') -replace '"', ''       

            $ConfigXMLPath = "\\$($instance.Hostname)\" + $SSIS.PathName.Substring(1,1) + '$\' + $ConfigXMLPath.Substring(3)       

            [xml]$ConfigXML = Get-Content -Path $ConfigXMLPath              

            if ($ConfigXML.DtsServiceConfiguration.TopLevelFolders.Folder.ServerName -like "*$($instance.InstanceName)*")       
            {       
                Write-Output ($SSIS | Select-Object DisplayName, StartName, State, StartMode)       
            }       
            elseif (($ConfigXML.DtsServiceConfiguration.TopLevelFolders.Folder.ServerName -EQ ".") -and ($instance.InstanceName -eq 'MSSQLSERVER'))       
            {       
                Write-Output ($SSIS | Select-Object DisplayName, StartName, State, StartMode)       
            }       
        }       
    }       
    catch       
    {       
        Write-Output "SSIS Error: " + $error[0]       
    }       
}

The login auditing setting on a SQL Server instance controls the auditing of logins – either all, successful only, failed only or none. It’s set via SSMS on the Server properties dialog;

and is responsible for these types of message in the log;

You might want to change this for several reasons – perhaps you have strict auditing requirements so want everything logged, or perhaps you only care about failures – especially if you have a noisy application that floods the log with successful logins.

It’s straightforward enough to change in SSMS – though note that a service restart is required for changes to take effect – but what if you want to change this setting on a bunch of servers? Fortunately, it can be programmatically read/changed using the (undocumented) xp_instance_regread and xp_instance_regwrite extended stored procedures, reading from/writing to the Windows registry value AuditLevel (specifically, the value in the correct key for the instance the proc is executed from) – that the setting in SSMS actually represents;

This key maps to the following values;

• 0 – None
• 1 – Successful logins only
• 2 – Failed logins only
• 3 – All logins only

To retrieve the value, we can use xp_instance_regread ;

DECLARE @result INT
EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel', @result OUTPUT
SELECT @result AS 'AuditLevel'

To set it, we use xp_instance_regwrite. An example of setting it to 2 (Failed logins only);

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel', REG_DWORD, 2

So by calling this procedure we can script out a read/change to the setting to multiple instances at once using Powershell. (Yep, we could directly modify the registry with Powershell and skip the call, but doing it this way benefits from the wrapping of the registry modification in the SQL extended proc, and thus the correct key for the instance being automatically used thanks to the ‘instance’ versions of the reg read/write procs vs the standard xp_regread/regwrite)

Retrieving the key is a straightforward iteration over instances. In this example I’ve populated an $instances variable with an array of instance names, but this can be substituted with reading from a text file or database;

$regread = "DECLARE @result INT
            EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'AuditLevel', @result OUTPUT
            SELECT @result AS 'AuditLevel'"

$instances = @('SQL1', 'SQL2', 'SQL3')

foreach($instance in $instances)
{
    try
    {          
        $flag = 99
        $flag = (Invoke-Sqlcmd -ServerInstance $instance -Query $regread -ConnectionTimeout 3 -QueryTimeout 3 -ErrorAction Stop).AuditLevel
       
        Write-Host "$($instance): $($flag)"
     }
     catch
     {
         Write-Host "$($instance): Error"
         Write-Host $error[0]
     }
}

Setting the key is also a straightforward iteration. In this example I’m also setting the value to ‘2’ (Failed logins only);

$regwrite = "EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', 
                N'Software\Microsoft\MSSQLServer\MSSQLServer', 
                N'AuditLevel', REG_DWORD, 2"

$instances = @('SQL1', 'SQL2', 'SQL3')

foreach($instance in $instances)
{
    try
    {          
        Invoke-Sqlcmd -ServerInstance $instance -Query $regwrite -ConnectionTimeout 3 -QueryTimeout 3 -ErrorAction Stop
        Write-Host "$($instance.ConnectionString): Success"
    }
    catch
    {
        Write-Host "$($instance): Error"
        Write-Host $error[0]
    }
}

After modification, the first script can be re-ran to confirm the modifications – and don’t forget a service restart is required to kick the setting into action.

Disclaimer – the registry-editing procedures used here are undocumented, and editing the registry is generally best avoided – any such code should be tested thoroughly before use in production.