SQL Server PowerShell : Basics – Connecting to SQL Server

PowerShell is a great tool that allows DBAs and Developers alike to script, manage and automate SQL Server tasks. Most of the objects and tasks will require SMO (SQL Server Management Objects).

Basic Steps

1. Set Execution Policy

Depending on what you need to do, you may need to set the Execution Policy in your Powershell console. In my case, I will be using it to access SQL Server for administrative purposes, so I will be setting mine to have Unrestricted access to my server. Make sure you review the security guidelines in your organization before you set your environment to Unrestricted

PS> Set-ExecutionPolicy Unrestricted

2. Load assemblies

First, you will need to load SQL Server related assemblies. For SMO, you will usually want to load the following assemblies:

  • Microsoft.SqlServer.Smo
  • Microsoft.SqlServer.SqlEnum
  • Microsoft.SqlServer.SmoEnum
  • Microsoft.SqlServer.ConnectionInfo

When you load the assemblies, you have to use the following format:

[Reflection.Assembly]::Load("Microsoft.SqlServer.Smo, `
      Version=, Culture=neutral, ` 

A couple of things to note here:

  • the backtick is the line continuation character, ie if your line of code spans multiple lines, you have to use a backtick
  • you can get the version of the assembly either by :
    • going to the GAC (C:Windowsassembly), right click on assembly and click on properties
    • use reflector

SQL Server SMO assembly

3. Create Server Object

You will need to create a server object

# ===================================
# if local instance
# ===================================
$server = New-Object -typeName Microsoft.SqlServer.Management.Smo.Server

# or

$serverName = "(local)"
$server = New-Object -typeName Microsoft.SqlServer.Management.Smo.Server ` 
          -argumentList "$serverName"

# ===================================
# if named instance, use format SERVERNAMEINSTANCENAME
# ===================================
$serverName = "JUBILEESQL01"
$server = New-Object -typeName Microsoft.SqlServer.Management.Smo.Server ` 
          -argumentList "$serverName"

4. Connect

Once you have created a SQL Server Object, you can connect to SQL Server using either windows (trusted) or SQL authentication.
This also shows you how you can use the Get-Credential cmdlet

Get-Credential cmdlet

# ===================================
# Alternative 1: Trusted Connection
# ===================================
$server.ConnectionContext.LoginSecure = $true

# ===================================
# Alternative 12: SQL authentication, using Get-Credential cmdlet 
# which prompts you for a username/password when you run the script
# ===================================
$server.ConnectionContext.LoginSecure = $false
$credential = Get-Credential

# remove leading backslash in username, assuming no domain name was supplied
$userName = $credential.UserName -replace("\","")

# ===================================
# Alternative 3: SQL authentication, supplying username 
# and password in the script, something you should be 
# *very* wary of doing
# ===================================

# also note here that we need to convert the password to 
# a SecureStriing, before we can pass it to set_SecurePassword

$securePassword = ConvertTo-SecureString "mypassword" -AsPlainText –Force

Here’s a complete sample.

All this script does is connect to a SQL Server named instance using SQL authentication, and just display the connection string.

[bash collapse=”false” firstline=”1″ gutter=”true” smarttabs=”true” tabsize=”4″ toolbar=”true”]

# load assemblies
[Reflection.Assembly]::Load(“Microsoft.SqlServer.Smo, `
Version=, Culture=neutral, `
[Reflection.Assembly]::Load(“Microsoft.SqlServer.SqlEnum, `
Version=, Culture=neutral, `
[Reflection.Assembly]::Load(“Microsoft.SqlServer.SmoEnum, `
Version=, Culture=neutral, `
[Reflection.Assembly]::Load(“Microsoft.SqlServer.ConnectionInfo, `
Version=, Culture=neutral, `PublicKeyToken=89845dcd8080cc91”)

# connect to SQL Server named instance
# server name is JUBILEE
# instance name is SQL01
# use serverinstancename
$serverName = “JUBILEESQL01”
$server = New-Object -typeName Microsoft.SqlServer.Management.Smo.Server -argumentList “$serverName”

# login using SQL authentication, which means we supply the username
# and password
$credential = Get-Credential
$userName = $credential.UserName -replace(“\”,””)

# clear the screen

# list connection string
Write-Host “——————————————————–”
Write-Host “Connection String : ”
Write-Host $server.ConnectionContext.ConnectionString
Write-Host “——————————————————–”


By the way, I am using Idera’s PowerShell Plus editor/console. I’ve tried writing all my scripts using a regular text editor before, and I’m pretty confident when I say nothing beats having an awesome tool when you do your job. A few very remarkable features I really like:

  • Intellisense – can anyone argue with this?
  • Editor/Console combo – I write my script, press F5, and it runs!
  • Built in Powershell Help/Reference
  • Debug feature

Idera Powershell Plus Editor

Idera PowerShell Plus Console

Idera also provides a PowerShell video series by Don Jones, and free Powershell scripts.

3 thoughts on “SQL Server PowerShell : Basics – Connecting to SQL Server

  1. Don Schaeffer December 8, 2009 / 10:45 am

    Thanks! Finally an example connecting to SMO using SQL authentication.


  2. nejm April 17, 2011 / 4:04 pm

    Thank you much helpfull and very interesting


  3. Dean Allard April 26, 2011 / 5:44 am

    I’m trying to set some settings in the Sql Server Surface Area dialog. I want to enable:
    Ole Automation
    Database Mail

    How can I change these settings using Smo?



Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s