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.

#7

Definitely still interested in this please @wayne_johnstone. Being able to access data held in Azure SQL is a pre-req for various useful flows for us, including but not limited to automation of Joiner/Mover/Leaver processes. Currently those workflows involve various tedious and repetitive manual tasks where any mistake might be costly, so this is more or less an ideal use case for automation.

#8

Hi Adam,
Hope to have an answer for you by EOD tomorrow.
Thanks,
Wayne

1 Like
#9

Hi Adam, Our engineers weren’t able to get a connection with Azure SQL today. I will get an update on Tuesday morning with hopefully some good news.
Have a good weekend.
Wayne

1 Like
#10

Hi Adam,
We have a fix in place which is in testing, we hope to deploy it this week.
I will keep you posted.
Thanks.

1 Like
#11

Fantastic - thank you very much for taking this on. I really appreciate it. Getting this working should unlock some great new capabilities for our InsightConnect.

1 Like
#12

Adam, the fix is deployed in the latest version 3.0.5 Rapid7 Extensions Can you please retest and let us know when you get a chance.

Have a good weekend.
Thanks

2 Likes
#13

Thanks Bindu - I see your commit [SOAR-10035] SQL Plugin pymssql update (#1337) (#1345) · rapid7/insightconnect-plugins@e21a793 · GitHub

I’ve updated the plugin to 3.0.5, and updated the plugin in the workflow, and published the workflow. I’ve confirmed via docker ps that the Orchestrator is now using the 3.0.5 image when I carry out a test run of the Workflow:

rapid7/sql:3.0.5

However, I can’t get the Connection Test or the SQL Action to work - it’s the same issue as before:

Connection test failed!

Unable to connect to the server. Check connection credentials. Response was: (pymssql.OperationalError) (20002, 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)

I’ve noticed that the Plugins & Tools > Connections still shows SQL 3.0.4; there is no SQL 3.0.5
mentioned there, which doesn’t seem right. I tried creating a brand new Connection in case there was something not right with the existing one, but that doesn’t help either.

Hopefully I’m missing something obvious, but I can’t think what else to check.

#14

Hi Adam,

We do have an instance in Azure that is working. Could you email me and we could plan a meeting around your configuration?

Thanks,
Wayne Johnstone
wjohnstone@rapid7.com

1 Like
#15

Thanks Wayne. I’ve sent you an email.