This post was created to show how to execute MYSQL 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
- Internet Connection is available
- Account with Local Admin privileges is being used to execute script
- 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 MYSQL Database is open
- Remote Access allowed on MYSQL Server (Default security when installing MYSQL disables this)
- Enable MYSQL Native Authentication
Installation Tasks and Steps
Task 1 – Install and Import the MYSQL Module
This first task will import the MYSQL 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 (The module, named SimplySql, can be searched for and downloaded via the Internet or found here:
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:
- User ID:
- 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 MYSQL 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 MYSQL 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.