Is anyone using InsightConnect to interact with an Azure SQL database?
Goal
Select some data from a table hosted in an Azure SQL database using the SQL plugin.
Problem
I’ve arranged connectivity - I can connect to the endpoint on port 1433 from a shell on the Orchestrator - but the Connection Test for the SQL plugin fails:
Unable to connect to the server. Check connection credentials. Response was: (pymssql.OperationalError) (20009, b'DB-Lib error message 20002, severity 9:\nAdaptive Server connection failed (prodsqlserver-fg.secondary.database.windows.net)\n') (Background on this error at: http://sqlalche.me/e/e3q8)
Tried again, double-checked my work
Looking at that message, my first thought was that I must have entered the credentials wrongly, so I went back and set up the connection from scratch. I also verified that the credentials can get me into Azure Portal, and that I had selected Database Type MSSQL. I tried the Connection Test again: same problem as before.
So: credentials are valid, and I’ve verified the connection setup as much as I can.
Is Azure SQL supported?
Azure SQL isn’t mentioned explicitly in the plugin documentation, but I couldn’t see any reason why this shouldn’t work. The InsightConnect SQL extension wraps pymssql to talk to MS SQL databases:
- Azure SQL support was introduced in pymssql 2.1.1.
- our InsightConnect has the latest R7 SQL plugin, confirmed with docker ps (
rapid7/sql:3.0.4
) - the SQL plugin 3.0.4 requires pymssql 2.1.5, which is the currently the latest version of pymssql.
- pymssql requires TDS protocol 7.1 or newer with SSL support to connect to Azure SQL - not 100% sure about this one, but I see tds version 8.0 mentioned in
Dockerfile
and/etc/freetds/freetds.conf