Powershell and SQL Authentication

As shown in my last post, using Powershell to connect to SQL is pretty straightforward, once you load the proper assembly. However, what if you need to connect to a server using SQL authentication – the server is in another domain for example. What you need to do is tell Powershell to NOT use the default connection mode, and then provide the user name and password.

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') 
$sqlServer = new-object ('Microsoft.SqlServer.Management.Smo.Server') "(local)"  

#Default connection is via Windows integrated, 
#need to tell Powershell we do NOT want that
$sqlServer.ConnectionContext.LoginSecure=$false; 

#This sets the login name 
$sqlServer.ConnectionContext.set_Login("local_TH"); 

#This sets the password 
$sqlServer.ConnectionContext.set_Password("!@Qwerty12")  

$sqlServer.databases | Select Name

It can be a little cryptic if you do something innocuous, like enter a wrong password. When I did that, this was the return message:

The following exception was thrown when trying to enumerate the collection: "Failed to connect to server (local).".
At line:13 char:1
+  <<<< $sqlServer.databases | Select Name
    + CategoryInfo          : NotSpecified: (:) [], ExtendedTypeSystemException

    + FullyQualifiedErrorId : ExceptionInGetEnumerator

If you have a script of sever hundred or several thousand lines, errors like that are going to be problematic. Can we do some error handling? Yep. Wrap the DB connection portion in a try\catch block, like so:

try{
    $sqlServer.databases | Select Name
    }
catch {'Could not connect to DB - verify credentials'
    exit
    }

And a little more cleanup. If you have been following along and trying these items yourself, you have noticed the following is returned for every execution:

GAC    Version        Location                                                                                                                                                                                                                 
---    -------        --------                                                                                                                                                                                                                 
True   v2.0.50727     C:\windows\assembly\GAC_MSIL\Microsoft.SqlServer.Smo\10.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Smo.dll    

When we load the assembly, in line 1, PS feels obligated to let us know some details about the assembly. If we want to suppress that, we need pipe that information to the ether, also known as null. By adding “| out-null” following the loading of the assembly we do not get any of that stuff.

References:
http://www.mssqltips.com/sqlservertip/1947/connect-to-sql-server-via-windows-powershell-with-sql-server-authentication/
http://www.sqlmusings.com/2009/04/29/sql-server-powershell-basics-connecting-to-sql-server/
http://sqlblogcasts.com/blogs/martinbell/archive/2010/01/01/Powershell-and-SQL-Authentication.aspx