How To Test SQL Database Connection In PowerShell?

by

PowerShell provides the SQLServer in order to check MS-SQL database connectivity. On daily operations, we may need to set up or create an SQL database but there may be some problems with the connectivity. We can use the Test-SQLDatabase command in order to check the database connectivity for network, port, credentials, etc.

Install Test-SQLDatabase

The Test-SQLDatabase command is not provided as built-in but can be installed easily by using the following Install-Module command. The Test-SQLDatabase command is provided via the SQLServer package.

PS> Install-Module SQLServer

From the screenshot, we can see that in order to install the SQLServer module two confirmation is required. We can simply put the Y and press enters in order to accept these confirmations.

Test-SQLDatabase Function

We can create a function named Test-SQLDatabase in order to use the SQLServer library in a more elegant way. The Test-SQLDatabase function required 3 parameters where all of which are mandatory.

  • $Server is the remote SQL database server hostname or IP address. If a hostname is provided it should be resolvable from the DNS server.
  • $Database is the database name in order to connect and make the default database.
  • $Credential is used to get the username and password via the PowerShell command line interface interactively.
function Test-SqlConnection {
    param(
        [Parameter(Mandatory)]
        [string]$Server,

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

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

    $ErrorActionPreference = 'Stop'

    try {
        $user = $Credential.User
        $password = $Credential.GetNetworkCredential().Password
        $connectionString = 'Data Source={0};database={1};User ID={2};Password={3}' -f $Server,$Database,$user,$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()
    }
}

We can call the Test-SQLConnection function below by providing the -Server , -Database parameters directly and calling the Get-Credential command to provide credentials in a secure way.

PS> Test-SQLConnection -Server 192.168.1.10 -Database "users" -Credential (Get-Credentials)

Provide SQL Connection Parameters As Variables

We can use PowerShell variables in order to provide the Server, Database parameters. In the following example, we set the $ServerName and $DatabaseName parameters and provide to the Test-SQLConnection as parameters.

PS> $ServerName = "192.168.1.10"
PS> $DatabaName="users"
PS> Test-SQLConnection -Server $SeverName -Database DatabaseName -Credential (Get-Credentials)

Provide SQL Connection Parameters As Connection String

The SQLServer module provides System.Data.SqlClient.SqlConnection object in order to connect to the SQL database. We can use this object directly and provide a connection string in order to test the SQL database connection. The Connection String is used to provide connection parameters like server, database, username, and password.

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

Now we can use the SqlConnection object with the created connection string to connect the SQL server. The connection object can be stored in an object and the Open() method is used to start the connection.

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

Check SQL Database Port

We may need to check only SQL database network connectivity by trying to connect the database server TCP port. The SQL database server’s default port number is 1433. We can use the Net.Sockets.TcpClient in order to connect SQL database port number 1433.

  • $Server is the remote server IP address or DNS name.
  • $Port is the remote database server port number.
$Server= '192.168.1.10'
$Port   = 1433

$TcpConnection = New-Object Net.Sockets.TcpClient
if ([void]$tcp.Connect($server, $port)) {
  'Database Port Is Accessable'
} else {
  'Can Not Access Database Port'
}
$tcp.Dispose()

Leave a Comment