One of the most common questions people have when it comes to PowerShell, is “How do I connect/query SQLServer?”. I’ll cover three common ways: .Net, Invoke-SqlCmd and Invoke-DbaQuery. Also known as: The Hard Way, The Easy Way and My Preferred Way.

Connect With .Net (The Hard Way)

Create a connection object (more info on connection string options here.

Step by step.

Here are the steps broken down. A copy and paste version is after 

    
        
#Create a connection string
$Conn  =  New-Object  System.Data.SqlClient.SqlConnection
#SQL authentication
$Conn.ConnectionString  =  'Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;'
#Trusted or Domain authentication.
$Conn.ConnectionString  =  'Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;'

Next we open the connection with the Open() method. 

    
        
$Conn.Open()

Then we create the SQL command object. 

    
        
$Command  =  New-Object  System.Data.SqlClient.SqlCommand

Pass in the connection object so it know where to connect to. 

    
        
$Command.Connection  =  $Conn

Write your query. 

    
        
$query  =  “SELECT ColumnName FROM Table WHERE OtherColumn = 'xyz'”

Set you query as the commandtext property. 

    
        
$Command.CommandText  =  $query

Create adapter and give it the command. 

    
        
$Adapter  =  New-Object  System.Data.SqlClient.SqlDataAdapter  $Command

Create a data object. 

    
        
$data  =  New-Object  System.Data.DataSet

Fill the data object from the adapter. 

    
        
$Adapter.Fill($data)

Retrieve your data. 

    
        
$data.Tables

Copy/Paste

Here’s the copy/paste version. Just change the connection string parameters for your use. 

    
        
#Create a connection string
$Conn  =  New-Object  System.Data.SqlClient.SqlConnection
#use one or the other
#SQL authentication
$Conn.ConnectionString  =  'Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;'
#Trusted or Domain authentication.
$Conn.ConnectionString  =  'Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;'
$Conn.Open()
$Command  =  New-Object  System.Data.SqlClient.SqlCommand
$Command.Connection  =  $Conn
$query  =  “SELECT ColumnName FROM Table WHERE OtherColumn = 'xyz'”
$Command.CommandText  =  $query
$Adapter  =  New-Object  System.Data.SqlClient.SqlDataAdapter  $Command
$data  =  New-Object  System.Data.DataSet
$Adapter.Fill($data)  
$data.Tables
As you can see, using .Net to query a database is possible but not pleasant. I would only use this on a locked down system where I could not install modules or where you’re writing for an unknown environment where .Net is the only thing guaranteed to be on the system.

Invoke-SqlCmd (The Easy Way)

Calling Invoke-SqlCmd by itself will auto load the SQLServer PowerShell module. If you don’t have the module installed run this from a administrator powershell window:

Install-Module -Name SqlServer to install for all users or

Install-Module -Name SqlServer -Scope CurrentUser to install for just your account.

Execute a query with trusted connection. If you omit the server, it will default to localhost. 

    
        
$results = Invoke-SqlCmd -ServerInstance MyServer -Database MyDb -Query "query"

Query with SQL authentication (not recommended because your password is in plaintext on the command line). 

    
        
$results = Invoke-SqlCmd -ServerInstance MyServer -Database MyDb -Username Me -Password Abcd1234 -Query "query"

SQL authentication with a PSCredential object. 

    
        
$cred = Get-Credential
$results = Invoke-SqlCmd -ServerInstance MyServer -Database MyDb -Credential $cred -Query "query"

Get your results. 

    
        
$results

This method has the advantage that it is more likely to already be installed on a system.

Invoke-DbaQuery (My Preferred Way)

This cmdlet is part of the dbatools module. The first reason for my preference is I do a lot of my work with the dbatools cmdlets so the module is usually already loaded in my session. Next is that it has consistently named parameters to the rest of the module. The final and main reason for using this over Invoke-SqlCmd is that you can pass multiple servers to the SqlInstance parameter.

From an Administrator session run: Install-Module -Name dbatools to install for all users or Install-Module -Name dbatools -Scope CurrentUser from a regular session to install for just your account.

Trusted with account the session is using. 

    
        
$results = Invoke-DbaQuery -SqlInstance Server -Database MyDb -Query "query"

With a PSCredential object. 

    
        
$cred = Get-Credential
$results = Invoke-DbaQuery -SqlInstance Server -Database MyDb -SqlCredential $cred -Query "query"

With multiple servers. 

    
        
$servers = ('SQL01','SQL02','SQL03')
$results = Invoke-DbaQuery -SqlInstance $servers -Database MyDb -SqlCredential $cred -Query "query"

Get your results. 

    
        
$results

Using Invoke-DbaQuery is fairly close to using Invoke-SqlCmd but you get the added benefit of it being part of over 600 (ver 1.0.149) 693 (ver2.1.28) other cmdlets that are part of the dbatools suite that has a very active community supporting it. What you use is largely preference and what your orginization allows.

Thanks for reading!