This post is designed to show how to execute MSSQL Database queries using scripts written in PowerShell ISE. In this example, the connection details for the SQL instance are being retrieved from a CSV, and the query is being executed. Once the query is executed, the results of the query are being outputted to a CSV file for consumption by another application.
- TLS 1.2 is installed
- PowerShell ISE 5.0 or higher is being used
- .NET Framework 4.5 is installed
- Internet Connection is available
- Account with Local Admin privileges is being used
- NuGet Package Library is installed (at least version: 220.127.116.11 – Requires TLS 1.2 to be enabled)
- Firewall access from Windows Machine to MSSQL Database is open
Installation Tasks and Steps
Task 1 – Import the MSSQL Module
This first task will import the MSSQL module into PowerShell. The Module contains certain commands and parameters that need to be executed to complete this script. Follow the below instructions to install the module:
- Launch the PowerShell ISE as an Administrator
- Execute the following command:
Task 2 – Create CSV File with Connection Details (Optional if CSV is not the required input method)
- Open a text editor to create the CSV file
- Create header columns with the connection details as a header
- Below is an example:
- SQL Server Hostname:
- Below is an example:
- Save the file in a directory that can be navigated to as a CSV file
Task 3 – Complete the Script to execute the command
This script will execute the SQL Query and export it to a CSV file for consumption:
To conclude this article, this script is meant to allow an Administrator to execute MSSQL queries against a SQL instance using PowerShell and export the results to a CSV file. This query is getting a list of all the users authenticated to a particular SQL database. This script should work with almost any query that can be executed.
When utilizing this script, please remember to check the same query in SQL Developer first to ensure that the results that return are the expected results. User permissions and database rights still apply when using this script.
Looking for additional help with querying the MSSQL DB server for users using Powershell? ISX is an elite IAM security firm that offers boundless expertise in a range of cybersecurity and business process services, including Powershell. Take your interoperability to the next level, and contact an ISX consultant today.