How to troubleshoot SQL Connection problems with Spider using PowerShell

[#1]

Summary

You have a problem accessing the spider database e.g. from the spider application server and want to easily troubleshoot it.

Synopsis

You have a problem accessing the spider database e.g. from the spider application server and want to easily troubleshoot it.

Discussion

Using PowerShell for this purpose makes it easy to do simple connection tests, since it does not require any adittional tools such as SQL client or SQL Management Studio etc.

The following PowerShell script helps you to check the connection to the Spider database - On success it will list you the registered

$SQLServer = "<sqlserverinstancename>"
$CoreName = "SpiderCore"
$SqlQuery = "SELECT Name FROM $CoreName..Application"

$SQLUser = "<username>"
$SQLPass = "<passwort>"
 
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; User Id=$SQLUser; Password=$SQLPass;"

$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
 
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
 
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
 
$SqlConnection.Close()

$DataSet.Tables

Warning

This script can also used to connect using integrated security (the connection is made with the user which starts the script) but you cannot connect with an AD user.

For this change the Connection String to the following:

$SqlConnection.ConnectionString = "Server = $SQLServer; Trusted_Connection = True"

Additional Information

Products

LCM6