Powerful Connections: How to Connect to SQL Server Using PowerShell

 

PowerShell is a powerful scripting language that can be used to automate tasks in Microsoft SQL Server. In this blog, we will discuss how to connect to a SQL Server instance from PowerShell.

Step 1: Install SQL Server PowerShell Module

To connect to SQL Server from PowerShell, you need to install the SQL Server PowerShell module. You can download the module from the Microsoft download centre or install it using the PowerShell Gallery.

To install the module using PowerShell Gallery, open PowerShell as an administrator and run the following command:

Install-Module -Name SqlServer -AllowClobber 

This will install the latest version of the SQL Server PowerShell module. If any prompts appears click yes or yes to all.

To view the versions of the SqlServer module installed

 

Get-Module SqlServer -ListAvailable

if SqlServer module is successfully installed you may receive output like below 

Step 2: Import the SQL Server PowerShell Module

Once the module is installed, you need to import it into your PowerShell session. To do this, run the following command:

Import-Module SqlServer

if you received error like below please execute script to set ExecutionPolicy

Import-Module : File C:\Program Files\WindowsPowerShell\Modules\SqlServer\22.0.59\SqlNotebook.psm1 cannot be loaded

because running scripts is disabled on this system. For more information, see about_Execution_Policies at

https:/go.microsoft.com/fwlink/?LinkID=135170.

At line:1 char:1

+ Import-Module SqlServer

+ ~~~~~~~~~~~~~~~~~~~~~~~

    + CategoryInfo          : SecurityError: (:) [Import-Module], PSSecurityException

    + FullyQualifiedErrorId : UnauthorizedAccess,Microsoft.PowerShell.Commands.ImportModuleCommand

Script to set ExecutionPolicy

Set-ExecutionPolicy -ExecutionPolicy RemoteSigned -Scope CurrentUser

Step 3: Connect to SQL Server

To connect to a SQL Server instance from PowerShell, you need to use the Invoke-Sqlcmd cmdlet. The cmdlet takes the following parameters:

• ServerInstance: The name of the SQL Server instance you want to connect to.
• Database: The name of the database you want to connect to.
• Credential: The Windows or SQL Server authentication credentials to use to connect to the instance.
• EncryptConnection: Whether to use an encrypted connection to the instance.
• Query: The SQL query to run.

Here's an example of how to use the Invoke-Sqlcmd cmdlet to connect to a SQL Server instance:

Copied!

# https://www.dbascrolls.com/

$server = "localhost" #Need to enter Servername
$database = "mydatabase" #Need to enter Database name
$username = "myusername" #Need to enter SQL user name
$password = "mypassword" #Need to enter SQL Password

$credential = New-Object System.Management.Automation.PSCredential ($username, $(ConvertTo-SecureString $password -AsPlainText -Force))

$query = "SELECT top 1 * FROM newtable"

$results = Invoke-Sqlcmd -ServerInstance $server -Database $database -Query $query  -Credential $credential -TrustServerCertificate  -EncryptConnection
$results | Format-Table

This will execute the SQL query and return the results in a table format.

Connecting to SQL Server from PowerShell is a simple process that can be done using the Invoke-Sqlcmd cmdlet. By automating tasks in PowerShell, you can save time and improve productivity in your SQL Server environment.