Metricbeat MSSQL module connecting to only localhost

phani_akkina · January 20, 2021, 3:50pm

I am trying to monitor MSSQL using metricbeat but I found some strange errors.

I am trying to monitor SQL Cluster with IP and SQL instance name. But I see whenever I use the correct SQL server details then it's connecting to localhost.

When I give the wrong IP of server name then it's trying to connect the correct one.

Below is my conf

- module: mssql
  metricsets:
    - "transaction_log"
    - "performance"
  hosts: ["sqlserver://SQLINSTANCE"]
  username: domain\username
  password: Securepassword
  period: 10

I have attached both logs with the correct SQL servername and the wrong server name.

PS C:\Program Files\metricbeat2_7102> .\metricbeat.exe -e -c .\metricbeat.yml
2021-01-20T15:53:19.368+0100    INFO    instance/beat.go:645    Home path: [C:\Program Files\metricbeat2_7102] Config path: [C:\Program Files\metricbeat2_7102] Data path: [C:\Program Files\metricbeat2_7102\data] Logs path: [C:\Program Files\metricbeat2_7102\logs]
2021-01-20T15:53:19.369+0100    INFO    instance/beat.go:653    Beat ID: a5b8cff5-1ba5-47ac-a86c-a1ae6705b7a3
2021-01-20T15:53:19.394+0100    INFO    [beat]  instance/beat.go:981    Beat info       {"system_info": {"beat": {"path": {"config": "C:\\Program Files\\metricbeat2_7102", "data": "C:\\Program Files\\metricbeat2_7102\\data", "home": "C:\\Program Files\\metricbeat2_7102",
 "logs": "C:\\Program Files\\metricbeat2_7102\\logs"}, "type": "metricbeat", "uuid": "a5b8cff5-1ba5-47ac-a86c-a1ae6705b7a3"}}}
2021-01-20T15:53:19.395+0100    INFO    [beat]  instance/beat.go:990    Build info      {"system_info": {"build": {"commit": "aacf9ecd9c494aa0908f61fbca82c906b16562a8", "libbeat": "7.10.2", "time": "2021-01-12T23:22:55.000Z", "version": "7.10.2"}}}
2021-01-20T15:53:19.395+0100    INFO    [beat]  instance/beat.go:993    Go runtime info {"system_info": {"go": {"os":"windows","arch":"amd64","max_procs":2,"version":"go1.14.12"}}}
2021-01-20T15:53:19.407+0100    INFO    [beat]  instance/beat.go:997    Host info       {"system_info": {"host": {"architecture":"x86_64","boot_time":"2021-01-16T04:18:38.92+01:00","name":"vmname","ip":["10.99.106.66/24","10.99.25.66/24","10.99.25.68/24","10.99.25.69/
24","10.99.237.1/29","10.99.105.66/24","169.254.2.202/16","::1/128","127.0.0.1/8"],"kernel_version":"10.0.14393.4169 (rs1_release.210107-1130)","mac":["00:50:56:86:21:e1","00:50:56:86:ba:71","00:50:56:86:52:ff","00:50:56:86:78:e2","02:9f:7b:5e:34:f9"],"os":{"family":"win
dows","platform":"windows","name":"Windows Server 2016 Datacenter","version":"10.0","major":10,"minor":0,"patch":0,"build":"14393.4169"},"timezone":"CET","timezone_offset_sec":3600,"id":"53978859-35a9-402e-94c8-01a00295312f"}}}
2021-01-20T15:53:19.408+0100    INFO    [beat]  instance/beat.go:1026   Process info    {"system_info": {"process": {"cwd": "C:\\Program Files\\metricbeat2_7102", "exe": "C:\\Program Files\\metricbeat2_7102\\metricbeat.exe", "name": "metricbeat.exe", "pid": 9280, "ppid":
 5628, "start_time": "2021-01-20T15:53:18.687+0100"}}}
2021-01-20T15:53:19.408+0100    INFO    instance/beat.go:299    Setup Beat: metricbeat; Version: 7.10.2
2021-01-20T15:53:19.409+0100    INFO    [publisher]     pipeline/module.go:113  Beat name: vmname
2021-01-20T15:53:19.595+0100    INFO    instance/beat.go:455    metricbeat start running.
2021-01-20T15:53:19.595+0100    INFO    [monitoring]    log/log.go:118  Starting metrics logging every 30s
2021-01-20T15:53:21.604+0100    INFO    [monitoring]    log/log.go:153  Total non-zero metrics  {"monitoring": {"metrics": {"beat":{"cpu":{"system":{"ticks":125,"time":{"ms":125}},"total":{"ticks":281,"time":{"ms":281},"value":281},"user":{"ticks":156,"time":{"ms":156}}}
,"handles":{"open":218},"info":{"ephemeral_id":"07f37ab7-6f93-41ed-9e9a-b483bb08bb66","uptime":{"ms":2855}},"memstats":{"gc_next":16323904,"memory_alloc":9876376,"memory_total":36700232,"rss":55472128},"runtime":{"goroutines":49}},"libbeat":{"config":{"module":{"running"
:0}},"output":{"type":"logstash"},"pipeline":{"clients":2,"events":{"active":0}}},"system":{"cpu":{"cores":2}}}}}
2021-01-20T15:53:21.604+0100    INFO    [monitoring]    log/log.go:154  Uptime: 2.8551547s
2021-01-20T15:53:21.605+0100    INFO    [monitoring]    log/log.go:131  Stopping metrics logging.
2021-01-20T15:53:21.605+0100    INFO    instance/beat.go:461    metricbeat stopped.
2021-01-20T15:53:21.606+0100    ERROR   instance/beat.go:956    Exiting: 2 errors: could not create connection to db: error doing ping to db: Unable to open tcp connection with host 'localhost:1433': dial tcp 127.0.0.1:1433: connectex: No connection could be made because
 the target machine actively refused it.; could not create connection to db: error doing ping to db: Unable to open tcp connection with host 'localhost:1433': dial tcp 127.0.0.1:1433: connectex: No connection could be made because the target machine actively refused it.
Exiting: 2 errors: could not create connection to db: error doing ping to db: Unable to open tcp connection with host 'localhost:1433': dial tcp 127.0.0.1:1433: connectex: No connection could be made because the target machine actively refused it.; could not create conne
ction to db: error doing ping to db: Unable to open tcp connection with host 'localhost:1433': dial tcp 127.0.0.1:1433: connectex: No connection could be made because the target machine actively refused it.

Below is the log with the wrong SQL instance name

PS C:\Program Files\metricbeat2_7102> .\metricbeat.exe -e -c .\metricbeat.yml
2021-01-20T20:36:35.021+0100    INFO    instance/beat.go:645    Home path: [C:\Program Files\metricbeat2_7102] Config path: [C:\Program Files\metricbeat2_7102] Data path: [C:\Program Files\metricbeat2_7102\data] Logs path: [C:\Program Files\metricbeat2_7102\logs]
2021-01-20T20:36:35.022+0100    INFO    instance/beat.go:653    Beat ID: a5b8cff5-1ba5-47ac-a86c-a1ae6705b7a3
2021-01-20T20:36:35.044+0100    INFO    [beat]  instance/beat.go:981    Beat info       {"system_info": {"beat": {"path": {"config": "C:\\Program Files\\metricbeat2_7102", "data": "C:\\Program Files\\metricbeat2_7102\\data", "home": "C:\\Program Files\\metricbeat2_7102",
 "logs": "C:\\Program Files\\metricbeat2_7102\\logs"}, "type": "metricbeat", "uuid": "a5b8cff5-1ba5-47ac-a86c-a1ae6705b7a3"}}}
2021-01-20T20:36:35.045+0100    INFO    [beat]  instance/beat.go:990    Build info      {"system_info": {"build": {"commit": "aacf9ecd9c494aa0908f61fbca82c906b16562a8", "libbeat": "7.10.2", "time": "2021-01-12T23:22:55.000Z", "version": "7.10.2"}}}
2021-01-20T20:36:35.045+0100    INFO    [beat]  instance/beat.go:993    Go runtime info {"system_info": {"go": {"os":"windows","arch":"amd64","max_procs":2,"version":"go1.14.12"}}}
2021-01-20T20:36:35.058+0100    INFO    [beat]  instance/beat.go:997    Host info       {"system_info": {"host": {"architecture":"x86_64","boot_time":"2021-01-16T04:18:39.18+01:00","name":"vmname","ip":["10.99.106.66/24","10.99.25.66/24","10.99.25.68/24","10.99.25.69/
24","10.99.237.1/29","10.99.105.66/24","169.254.2.202/16","::1/128","127.0.0.1/8"],"kernel_version":"10.0.14393.4169 (rs1_release.210107-1130)","mac":["00:50:56:86:21:e1","00:50:56:86:ba:71","00:50:56:86:52:ff","00:50:56:86:78:e2","02:9f:7b:5e:34:f9"],"os":{"family":"win
dows","platform":"windows","name":"Windows Server 2016 Datacenter","version":"10.0","major":10,"minor":0,"patch":0,"build":"14393.4169"},"timezone":"CET","timezone_offset_sec":3600,"id":"53978859-35a9-402e-94c8-01a00295312f"}}}
2021-01-20T20:36:35.058+0100    INFO    [beat]  instance/beat.go:1026   Process info    {"system_info": {"process": {"cwd": "C:\\Program Files\\metricbeat2_7102", "exe": "C:\\Program Files\\metricbeat2_7102\\metricbeat.exe", "name": "metricbeat.exe", "pid": 12364, "ppid"
: 5628, "start_time": "2021-01-20T20:36:34.340+0100"}}}
2021-01-20T20:36:35.058+0100    INFO    instance/beat.go:299    Setup Beat: metricbeat; Version: 7.10.2
2021-01-20T20:36:35.059+0100    INFO    [publisher]     pipeline/module.go:113  Beat name: vmname
2021-01-20T20:36:38.045+0100    INFO    [add_cloud_metadata]    add_cloud_metadata/add_cloud_metadata.go:89     add_cloud_metadata: hosting provider type not detected.
2021-01-20T20:36:39.618+0100    INFO    instance/beat.go:424    metricbeat stopped.
2021-01-20T20:36:39.618+0100    ERROR   instance/beat.go:956    Exiting: 2 errors: could not create connection to db: error doing ping to db: lookup nosqlinstance: no such host; could not create connection to db: error doing ping to db: lookup nosqlinstance: no such host
Exiting: 2 errors: could not create connection to db: error doing ping to db: lookup nosqlinstance: no such host; could not create connection to db: error doing ping to db: lookup nosqlinstance: no such host

Does anyone have any idea on this?

Kaiyan_Sheng · January 20, 2021, 7:12pm

@Mario_Castro Do you have any insights on this?

phani_akkina · January 20, 2021, 8:05pm

The same SQL Instance working fine with SQL module. Have connection issues only when I use MSSQL Module.

Mario_Castro · January 20, 2021, 10:21pm

I don't see anything clearly wrong. I'd like to see more concrete information about the configuration that you are using @phani_akkina The only thing I see that might be wrong is that you are writing username/domain in the username field. Domain is not allowed there. Are you doing the same with the sql module?

Sql module is really barebones but it uses the same foundations that mssql module so there's no direct reason to find a issue there.

Can you provide the exact connection details you are using in both modules?

phani_akkina · January 21, 2021, 11:16am

@Mario_Castro I tried with both Domain user and local user. For both I faced the same issue.

Here are both modules config file

Using MSSQL module:

- module: mssql
  metricsets:
    - "transaction_log"
    - "performance"
  hosts: ["sqlserver://sqlinstance"]
  username: username
  password: password
  period: 10

Using SQL module:

- module: sql
  metricsets:
    - query
  period: 60s
  hosts: ["sqlserver://sqlinstance"]
  username: username
  password: password
  driver: "mssql"
  sql_query: "select name FROM sysdatabases WHERE (DATABASEPROPERTYEX(name, N'Status') != 'ONLINE') OR (has_dbaccess(name) = 0)"
  sql_response_format: table
  processors:
  - add_tags:
      tags: sql
      target: "module"

Let me know if require any other details

I have one more question. Is MSSQL module will not work with the domain account?

Mario_Castro · January 21, 2021, 1:10pm

Uhmmm, can you try setting all parameters in the hosts section? I'm looking at the code and I suspect that there's a bug where the username/password is not being parsed at all in mssql.

I think the format would be something like sqlserver://username:password@instance/domain

Extra info: https://github.com/elastic/beats/issues/13006

phani_akkina · January 21, 2021, 4:28pm

Using the above way also I am getting same error. Its trying to connect Localhost instead of MSSQL instance name

PS C:\Program Files\metricbeat2_7102> .\metricbeat.exe -e -c .\metricbeat.yml
2021-01-21T17:26:16.592+0100 INFO instance/beat.go:645 Home path: [C:\Program Files\metricbeat2_7102] Config path: [C:\Program Files\metricbeat2_7102] Data path: [C:\Program Files\metricbeat2_7102\data] Logs path: [C:\Program Files\metricbeat2_7102\logs]
2021-01-21T17:26:16.597+0100 INFO instance/beat.go:653 Beat ID: a5b8cff5-1ba5-47ac-a86c-a1ae6705b7a3
2021-01-21T17:26:16.630+0100 INFO [beat] instance/beat.go:981 Beat info {"system_info": {"beat": {"path": {"config": "C:\Program Files\metricbeat2_7102", "data": "C:\Program Files\metricbeat2_7102\data", "home": "C:\Program Files\metricbeat2_7102",
"logs": "C:\Program Files\metricbeat2_7102\logs"}, "type": "metricbeat", "uuid": "a5b8cff5-1ba5-47ac-a86c-a1ae6705b7a3"}}}
2021-01-21T17:26:16.631+0100 INFO [beat] instance/beat.go:990 Build info {"system_info": {"build": {"commit": "aacf9ecd9c494aa0908f61fbca82c906b16562a8", "libbeat": "7.10.2", "time": "2021-01-12T23:22:55.000Z", "version": "7.10.2"}}}
2021-01-21T17:26:16.632+0100 INFO [beat] instance/beat.go:993 Go runtime info {"system_info": {"go": {"os":"windows","arch":"amd64","max_procs":2,"version":"go1.14.12"}}}
2021-01-21T17:26:16.675+0100 INFO [beat] instance/beat.go:997 Host info {"system_info": {"host": {"architecture":"x86_64","boot_time":"2021-01-16T04:18:40.33+01:00","name":"vmname","ip":["10.99.106.66/24","10.99.25.66/24","10.99.25.68/24","10.99.25.69/
24","10.99.237.1/29","10.99.105.66/24","169.254.2.202/16","::1/128","127.0.0.1/8"],"kernel_version":"10.0.14393.4169 (rs1_release.210107-1130)","mac":["00:50:56:86:21:e1","00:50:56:86:ba:71","00:50:56:86:52:ff","00:50:56:86:78:e2","02:9f:7b:5e:34:f9"],"os":{"family":"win
dows","platform":"windows","name":"Windows Server 2016 Datacenter","version":"10.0","major":10,"minor":0,"patch":0,"build":"14393.4169"},"timezone":"CET","timezone_offset_sec":3600,"id":"53978859-35a9-402e-94c8-01a00295312f"}}}
2021-01-21T17:26:16.675+0100 INFO [beat] instance/beat.go:1026 Process info {"system_info": {"process": {"cwd": "C:\Program Files\metricbeat2_7102", "exe": "C:\Program Files\metricbeat2_7102\metricbeat.exe", "name": "metricbeat.exe", "pid": 648, "ppid":
5628, "start_time": "2021-01-21T17:26:13.724+0100"}}}
2021-01-21T17:26:16.677+0100 INFO instance/beat.go:299 Setup Beat: metricbeat; Version: 7.10.2
2021-01-21T17:26:16.692+0100 INFO [publisher] pipeline/module.go:113 Beat name: vmname
2021-01-21T17:26:16.880+0100 INFO instance/beat.go:455 metricbeat start running.
2021-01-21T17:26:16.879+0100 INFO [monitoring] log/log.go:118 Starting metrics logging every 30s
2021-01-21T17:26:18.913+0100 INFO [monitoring] log/log.go:153 Total non-zero metrics {"monitoring": {"metrics": {"beat":{"cpu":{"system":{"ticks":203,"time":{"ms":203}},"total":{"ticks":468,"time":{"ms":468},"value":468},"user":{"ticks":265,"time":{"ms":265}}}
,"handles":{"open":209},"info":{"ephemeral_id":"88723c48-9f39-4ddf-aaf9-eb5f0aeb8b77","uptime":{"ms":3989}},"memstats":{"gc_next":16420064,"memory_alloc":8810464,"memory_total":29588296,"rss":54456320},"runtime":{"goroutines":49}},"libbeat":{"config":{"module":{"running"
:0}},"output":{"type":"logstash"},"pipeline":{"clients":2,"events":{"active":0}}},"system":{"cpu":{"cores":2}}}}}
2021-01-21T17:26:18.916+0100 INFO [monitoring] log/log.go:154 Uptime: 4.00231s
2021-01-21T17:26:18.916+0100 INFO [monitoring] log/log.go:131 Stopping metrics logging.
2021-01-21T17:26:18.922+0100 INFO instance/beat.go:461 metricbeat stopped.
2021-01-21T17:26:18.922+0100 ERROR instance/beat.go:956 Exiting: 2 errors: could not create connection to db: error doing ping to db: Unable to open tcp connection with host 'localhost:1433': dial tcp 127.0.0.1:1433: connectex: No connection could be made because
the target machine actively refused it.; could not create connection to db: error doing ping to db: Unable to open tcp connection with host 'localhost:1433': dial tcp [::1]:1433: connectex: No connection could be made because the target machine actively refused it.
Exiting: 2 errors: could not create connection to db: error doing ping to db: Unable to open tcp connection with host 'localhost:1433': dial tcp 127.0.0.1:1433: connectex: No connection could be made because the target machine actively refused it.; could not create conne
ction to db: error doing ping to db: Unable to open tcp connection with host 'localhost:1433': dial tcp [::1]:1433: connectex: No connection could be made because the target machine actively refused it.

Mario_Castro · January 21, 2021, 5:58pm

Is it possible that you have some dns issue? Like some entry on your /etc/hosts or the equivalent in windows? Can you set the IP directly?

phani_akkina · January 22, 2021, 8:08am

Hosts file should not be an issue, Because it's working perfectly fine with SQL module.

And I tried with multiple MSSQL servers, all have the same issue

Mario_Castro · January 25, 2021, 5:55pm

So, I have just downloaded the latest version of MSSQL from Docker Hub https://hub.docker.com/_/microsoft-mssql-server and I started it with the recommended settings (by reading the Readme in Docker Hub about accepting EULA and setting a password), I have taken the latest version of Metricbeat, activate the module with a normal set of settings and everything runs without problem

- module: mssql
  metricsets:
    - "transaction_log"
    - "performance"
  hosts: ["sqlserver://172.17.0.2"]
  username: sa
  password: "Mypass_1234"
  period: 10s

So either you have something in your environment or in Beats that is interfering. My only suggestion is to try to setup the module using an IP directly. If that works, then we can try to narrow dawn the path to solve the issue.

phani_akkina · January 28, 2021, 12:09pm

I tried configuring the IP address.

Unfortunately no luck. I have the same issue using IP as well

Mario_Castro · January 28, 2021, 3:27pm

What's your MSSQL version?

phani_akkina · January 29, 2021, 8:44am

We are using SQL SERVER 2017

phani_akkina · February 5, 2021, 3:32pm

Reinstallation of the metricbeat service fixed the issue. Nothing changed other than that.

Thanks for the support from everyone.

system · March 5, 2021, 5:32pm

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.