I've got an application owner who thinks he is having issues with his app server connecting to his database server. I've offered to create a monitor that would run from his APP server and make a DB connection to his SQL server. I'm attempting to do this with a PS script type component designated to run on the "Remote Host". Using this script.
try {
$connectionString = "Server=MyServer;Database=EFTDB;Integrated Security=False;User ID = ${USER};Password = ${PASSWORD}";
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $connectionString;
$sqlConnection.Open();
$msg = "True";
Write-Host "Message: $msg";
Write-Host "Statistic: 1";
} catch {
$ErrorMessage = $_.Exception.Message
$FailedItem = $_.Exception.ItemName
$msg = "False $ErrorMessage";
Write-host "Message: $msg";
Write-host "Statistic: 0";
## EXIT 1
} finally {
## Close the connection when we're done
$sqlConnection.Close()
## EXIT 0
}
No matter what I do, I keep getting the error Message: False Exception calling "Open" with "0" argument(s): "Login failed for user 'AD\user'."
Statistic: 0
If I change the execution mode to "Local Host" and the script to Integrated Security, it works
try {
$connectionString = "Server=MyServer;Database=EFTDB;Integrated Security=True";
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $connectionString;
$sqlConnection.Open();
$msg = "True";
Write-Host "Message: $msg";
Write-Host "Statistic: 1";
} catch {
$ErrorMessage = $_.Exception.Message
$FailedItem = $_.Exception.ItemName
$msg = "False $ErrorMessage";
Write-host "Message: $msg";
Write-host "Statistic: 0";
} finally {
## Close the connection when we're done
$sqlConnection.Close()
}
Again the idea is for the script to run on the app server and create a connection to the Db server from there. I believe "Local Host" mode would cause it to run from the poller?
So what am I doing wrong? I 100% know the account has access to that Db, I am logged into it as that account via MSSMS. The AppInsight for MSSQL works fine with that account. Is there another approach I should be taking?
