Connect to MS SQL Server using PowerShell

Requirements before going forward.

• Current version of PowerShell
• SQL Server Native Client - needs to be installed separately from here - https://learn.microsoft.com/en-us/sql/relational-databases/native-client/applications/installing-sql-server-native-client?view=sql-server-ver15
• Permissions on the SQL server

Connecting to MS SQL Server using PowerShell

Install the SQLServer module in PowerShell.

Install-Module -Name SqlServer -AllowClobber -Force

This command will install the SqlServer module which includes SMO (Server Management Objects).

Basic operations

To ease readability and clarity of code, create variables for connection strings and database names.

$serverName = "my-server"
$databaseName = "mydata"

$sqlConnection = New-Object System.Data.SqlClient.SqlConnection
$sqlConnection.ConnectionString = "Server=$serverName;Database=$databaseName;Integrated Security=True;"

The Integrated Security=True parameter indicates that we are using the credentials of the logged in user to connect to the SQL server instance. This is ideal, because it doesn't expose the credentials to the script.

Establish the connection

$sqlConnection.Open()

If we want to use SQL Server Authentication, we will need to adjust the connection string to include user and password.

$sqlConnection.ConnectionString = "Server=$serverName;Database=$databaseName;User Id=<username>;Password=<password>;"
$sqlConnection.Open()

Obviously, this would not be ideal, as it would be visible to whoever has access to the script.

Basic queries

$command = $sqlConnection.CreateCommand()
$command.CommandText = "SELECT * FROM <our table>"
$reader = $command.ExecuteReader()

The variable $reader will now contain the results of the SELECT query.

To display the contents of this variable, you can:

while ($reader.Read()){
        Write-Host $reader["columeName"]
}
$reader.Close()

Close the connection

It is always best practice to close the database connection after completing the operations you wanted to perform to free up the resources used during the operations.

$sqlConnection.Close()