SQL server powershell war chest – advanced connection provider

In a previous post I covered a basic connection provider, which is nice, but has its limitations. biggest one being that you always have to supply credentials. If you’re like most DBAs, chances are that you usually work on several servers in different environments and entering the login info all the time can get tedious. Of course integrated authentication helps a bit, but still, for those that still use SQL logins it could be a bit of a nuisance. All of this was a problem until my friend Jorge Satorre showed me a beautiful solution that I will share with you here.
First of all, let’s start from the basics – saving your password for future reuse. This blog post may come in handly. Jorge likes Powershell Cookbook (I believe it’s this one). In any case, first step is to save a password to a file. It’s a bit of a tango twist, but not too confusing. First we create an object with the following properties – AuthenticationType, Username and SecurePassword. AuthenticationType can be integrated or regular:

 
$object = "" | Select-Object AuthenticationType,Username,SecurePassword 

the twist happens when saving a password. We take it from a regular string to a secure string:

convertto-securestring "P@ssW0rD!" -asplaintext -force 

then we encrypt the secure string for storage and export to an xml file:

$object = "" | Select-Object AuthenticationType,Username,SecurePassword
$object.AuthenticationType = "regular"
$object.Username = "superuser"
$object.Password = convertto-securestring "P@ssW0rD!" -asplaintext -force | ConvertFrom-SecureString
$object |  Export-Clixml ./ServerA.xml

we can create a file for each server and store them in a save location. As you will see in the connection script I store my server authentication files in c:\powershell\ss.
We’re almost done. Now our get_connection script just needs two params – a database name and a server.

param(
	  [string] $database = "MYdb",
	  [string] $dataSource="ServerA"
     )

all we have to do is to read the credential file and construct a connection string accordingly. If the AuthenticationType is “regular” we will also read the username/password pair.

       $Path = "c:\powershell\ss\$dataSource.XML"
 
        # Import credential file
        $import = Import-Clixml $Path 
 
if ($import.AuthenticationType -eq "Integrated")

{
$authentication = "Integrated Security=SSPI;"
}
else
{
        # Test for valid import
        if ( !$import.UserName -or !$import.Password ) {
                Throw "Input is not a valid ExportedPSCredential object, exiting."
        }
		
		$username = $import.Username
		
		        # Decrypt the password and store as a SecureString object for safekeeping
        $SecurePass = $import.Password | ConvertTo-SecureString
		
        # Build the new credential object
        $Credential = New-Object System.Management.Automation.PSCredential $Username, $SecurePass

	    $plainCred = $credential.GetNetworkCredential()
    	$authentication = ("uid={0};pwd={1};" -f $plainCred.Username,$plainCred.Password)
}

$connectionString = "Persist Security Info=True;Initial Catalog=$database;Data Source=$dataSource;$authentication"

that’s it. Now all of your servers are at your fingertips. Download the get_connection script.