SQL server powershell war chest – connection provider

It is clear by now that MS SQL server and powershell are going to be good friends for a long time to come. So a prudent DBA should be building a war chest of script modules that can be reused in different cases. I will go through a series of short posts that will make every DBAs life easier and more productive.

let’s start with basics – connection provider. If you’re going to interface with a SQL server, you need to connect to it. Here is a very simple script that will do the job:


      [string] $datasource="myserveraddress",
	  [string] $database = "MyDb",
	  [string] $authType = "Integrated",
	  [string] $username ="",
	  [string] $password=""

## Prepare the authentication information. 
if ($authType -eq "Integrated")
$authentication = "Integrated Security=SSPI;"
{ $authentication = ("uid={0};pwd={1};" -f $username,$password)	}

# Create the actual connection string 
$connectionString = "Persist Security Info=True;Initial Catalog=$database;Data Source=$dataSource;$authentication"

## Connect to the data source and open it
$connection = new-object System.Data.SqlClient.SqlConnection $connectionString

As you can see, there is practically nothing to it. you can easily switch between SQL [regular] and Windows [integrated] modes of authentication. We’ll be using this script a lot in the future.

Download Get_connection.ps1

Share this post

Leave a Reply

Your email address will not be published. Required fields are marked *

Are you #CuriousAboutData? This is a members’ only discussion forum for any CTO, Developer, DBA, Data Engineer/Scientist — or anyone who just wants to know more about using and managing data. Members enjoy open debate, ask/ answer questions, offer opinions, expertise, and support. If your curiosity has no limits, this group is for you!