Microsoft SQL Connector not able to connect

Elastic Stack Kibana
1

Hello,

So Basically what the title says. I´m trying to create a native connector for an Azure deployed Microsoft SQL Database and i´m not being able to connect to the db. All this in the Connectors tab.

I have this connected to logstash by i´m trying to migrate what i do in logstash to a connector (To avoid deploying logstash), so i know the db and the connection should work.

The only feedback that i get from Elastic is:

Connector error

Can't connect to Microsoft SQL on hostname.database.windows.net

I don´t know if i may be missing some type of data when creating the connector or what could it be since i don´t get any more info.

This is how i have the data:

Host
hostname.database.windows.net

Port
1433

Username
Username@hostname

Password
password

Database
hostname/databaseName (Already tried this with just the hostname, and just the db name)

Comma-separated list of tables
*

Schema
dbo

Enable SSL
true (Already tried with this off)

Validate host
true

Feel free to ask any questions in order to understand a little bit more.
Thanks!

2

Hey sebaV_20,

I don't see anything bad in your configuration.

Just to check - do you have a firewall on the MSSQL side? Are you able to connect with these credentials from your host?

Additionally, you can check the logs of your cloud deployment to see if any additional errors are reported by the connectors service.

3

Hey Artem,

Thanks for the quick answer.

I am able to connect with those credentials, even the logstash pipe that i have locally deployed in a Docker Container is working fine.

It could be, as you point out, the firewall of the Server. I set some rules using the ip´s that i got from Dev Tools using the GET /_cat/nodes?v call.

But i´m not sure if those are the ones that the elastic connector uses to connect to the Azure Db on Server.

From the Elastic Logs and metrics from the monitoring section in deployment, i have no logs refering to this issue. (I guess elastic is not getting info about it)

4

You can check your cloud deployment settings and get Enterprise Search hostname from it - this host will be used to access your MSSQL server.

Additionally, have you checked logs in MSSQL server - do you have any records there about the connector attempting to connect to it?

5

Will check on that host name, thanks.

And yes, i have checked the logs in MSSQL server, i have no records even of a denied access.
To check if the logs are working as expected, i tried, connecting from an ip that wasn´t on my rules, and it logged correctly the failed attempt. So i´m thinking the issue could actually be in the Host i´m giving the connector to reach the server, maybe this: hostname.database.windows.net is missing something?

I tried typing whatever on the Host just to check if the Error would change and it is still the same, so no help from the exception message there to know if it is actually reaching the server.

Thanks again for taking the time to answer !

6

Okay it's good to know that connectors were unable to reach out to your deployment.

hostname.database.windows.net should be fine as an option.

Do you have traffic filters enabled in you cloud deployment?

Additionally, an easy way to do some good troubleshooting is to pull the connectors repository to a unix machine that has access to the MSSQL instance and try to repeat the steps that you've done while setting service.log_level: DEBUG.

If you have such setup, you can edit connector code to have more logs or try to change the connection settings. I can help you with that too, if nothing works.

7

I was finally able to connect it !

After i was able to check the logs correctly i found out it was basically a combination of an SSL issue with some other stuff with the credentials of the db.

Thank you very much for your help

8

Happy to hear you were able to connect!

How did you figure this out? Have there been anything in the process that could have helped you understand it faster from the connector side?

9

It was a combination between elastic own internal support tickets and finally understood how to take a good look at the logs of monitoring.
Once the monitoring worked good, it helped me with the things related to other fields, and elastic support helped with the ssl, giving instructions on how to obtain actually what was needed.

1 Like
10

Do you have any details on how you obtained better errors and how you got the required values? (I've got the exact same error)

11

Should someone find this in the future.

The username to use is the python connection string username in Azure. Something like user@sqlserverhostname. Ssl must be on, and the narrow certificate field should contain the certificate chain or certificate authority in readable pem format. Something like one or more "-----BEGIN CERTIFICATE-----" blocks. Just paste it in from an editor, it allows line enters in the ui but doesnt show them. You can download this by going to the Azure sql server https:// hostname:1433/ and saving the pem certificate from the browser. Make sure to grab the ca and not main one.

12

Hi, i have similar issue but i couldnt resolve it , any suggestions pls.

My Azure SqlConnector values in Kibana are (SSL certificate i pulled from Azure sql using python using function pem_cert = ssl.DER_cert_to_PEM_cert(der_certs)
Host
sqlserver1230.database.windows.net
Port
1433
Username
test_abc_123
Password

Database
test
Comma-separated list of tables
table1_creditcard
Schema
dbo
Enable SSL verification
true
SSL certificate
-----BEGIN CERTIFICATE-----
dddddd
sdaf
asd
-----END CERTIFICATE-----
Validate host
true

below is error for my MS Sql connector configured for Azure SQL

[FMWK][09:18:48][WARNING] [Connector id: xx, index name: content-new_azure_sql] Something went wrong while executing query. Exception: [('SSL routines', '', 'certificate verify failed')]
[FMWK][09:18:51][WARNING] [Connector id: xx, index name: content-new_azure_sql] Something went wrong while executing query. Exception: [('SSL routines', '', 'certificate verify failed')]
[FMWK][09:18:56][WARNING] [Connector id: xx, index name: content-new_azure_sql] Something went wrong while executing query. Exception: [('SSL routines', '', 'certificate verify failed')]
[FMWK][09:18:56][ERROR] [Connector id: xx, index name: content-new_azure_sql] Can't connect to Microsoft SQL on sqlserver1230.database.windows.net

13

Hi @Madhu_Challapalli,

I think the best thing you can do is try the certificate you've pulled out without connectors first - if you can connect with it, then it's a problem on connector's side and we can fix it, otherwise there should be a different certificate that you can use to connect.

Unfortunately I'm not very familiar with how Azure SQL generates/treats certificates.