HOWTO Connect Powershell to SQL Server

In this article I'll be walking you through using Powershell to connect to SQL database. The objective is to be as straightforward as possible while providing context the whole way. Remember that lines marked with # are comments. Some of these will get long because we're trying to provide in-line context.

Assumptions:

• Powershell 2.0 or higher
• Connecting to a MS SQL server; for other connection strings see the excellent connectionstrings.com
• Appropriate access to the SQL DB in question with your currently logged on account
• Guts. 

Forward (or, WTH are we doing?!)

We'll be using two different objects for this operation: System.Data.SQLClient.SQLConnection and System.Data.SQLClient.SQLCommand. The first is a connection object that allows you to attach to the DB, and the second is a command object that allows you to specify and submit your command. Both these objects are part of the .NET framework, so they should be present on any machine that can run Powershell. Avoid using Microsoft.SqlServer.Management.Smo.Server unless you need to (and you'll know it) because it requires the SQL tools to be installed. 

In this first example, we'll be doing a query and returning the results. I won't be covering specifying UserID and PW in this because that relies on 1> SQL authentication and 2> Stores UserID and PW in the script. Bad idea. 

Setting the Stage

Let's first declare some of our variables to keep things clean down the line.

 #Declare our SQL server name; Make sure to use Server\Instance for instances, or Server:Port if you're on an instance and don't have the access to the SQL Browser service (UDP 1434). Also, try to use the FQDN because it's good practice and will insure NETBIOS issues won't trip you up.  
 $SQLServer="my.server.here"  
 #Provide the database name, i.e. "Northwind"  
 $SQLDBName="DATABASE"  

Now let's get to the meat; create objects, define properties, and open the connection. Here are reference links for the concepts in this section, followed by the comments and code:

System.Data.SQLClient.SQLConnection
System.Data.SQLClient.SQLCommand
ConnectionString Property

 #Create the SQL Connection Object  
 $SQLConn=New-Object System.Data.SQLClient.SQLConnection  
 #Create the SQL Command Ojbect (otherwise all we can do is admire our connection)  
 $SQLCmd=New-Object System.Data.SQLClient.SQLCommand  
 #Set our connection string property on the SQL Connection Object and tell it to use integrated auth, hopefully kerberos  
 $SQLConn.ConnectionString="Server=$SQLServer;Database=$SQLDBName;Integrated Security=SSPI"  
 #Open the connection  
 $SQLConn.Open()  

Now we'll define the query and execute. 

Devguru t-sql reference

 #Define our Command with a parameter (we will cover this below)  
 $SQLCmd.CommandText="SELECT [FOO],[BAR] FROM [dbo].[table] WHERE [COLUMN] = @smalls"  
 #Provide the open connection to the Command Object as a property  
 $SQLCmd.Connection=$SQLConn  
 #Set the WHERE clause in a variable to be referenced in the parameter (See section below)
 $WhereClause="smalls"
 #Prepare parameters  
 $SQLCmd.Parameters.Clear()
 $SQLcmd.Parameters.Add("@smalls",$WhereClause)  
 #Execute this thing  
 $SQLReturn=$SQLcmd.ExecuteReader()  
 #Init arrays to handle multiple returns
 $TheFooReturn=@()
 $TheBarReturn=@()
 #Parse it out  
 while ($SQLReturn.Read())  
 {  
      $TheFooReturn+=$SQLReturn["FOO"]  
      $TheBarReturn+=$SQLReturn["BAR"]  
 }  
 #Clean it up  
 $SQLReturn.Close()  
 $SQLConn.Close()  

Bonus Section: Avoiding SQL Injection

Note the lines $SQLCmd.CommandText="SELECT [FOO],[BAR] FROM [dbo].[table] WHERE [COLUMN] = @smalls", $WhereClause="smalls" & $SQLcmd.Parameters.Add("@smalls",$WhereClause). By using @ to set that value to a parameter we can later associate the parameter to the variable which will sanitize the input if it is provided by an external data source. (Prompt, file, etc.) This will protect from SQL injections; if we had used standard variable in the first place you could inject an inline SQL statement. By adding it as a parameter SQL will essentially treat it as a string. Thanks to my buddy Austin Peters for the education on this. :)

Closing Thoughts:

• You can have multiple SQL connections open at once. When you do so, name your object variables in a way that you can keep track of the DB that variable represents
• To do something other than a query (INSERT, etc.) set the SQLCmd.CommandText appropriately and change the statement SQLcmd.ExecuteReader to SQLcmd.ExecuteNonQuery. 

For more information, see this post by Don Jones.