The server is MS SQL 2014 with SSL enabled/enforced

I have verified the credentials are correct. Using Windows authentication. I tried all three encryption settings (server default, force with no chain of trust, force with chain of trust) I can log in with other tools (linqpad, ssms...)

Is there a log somewhere with an error code to find out why it failed to log in?

Article Comments

I am not using a MSSQL-V2-sensor but it might help..

Did you specify the database credentials in the device settings or did you say it have to use Windows authentication? I tried that right now and I can't set both at the same time. Either credentials or Windows authentication. If it is set to Windows authentication I think the sensor is using the account that also runs the PRTG-service on your monitoring-machine.

Could you just check that?

I hope I could help you :)


Apr, 2016 - Permalink

Hello,

Can you confirm that the password do not contain the Special caraters "<" or ">"?


Apr, 2016 - Permalink

I don't have the special characters in the password. Tried different accounts, too.


Apr, 2016 - Permalink

Hello,

In the sensor settings, please enable the option "Write sensor result to disk" let the sensor run on schedule or force it manually to "check now" and then send us the file to support@paessler.com. Please refer to this kb post.

Thank you


Apr, 2016 - Permalink

Danke Jochen fuer den Support :)

The diagnostic data didn't reveal anything new, so I ended up writing my own SQL sensor. This uses the MS SQL client libraries (it requres Sql Managment Objects (SMO) installed on the probe, a free download from Microsoft)

Script goes into CustomSensors\EXE To make use of it, write a query that returns a single integer indicating error count. Mine often look like

Query.sql SELECT COUNT(1) FROM Log WHERE Level = 'Error'

Put this in a file on its own and put that file into CustomSensors\EXE as well.

Then, create a new EXE sensor Sql.ps1 as the custom exe <ServerName> <Scriptname> as arguments

Sql.ps1

[CmdletBinding()]
Param(
   [Parameter(Mandatory=$True, Position=1)]
   [string] $Server,
    
   [Parameter(Mandatory=$True, Position=2)]
   [string] $QueryFile
)

Set-StrictMode -Version Latest
$ErrorActionPreference = "Stop"

# If we don't have a fully qualified path, look in the script folder for the query file
if (!(Test-Path $QueryFile))
{
    $ScriptPath = Split-Path -Parent $PSCommandPath
    $QueryFile = Join-Path $ScriptPath $QueryFile
}

$Query = Get-Content $QueryFile
$ConnectionString = "Data Source=$Server; Integrated Security=SSPI"

$Conn = New-Object System.Data.SqlClient.SqlConnection($ConnectionString)
$Conn.Open()
$Command = $Conn.CreateCommand()
$Command.CommandText = $Query
$Result = $Command.ExecuteScalar()

if ($Result -gt 0)
{
  Write-Host "$($Result):Error"
  exit 1
}

Write-Host "$($Result):Ok"
exit 0

May, 2016 - Permalink