Unable to query SQL Server 2016 using dbquery.nsm

[stoffmann]

Hi,

I try to query some data from an MS SQL Server 2016, but can't get it working.

My Agent config (xxx means confidential):

MasterServers = xxx
ConfigIncludeDir = D:\Program Files\NetXMSAgent\etc\nxagentd.conf.d
LogFile = D:\Program Files\NetXMS\log\agent.log
DebugLevel = 4
FileStore = D:\Program Files\NetXMSAgent\var
SubAgent = filemgr.nsm
SubAgent = ping.nsm
SubAgent = logwatch.nsm
SubAgent = winperf.nsm
SubAgent = wmi.nsm
SubAgent = dbquery.nsm

ExecTimeout=60000
ExternalParameterShellExec=Custom.EBGroupUsers:net localgroup "Engineering Base" | find /c "\"

*DBQUERY
Database = id=db1;driver=mssql.ddr;server=xxx(IP ADDRESS);dbname=master;login=xxx;password=xxx
Query = SQL.RecoverModel:db1:600:SELECT Count(name) FROM sys.databases WHERE recovery_model<>3 and name not in ('msdb','model','master')

I'm sure username and password works.

In the agent log I get:

DBQUERY: cannot connect to database db1 ([Microsoft][SQL Server Native Client 11.0]Named Pipes Provider: Could not open a connection to SQL Server [2]. )
DBQUERY: H_DirectQueryConfigurable: no connection handle for database db1

Named Pipes and TCP/IP is enabled in configuration manager.

I use NetXMS Version 3.2.380

Best regards

Stefan

[victor]

Hi,

configuration looks good. Is hybrid authentication enabled? Also, could it be that password contains some characters that could be misinterpreted (like: ; # ' ")?

Best regards,
Victor

[stoffmann]

Hi,

I think You mean "Mixed Mode Authentication" in SQL Server? This is enabled. Password only uses upper and lowercase chars with some numbers.
Checking the SQL server logs shows that there is no try to login from the login used by the agent.
Very strange.

Do You have more ideas?

Regards

Stefan

[victor]

Hi,

I did some testing on my system, and I can get this error if TCP/IP is disabled in native client configuration. Check "Client Protocols" section under "SQL Native Client 11.0 Configuration" in "SQL Server Configuration Manager".

Best regards,
Victor

[stoffmann]

Hi Victor,

I will check this, when I'm back from vacation in two weeks.

Regards

Stefan

[stoffmann]

Hi, I'm back from vacation now and checked the configuration again.
TCP/IP is enabled for Native Client 32bit and 64bit. (see screenshots)
Is there anything I could test?

Best regards

Stefan

---

[stoffmann]

I still have this problem. Is there a way to get more information to find out where the problem is?

[stoffmann]

I finally found the problem. Man, I'm so stupid. I forgot to add the instance name after the servername in the agent config ::)

[Filipp Sudanov]

Thanks for the follow up