Friday, January 18, 2013

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:

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:

#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.