How to Test Connection to SQL Server Using PowerShell

Tagged: PowerShell

Problem#

As engineer, it is common to have a task where we want to test connection to Sql Server database instance so that we can query. To achieve this task, we can use PowerShell.

In this blog post, we will walk you through how to test connection to Sql Server using PowerShell.

Using SqlServer Module#

To use this module, first we need to install the module.

Install-Module -Name SqlServer

Then, we can use Invoke-Sqlcmd cmdlet to connecto to database by specifying connection string and the query. In this case, we use Windows Authentication. TrustServerCertificate property in connection string is optional.

Invoke-Sqlcmd -ConnectionString "Server=localhost;Database=AgrM7Loc541;Trusted_Connection=True;TrustServerCertificate=True" -Query "SELECT @@VERSION"

Using DbaTools Module#

To use this module, first we need to install it from PowerShell Gallery.

Install-Module dbatools

Then, we can use Test-DbaConnection cmdlet to test the connection by specifying sql server instance. This cmdlet will test connection to single sql server instance.

By default, this cmdlet will use Windows Authentication credential.

Test-DbaConnection -SqlInstance localhost

Please remember you can replace localhost with computername.

You can also connect with other alternative credential like Windows credentials by using SqlCredential parameter. In this case, u4agr\hsuryoat is the user.

Test-DbaConnection -SqlInstance localhost -SqlCredential (Get-Credential u4agr\hsuryoat)

Using SqlConnection Class from .NET Framework#

We can also use SqlConnection class from .NET Framework. But, remember we have to enclosed the script with try-finally block to ensure the connection is released regardless error happens or not to avoid memory leak.

This example uses Windows Authentication connection string.

try {
    # Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;
    $server = 'localhost'
    $database = 'AgrM7Loc542'

    $connectionString = 'Server={0};Database={1};Trusted_Connection=True' -f $server, $database
    $sqlConnection = New-Object System.Data.SqlClient.SqlConnection $ConnectionString
    $sqlConnection.Open()

    ## This message will be displayed if 'Open' method does not throw an exception
    Write-Host 'Open database connection'
}
catch {
    Write-Host 'An error occurs'
}
finally {
    ## Ensure closing the connection to release the resource / free memory
    $sqlConnection.Close()

    Write-Host 'Close database connection'
}

Conclusion#

To test connection to sql server specifically the instance, we can use Test-DbaConnection cmdlet from DbaTools module. If we want to connect to sql server instance and query, we can use Invoke-Sqlcmd cmdlet from SqlServer module.

We can also use SqlConnection class from .NET Framework. To apply this method, we have to apply dispose pattern where the code is enclosed in try-finally block so that if error happens the connection must be released to avoid memory leak.