Azure SQL from InsightConnect

InsightConnect
,
#1

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:

#2

N.B. I’ve also confirmed that the credentials I want to use for InsightConnect can import data from the database/table into Excel.

#3

Hi Adam, I will do some testing and let you know what we find, hopefully, have an answer this afternoon.

Quick Question: Did you whitelist the IP Address of the server that the orchestrator is running on for your Azure SQL instance?

1 Like
#4

Thanks for taking a look @wayne_johnstone.

Definitely a good first question to ask. We did have to arrange an allowlist entry for the range the orchestrator appears from to get to the point I describe above. Before that allowlist entry was in place, Azure responded to the SQL plugin connection test with a Connection Refused.

1 Like
#5

Hi Adam, I had no luck getting the connection working, I am going to pass this on to an engineer to identify what the issue is. I hope to update you early next week.

1 Like
#6

I’ve read that Azure SQL always runs on the latest version of SQL Server, which looks like CU16 15.0.4223.1, whereas the R7 SQL plugin documentation claims to support MSSQL 2019 15.0.2000.5, which was the RTM version of SQL Server 2019.

I couldn’t find a definitive source, and granted the versions aren’t exactly aligned, but hopefully there shouldn’t be any compatibility issues there given that they’re both 15.0.x

Thanks again Wayne. Have a great weekend.