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.
- Controlling SQL services using PowerShell
- Using PowerShell to look at services configuration
- Using PowerShell to list the SQL services
About the author
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