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.
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)
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.
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.
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.
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.
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.
Apache, Apache Lucene, Apache Hadoop, Hadoop, HDFS and the yellow elephant
logo are trademarks of the
Apache Software Foundation
in the United States and/or other countries.