You have a problem accessing the spider database e.g. from the spider application server and want to easily troubleshoot it.
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
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"
Oct 22, 2018