31 Posts of using Sql Server with Powershell — Post 2: ServerConnection

The first post consisted of getting the powershell cmdlets installed/imported into your powershell environment (in my case, powershell_ise).  For day 2, we’re going to look at how we can connect to a Sql Server instance.

ServerConnection

The serverconnection is just what it sounds like; a connection to your sql server instance.  In the code below, all we’re doing is importing the SqlPS module, then creating a ServerConnection object and passing in the name of the server instance that we want to connect to, then opening said connection.

Push-Location
Import-Module SqlPS -DisableNameChecking

try{

    $srvConn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection 'bacon'
    $srvConn.Connect();

}
catch{
    $_ | fl -Force
}
finally{
    $srvConn.Disconnect();
}

Pop-Location

I’ll explain a couple of the details the details here.  The Push-Location at the beginning of the code is used to store your current location, and the Pop-Location at the end just returns you to that location when we’re done running this code.

You don’t NEED to do this, but the Import-Module SqlPS will switch you to the SQLSERVER drive, which can then interfere with other cmdlets you may be running.  It’s just a good habit to get into when using the SqlPS provider, sadly not one I’ve consistently formed.

Secondly, in the catch statement I’m taking the current exception object, doing a format-list on it and doing a –Force.  The reason why I’m doing this is so you can see the details of the exception further down than the first error.  Best explained with an example.  Take the following code:

Push-Location
Import-Module SqlPS -DisableNameChecking

try{

    $srvConn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection 'bacon'
    $srvConn.LoginSecure = $false;
    $srvConn.Login = 'snewman'
    $srvConn.Password = 'Test'
    $srvConn.Connect();

}
catch{
    $_ #| fl -Force
}
finally{
    $srvConn.Disconnect();
}

Pop-Location

Notice I have the ‘ | fl –Force’ commented out in the catch statement.  When I run this code, I get the following error:

image

So, what is this error really telling me?  All I know is that it failed to connect, not what the problem really was.  Is my server name incorrect?  Was my password incorrect?

Now, run the same code again, this time without the ‘| fl –Force’ commented out:

Push-Location
Import-Module SqlPS -DisableNameChecking

try{

    $srvConn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection 'bacon'
    $srvConn.LoginSecure = $false;
    $srvConn.Login = 'snewman'
    $srvConn.Password = 'Test'
    $srvConn.Connect();

}
catch{
    $_ | fl -Force
}
finally{
    $srvConn.Disconnect();
}

Pop-Location

After running this code, I get much, much more information about the error:

image

I can see that the issue was that the login was indeed incorrect.

Okay, now that that is all explained, let’s get back to the ServerConnection object.  If you want to use AD authentication with the ServerConnection object, there is a property named LoginSecure that will set this.  It is true by default, so to use AD auth, just open the connection on the ServerConnection object and it will use it by default.

Push-Location
Import-Module SqlPS -DisableNameChecking

try{

    $srvConn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection 'bacon'
    $srvConn.Connect();
}
catch{
    $_ | fl -Force
}
finally{
    $srvConn.Disconnect();
}

Pop-Location

If you want to use Sql Server authentication, you have to set a few more properties.

Push-Location
Import-Module SqlPS -DisableNameChecking

try{

    $srvConn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection 'bacon'
    $srvConn.LoginSecure = $false;
    $srvConn.Login = 'LoginName';
    $srvConn.Password = 'P@55w0rd'
    $srvConn.Connect();
}
catch{
    $_ | fl -Force
}
finally{
    $srvConn.Disconnect();
}

Pop-Location

First, you have to set the LoginSecure property to $false.  If you try to set the Login when the LoginSecure is not $false, you will receive an error:

image

There is also another property on the ServerConnection object named SecurePassword.

Push-Location
Import-Module SqlPS -DisableNameChecking

try{

    $srvConn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection 'bacon'
    $srvConn.LoginSecure = $false;
    $srvConn.Login = 'LoginName';
    $srvConn.SecurePassword = (ConvertTo-SecureString -String 'P@$$w0rd' -AsPlainText -Force)
    $srvConn.Connect();
}
catch{
    $_ | fl -Force
}
finally{
    $srvConn.Disconnect();
}

Pop-Location

This property exists so you can encrypt your passwords via a secure string (most likely held in a file) and use that to more securely handle passwords.  This same type of scenario also exists for the ConnectionString property & SecureConnectionString property.

The most common methods for this object are the Execute* properties for executing various types of Sql statements.

image

Here is a quick example of using the ExecuteNonQuery to create a table:

Push-Location
Import-Module SqlPS -DisableNameChecking

try{

    $srvConn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection 'bacon'
    $srvConn.Connect();

    $srvConn.ExecuteNonQuery("CREATE TABLE dbo.Test(ID INT, Value VARCHAR(100));");
}
catch{
$_ | fl -Force
}
finally{
$srvConn.Disconnect();
}
Pop-Location

Now, let’s have some fun with inserting data into the table:

Push-Location
Import-Module SqlPS -DisableNameChecking

try{

    $srvConn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection 'Bacon'
    $srvConn.Connect();

    #$srvConn.ExecuteNonQuery("CREATE TABLE dbo.Test(ID INT, Value VARCHAR(100));");

    1..1000 | %{
        $srvConn.ExecuteNonQuery("INSERT INTO dbo.Test(ID, Value) SELECT $($_), 'Test $($_)'") | Out-Null
    }
}
catch{
    $_ | fl -Force
}
finally{
    $srvConn.Disconnect();
}

Pop-Location

Essentially, what this is doing is for 1 to 1000 (1..1000) execute the ExecuteNonQuery method and insert the current number ($_) into the ID column and insert ‘Test_[current number]’ into the Value column.  Now, this 1..1000 could be values read from a file, read from another database, a custom psobject, you get the idea.

For the next post, we’ll look at getting data out of a database.

Advertisements

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: