MS SQL V2: Diagnosing "Can not log in using the specified credentials!"

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 :)

Hello,

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

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

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 [email protected]. Please refer to this kb post.

Thank you

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