Inventory SQL Server Environment for Service Accounts, Versions, Ports

By: Bernard Black | Updated: 2022-01-12 | Comments | Related: More > Database Administration

Problem

Your security team or perhaps an auditor wants to see the accounts your SQL Server services are running under. They also want to know what port SQL Server is using and the version of SQL Server. In this tutorial we cover a PowerShell script you can use to quickly inventory your environment.

Solution

Using a table of your servers and the PowerShell script below, you can get all the service accounts. The script also gets the port SQL Server is using and the SQL Server version that's running. It creates a .CSV file that can be read into an Excel spreadsheet. Your boss and security will be happy. You might not be so happy when they ask you to change a bunch, but's that's a DBAs life in today's computer security world.

The Query Used by the PowerShell Script

Below is the heart of the PowerShell script, it is a query that utilizes the master.dbo.xp_regread extended stored procedure that reads the registry on a server. Apparently, this system stored procedure (extended) returns 2 fields, the value and data. It is undocumented so we are on our own when using it. If you run it without the @OUTPUT parameter, it returns 2 fields. If you put an @OUTPUT for a variable, the second field populates the data field. I'm not sure why the value field is not brought back.

The queries are pretty straight forward except for the engine, the agent, and the SSIS service. The SSIS service uses a different registry name for each version of SQL Server. I believe I have all of them included up to SQL Server 2019. The engine service is different for default instances and named instances and you will see two areas below in the code. Ditto for SQL Server Agent.

----SQLBrowser  SQLWriter, MSSQLServerOLAPService, ReportServer, MSDTSServer110 etc
DECLARE @serv NVARCHAR(128);
IF @@SERVICENAME = 'MSSQLSERVER'
BEGIN
 EXEC master.dbo.xp_regread 
 'HKEY_LOCAL_MACHINE', 
 'SYSTEM\CurrentControlSet\services\MSSQLSERVER',
 'ObjectName', 
 @serv OUTPUT;
END

IF @@SERVICENAME <> 'MSSQLSERVER'
BEGIN
 DECLARE @inst_name NVARCHAR(128);
 SET @inst_name = 'SYSTEM\CurrentControlSet\services\MSSQL$' + @@SERVICENAME ;
 EXEC master.dbo.xp_regread 
 'HKEY_LOCAL_MACHINE',
 @inst_name,
 'ObjectName',
 @serv OUTPUT;
END

---select @serv
DECLARE @serv2 NVARCHAR(128);
DECLARE @inst2_name NVARCHAR(128);
IF @@SERVICENAME = 'MSSQLSERVER'
BEGIN
 EXEC master.dbo.xp_regread 
 'HKEY_LOCAL_MACHINE', 
 'SYSTEM\CurrentControlSet\services\SQLSERVERAGENT',
 'ObjectName', 
 @serv2 OUTPUT; 
END

IF @@SERVICENAME <> 'MSSQLSERVER'
BEGIN
 SET @inst2_name = 'SYSTEM\CurrentControlSet\services\SQLAGENT$' + @@SERVICENAME ;
 EXEC master.dbo.xp_regread 
 'HKEY_LOCAL_MACHINE', 
 @inst2_name,
 'ObjectName', 
 @serv2 OUTPUT; 
END

 DECLARE @serv3 NVARCHAR(128); 
 EXEC master.dbo.xp_regread 
 'HKEY_LOCAL_MACHINE', 
 'SYSTEM\CurrentControlSet\services\SQLBrowser', 
 'ObjectName', 
 @serv3 OUTPUT; 

 DECLARE @serv4 NVARCHAR(128); 
 EXEC master.dbo.xp_regread 
 'HKEY_LOCAL_MACHINE', 
 'SYSTEM\CurrentControlSet\services\SQLWriter', 
 'ObjectName', 
 @serv4 OUTPUT; 

 DECLARE @serv5 NVARCHAR(128); 
 EXEC master.dbo.xp_regread 
 'HKEY_LOCAL_MACHINE', 
 'SYSTEM\CurrentControlSet\services\ReportServer',
 'ObjectName', 
 @serv5 OUTPUT; 

 DECLARE @serv6 NVARCHAR(128); 
 EXEC master.dbo.xp_regread 
 'HKEY_LOCAL_MACHINE', 
 'SYSTEM\CurrentControlSet\services\MSSQLServerOLAPService',  
  'ObjectName', 
 @serv6 OUTPUT; 

 DECLARE @serv7 NVARCHAR(128); 
 EXEC master.dbo.xp_regread 
 'HKEY_LOCAL_MACHINE', 
 'SYSTEM\CurrentControlSet\services\MSDTSServer100',   ---2008r2, 2008
 'ObjectName', 
 @serv7 OUTPUT; 

 DECLARE @serv8 NVARCHAR(128); 
 EXEC master.dbo.xp_regread 
 'HKEY_LOCAL_MACHINE', 
 'SYSTEM\CurrentControlSet\services\MSDTSServer110',     ---2012
 'ObjectName', 
 @serv8 OUTPUT; 

 DECLARE @serv9 NVARCHAR(128); 
 EXEC master.dbo.xp_regread 
 'HKEY_LOCAL_MACHINE', 
 'SYSTEM\CurrentControlSet\services\MSDTSServer120',    ----2014
 'ObjectName', 
 @serv9 OUTPUT; 

 DECLARE @serv10 NVARCHAR(128); 
 EXEC master.dbo.xp_regread 
 'HKEY_LOCAL_MACHINE', 
 'SYSTEM\CurrentControlSet\services\MSDTSServer130',  ---2016
 'ObjectName', 
 @serv10 OUTPUT; 
   
 DECLARE @serv11 NVARCHAR(128); 
 EXEC master.dbo.xp_regread 
 'HKEY_LOCAL_MACHINE', 
 'SYSTEM\CurrentControlSet\services\MSDTSServer140',  ---2017
 'ObjectName', 
 @serv11 OUTPUT; 

 DECLARE @serv12 NVARCHAR(128); 
 EXEC master.dbo.xp_regread 
 'HKEY_LOCAL_MACHINE', 
 'SYSTEM\CurrentControlSet\services\MSDTSServer150',  ---2019
 'ObjectName', 
 @serv12 OUTPUT; 
  
 DECLARE @serv13 NVARCHAR(128); 
 EXEC master.dbo.xp_regread 
 'HKEY_LOCAL_MACHINE', 
 'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP',  ---2019
 'TcpPort', 
 @serv13 OUTPUT; 

select @serv as 'Engine Acct', @serv2 as 'Agent Acct', @serv3 as 'SQL Browser Acct', @serv4 as 'SQL Writer Acct'
 , @serv5 as 'SSRS Acct', @serv6 as 'SSAS Acct',
 CASE
   WHEN  @serv7 is NOT NULL then @serv7
   WHEN  @serv8 is NOT NULL then @serv8
   WHEN  @serv9 is NOT NULL then @serv9
   WHEN  @serv10 is NOT NULL then @serv10
   WHEN  @serv11 is NOT NULL then @serv11
   WHEN  @serv12 is NOT NULL then @serv12
 end as 'SSIS Acct'
 , @serv13 as 'TCP Port'
 , @@VERSION

The PowerShell Script

Note, the script is using integrated security to connect to a database named "DBName" on a server called "SQLServer" to a table called "TBL_sql_servers". This means that the person running this script needs read access to the database. You can adjust these values as needed in the script below.

You will need to create the table "TBL_sql_servers" and add a column named "is_sqlserver" as a varchar(255) and then enter a list of servers you want to check.

It creates a .NET esque object called a dataset which it fills with the servers it reads from the database. As it creates the local variables with the service logons it will dump them to a file called: "C:\Junk\SQL_Services_Logins.csv". You can change this to fit your directory structure. It is setup to append to the csv file so if you run it twice the file will have records from the first run and the second.

Note also the continuation tic marks "`" at the end of each line for the query.

$SqlQuery = "select is_sqlserver from TBL_sql_servers  " 
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = SQLServer; Database = DBName; Integrated Security = True" 
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd 
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet, "Server_Names") | Out-Null

$SqlConnection.Close() 

#Counter variable for rows
$intRow = 1

#Read thru the contents of the SQL_Servers.txt file
####foreach ($instance in get-content "c:\junk\SQL_Servers.txt")
foreach ($row in $DataSet.Tables["Server_Names"].rows)
{
 ###    $instance = $row[1].ToString().Trim()$row
      $instance = $row.is_sqlserver
Write-Host $instance

 $intRow++

$STR="DECLARE @serv NVARCHAR(128); `
IF @@SERVICENAME = 'MSSQLSERVER' `
BEGIN `
 EXEC master.dbo.xp_regread  `
 'HKEY_LOCAL_MACHINE', `
'SYSTEM\CurrentControlSet\services\MSSQLSERVER', `
'ObjectName', `
 @serv OUTPUT; `
END `
 IF @@SERVICENAME <> 'MSSQLSERVER' `
BEGIN `
 DECLARE @inst_name NVARCHAR(128); `
 SET @inst_name = 'SYSTEM\CurrentControlSet\services\MSSQL$' + @@SERVICENAME ; `
 EXEC master.dbo.xp_regread  `
 'HKEY_LOCAL_MACHINE', `
 @inst_name, 'ObjectName', `
 @serv OUTPUT; `
END `
DECLARE @serv2 NVARCHAR(128); `
DECLARE @inst2_name NVARCHAR(128); `
 IF @@SERVICENAME = 'MSSQLSERVER' `
BEGIN `
 EXEC master.dbo.xp_regread  `
 'HKEY_LOCAL_MACHINE', `
 'SYSTEM\CurrentControlSet\services\SQLSERVERAGENT', `
 'ObjectName',  @serv2 OUTPUT;  `
END `
IF @@SERVICENAME <> 'MSSQLSERVER' `
BEGIN `
 SET @inst2_name = 'SYSTEM\CurrentControlSet\services\SQLAGENT$' + @@SERVICENAME ; `
 EXEC master.dbo.xp_regread  `
 'HKEY_LOCAL_MACHINE', `
 @inst2_name, 'ObjectName', `
 @serv2 OUTPUT; `
END `
 DECLARE @serv3 NVARCHAR(128);  `
 EXEC master.dbo.xp_regread `
 'HKEY_LOCAL_MACHINE', `
  'SYSTEM\CurrentControlSet\services\SQLBrowser', `
 'ObjectName', `
 @serv3 OUTPUT; `
 DECLARE @serv4 NVARCHAR(128); `
 EXEC master.dbo.xp_regread `
 'HKEY_LOCAL_MACHINE', `
 'SYSTEM\CurrentControlSet\services\SQLWriter', `
 'ObjectName', `
 @serv4 OUTPUT; `
 DECLARE @serv5 NVARCHAR(128); `
 EXEC master.dbo.xp_regread `
 'HKEY_LOCAL_MACHINE', `
'SYSTEM\CurrentControlSet\services\ReportServer', `
 'ObjectName', `
 @serv5 OUTPUT; `
 DECLARE @serv6 NVARCHAR(128); `
 EXEC master.dbo.xp_regread `
 'HKEY_LOCAL_MACHINE', `
 'SYSTEM\CurrentControlSet\services\MSSQLServerOLAPService', `
  'ObjectName', `
 @serv6 OUTPUT; `
 DECLARE @serv7 NVARCHAR(128); `
 EXEC master.dbo.xp_regread `
 'HKEY_LOCAL_MACHINE', `
'SYSTEM\CurrentControlSet\services\MSDTSServer100', `
 'ObjectName', `
 @serv7 OUTPUT; `
 DECLARE @serv8 NVARCHAR(128); `
 EXEC master.dbo.xp_regread `
 'HKEY_LOCAL_MACHINE', `
   'SYSTEM\CurrentControlSet\services\MSDTSServer110',  `
 'ObjectName', `
 @serv8 OUTPUT; `
 DECLARE @serv9 NVARCHAR(128); `
 EXEC master.dbo.xp_regread `
 'HKEY_LOCAL_MACHINE', `
 'SYSTEM\CurrentControlSet\services\MSDTSServer120',  `
 'ObjectName', `
 @serv9 OUTPUT; `
 DECLARE @serv10 NVARCHAR(128); `
 EXEC master.dbo.xp_regread `
 'HKEY_LOCAL_MACHINE', `
  'SYSTEM\CurrentControlSet\services\MSDTSServer130',  `
 'ObjectName', `
 @serv10 OUTPUT; `
 DECLARE @serv11 NVARCHAR(128); `
 EXEC master.dbo.xp_regread `
 'HKEY_LOCAL_MACHINE', `
  'SYSTEM\CurrentControlSet\services\MSDTSServer140', `
 'ObjectName', `
 @serv11 OUTPUT; `
 DECLARE @serv12 NVARCHAR(128); `
  EXEC master.dbo.xp_regread `
 'HKEY_LOCAL_MACHINE', `
  'SYSTEM\CurrentControlSet\services\MSDTSServer150', `
 'ObjectName', `
 @serv12 OUTPUT; `
  DECLARE @serv13 NVARCHAR(128); `
  EXEC master.dbo.xp_regread `
 'HKEY_LOCAL_MACHINE', `
  'SOFTWARE\MICROSOFT\MSSQLSERVER\MSSQLSERVER\SUPERSOCKETNETLIB\TCP', `
 'TcpPort', `
 @serv13 OUTPUT; `
 select @@SERVERNAME as 'Server', @serv as 'Engine Acct', @serv2 as 'Agent Acct', @serv3 as 'SQL Browser Acct', @serv4 as 'SQL Writer Acct' `
 , @serv5 as 'SSRS Acct', @serv6 as 'SSAS Acct', `
 CASE `
   WHEN  @serv7 is NOT NULL then @serv7 `
   WHEN  @serv8 is NOT NULL then @serv8 `
   WHEN  @serv9 is NOT NULL then @serv9 `
   WHEN  @serv10 is NOT NULL then @serv10 `
   WHEN  @serv11 is NOT NULL then @serv11 `
   WHEN  @serv12 is NOT NULL then @serv12 `
end as 'SSIS Acct' `
, @serv13 as 'TCP Port' `
, LEFT(@@VERSION,40) as 'SQL Version'  "

Invoke-Sqlcmd -ServerInstance $instance -Database "master" -Query $STR | Export-CSV  "C:\Junk\SQL_Services_Logins.csv" -noType -append
}

clear $DataSet.Tables[0] 
cls

What the output looks like

The PowerShell puts everything in a file called: "C:\Junk\SQL_Services_Logins.csv".

Below is what the .csv file looks like.

Right click on the file and open in Excel and below is what it looks like in Excel.

Next Steps

Here are some links that show how to use PowerShell to look at the SQL service's and control them.

About the author

Bernard Black is a DBA for Beacon Health System in South Bend, IN and has been working with databases for over 15 years.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Article Last Updated: 2022-01-12

Collect SQL Server Services Accounts, Versions, Ports and more