How To Connect SQL Server via PowerShell?

PowerShell provides the SQLServer module in order to connect and use the SQL database servers. We can use the SQLServer module in order to connect and run queries in the connected database server. In this tutorial, we examine how to import and use the SQLServer module to connect SQL Server Database.

Import SQLServer Module

We should use the Import-Module command in order to use the functions.

PS> Import-Module SqlServer

Display SqlServer Module Information

We can use the Get-Module in order to display information about the SqlServer module. This command provides information like ModuleType, Version, NAme, and ExportedCommands. The ExportedCommands is very important as it lists all provided functions to connect and query the SQL database server.

PS> Get-Module -Name SqlServer
ModuleType Version      Name          ExportedCommands
----------      -------        ----              ----------------
Script            21.1.1123  SqlServer     {Add-SqlAvailabilityDatabase, Add-SqlAvailabilityGroupList...

Connect SQL Server

After importing the SQLServer module we can connect to the SQL server by using the Get-SqlInstance command providing the remote SQL server hostname or IP address and required credentials to authenticate the SQL server. The -Get-Credential command is used to request credentials interactive from the command line interface.

PS> $crendentials = Get-Credential
PS> $instance = Get-SqlInstance -ServerInstance -Credential $crendential

Run Query In SQL Server

The Invoke-Sqlcmd command is used to run SQL or query the database. We should provide the $instance to the Invoke-Sqlcmd command using the -ServerInstance parameter. The query is specified with the -Query parameter.

PS> $crendentials = Get-Credential
PS> $instance = Get-SqlInstance -ServerInstance -Credential $crendential
PS> Invoke-Sqlcmd -Query "SELECT * FROM Users" -ServerInstance $instance

Leave a Comment