Microsoft SQL Connector not able to connect

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!

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.

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)

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?

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 !

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.