Connecting ServiceNow to an Azure SQL Database

[Walt Janssens]

Hi,

We are trying to fill an Azure SQL Database with our ServiceNow data for reporting purposes.
We tried using the JDBC Connector, but because this uses the MID server, we are dependent on our Networking team.
Also we had some issues with the connection, which results in this not being a stable option.

What we would prefer is using REST API, SaaS to SaaS. Is there anyone who has experience with this?

This would be really helpful.

Thanks in Advance.

[Sohail Khilji]

Hi @Walt Janssens ,

For SQL recommended is JDCB, can you let us know why this was not a stable solution and what errors and pitfalls did you face?

As you said azure sql is expecting data from ServiceNow for reporting, yes this is possible too... you can create rest api user in servicenow with proper web service enables and with rest api roles. remove restriction on the table if any and azure can fetch data from servicenow using REST API using Pagination...

i hope this helps...

[Walt Janssens]

Hi @Sohail Khilji,

At first we faced networking issues. Firewalls etc. Then our TEST environment didn't have the right Crypto Module. Then everything worked, suddenly it stopped working because the IP address of the Azure server changed. 
Then everything worked, except for, again, the TEST environment. Tried a couple days later and it worked again, without changing anything..

[Sohail Khilji]

You issue is intermittent; you can surely find the root cause for that and make sure your JDBC is working smooth. There can be certain changes on firewall which is stopping you... i hope my info helps..

[Ryan Duce]

A few recommendations from me:

• MID servers should have line of sight access to the SQL database. Yes, you will need your cloud network team support with this, but it's far more secure to have a MID server in place to run your JDBC connector through than expose the DB across the public internet.
• Make sure your ServiceNow MID server service user has access to the crypto module you need. Don't do this by assigning any additional out-of-box roles to the MID server user over and above the baseline mid_server role, as that doesn't respect least-privilege principles. Bring it into the crypto module with a "user" mapping or a custom role if multiple MID server users need the same access.
• You should be using an FQDN, not an IP address.
• Stick with it on JDBC - don't use REST APIs, they won't necessarily solve the specific problems you're having and you'll need pagination for large datasets, it will result in more (and less performant) egress traffic from Azure which potentially means higher costs.
• Some advice has been shared here for gotchas getting Azure SQL DB going through MID servers.