PowerShell How-To

How To Quickly Test a SQL Connection with PowerShell

• By Adam Bertram
• 12/10/2018

Unfortunately, not every SQL database is always available when we expect it to be. When you're creating automation scripts to discover, add, update or remove records from a remote SQL database, you need to ensure your script can successfully connect to the database.

One way to figure out if your computer can reach a SQL database and that your credentials work to connect to it is by using a PowerShell script.

You'll first need to get the SqlServer PowerShell module. This module is available for free in the PowerShell Gallery by running Install-Module SQLServer. Once you have this module downloaded and installed, it's time to get started.

To test a SQL database connection, you'll need at least four pieces of information: the user name password, database name and endpoint (such as a DNS name or IP address). For this article, we're only going to be demonstrating SQL authentication. If your database is set up with Windows authentication, the code we're about to cover will not work.

We'll first need to create an ODBC string. This is the string we'll eventually pass to the SqlConnetion object, which contains all of the information for our connection. If you've got those four pieces of information described earlier, this step is just a matter of dropping in the various values in the appropriate places, as shown below:

$connectionString = 'Data Source={0};database={1};User ID={2};Password={3}' -f $ServerName,$DatabaseName,$userName,$password

Next, we need to create a SQLConnection object. We'll do this by passing the connection string as an argument to it. Once the object is created, we then can call the Open() method to attempt the connection. When run, this may pause for a little bit, especially if it's not able to connect successfully.

$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $connectionString
$sqlConnection.Open()

If the connection is unsuccessful, you will receive an error similar to what's shown below. This error means it couldn't even resolve the hostname. You may see many different reasons why the connection couldn't be completed.

Once the test is complete, you'll then need to close the connection (if it was successful) by calling the Close() method on the SQLConnection object.

$sqlConnection.Close()

That's it! However, when running this in the real world, there's a much easier way to do it: Creating a PowerShell function. By creating a PowerShell function to accomplish this task, we're able to combine these steps into a single action to make this process much more manageable.

There are many ways to create this function, but one example is below. You can see that we're still accepting the same input information (server name, database, user name and password) but accepting the user name and password more securely and using a PSCredential.

We're also using a try/catch block here to prevent seeing the error shown in the figure above. We know ahead of time that this error is possible, but we just want to know if the connection was successful. So, I'm defining $ErrorActionPreference = 'Stop' to ensure an exception is thrown. If so, it will go into the catch block where it will then return False, as we want.

Then, to ensure the database connection is closed every time, we have the database close step in the final block to ensure it runs whether or not the database connection was successful.

function Test-SqlConnection {
    param(
        [Parameter(Mandatory)]
        [string]$ServerName,

        [Parameter(Mandatory)]
        [string]$DatabaseName,

        [Parameter(Mandatory)]
        [pscredential]$Credential
    )

    $ErrorActionPreference = 'Stop'

    try {
        $userName = $Credential.UserName
        $password = $Credential.GetNetworkCredential().Password
        $connectionString = 'Data Source={0};database={1};User ID={2};Password={3}' -f $ServerName,$DatabaseName,$userName,$password
        $sqlConnection = New-Object System.Data.SqlClient.SqlConnection $ConnectionString
        $sqlConnection.Open()
        ## This will run if the Open() method does not throw an exception
        $true
    } catch {
        $false
    } finally {
        ## Close the connection when we're done
        $sqlConnection.Close()
    }
}

Finally, this function can be run using the example below. This will prompt the user for a user name and password, and then pass that credential to the server and attempt to connect to the database.

Test-SqlConnection -ServerName 'serverhostname' -DatabaseName 'DbName' -Credential (Get-Credential)

Next time your script is failing when you least expect it because of a failed SQL connection, use this PowerShell code to check ahead of time. Adding error handling like this will significantly improve the resiliency of your PowerShell scripts.