I faced this issue with one of my customers, and I had to spend a substantial amount of time reading ServiceNow and Microsoft documentation, so I thought I'd summarize the solution which finally helped to solve the task.
1. Update JDBC driver on MID server and provide MSAL4J library:
- Download the latest JDBC driver for SQL Server from the Microsoft website, unzip it. We will need only the JAR file with jre11 in the name.
- Download MSAL4J library from the Maven repository (the official repository for Microsoft libraries). This is also a JAR file.
- Login to ServiceNow with an account having admin privileges, go to MID Server > JAR files, add both JAR files by creating two new records in this table, and then by attaching one JAR file to each record.
- Deactivate any other records with JAR files in case there are any of them in this table.
- As suggested by this knowledge article, add these two lines to agent/conf/wrapper-override.conf file on the MID server:
wrapper.java.classpath.1=extlib/*.jar
wrapper.java.classpath.2=lib/*.jar
- Restart the MID server. Make sure it restarts successfully.
2. Make sure that agent/extlib directory contains both two JAR files which we’ve uploaded to ServiceNow, and only them.
- In case you need some other JAR files for other purposes, you can keep them enabled, but some of them might lead to errors when restarting MID server. To isolate the JAR file leading to errors, deactivate all JAR files except JDBC driver and MSAL4J library, and then start activating them one by one in ServiceNow, restart MID server each time and make sure there’s no error.
- Enable proper domain name resolution for the FQDN of the SQL server. In case you are using private endpoint, the FQDN should point to an internal IP address. Either change DNS configuration on the MID server, or add a line to /etc/hosts file:
<IP address of private endpoint> <FQDN of private endpoint>
3. Make sure there’s network connectivity between MID server and the SQL server via private endpoint. In order to do it, after enabling proper domain name resolution, run this command:
openssl s_client -connect <FQDN of private endpoint>:<port, usually it is 1433 for SQL Server>
You should see CONNECT in the first line of the output, and then the SSL certificate of the SQL server.
4. Configure a data source in ServiceNow:
- Add Connection URL field to the form of the data source.
- When configuring data source, use:
- Type = “JDBC”
- Format = “SQLServer”
- Connection URL = “jdbc:sqlserver://<FQDN of private endpoint>:<port>;databaseName=<name of the database>;hostNameInCertificate=<see below>;authentication=ActiveDirectoryServicePrincipal”
- Username = the application (client) ID of the service principal
- Password = the service principal secret
- Query = “Specific SQL” (we are using this because the names of the views in SQL Server might be too long for respective field in ServiceNow, and because you might want to apply some filter when fetching data from SQL Server)
- SQL statement = “SELECT * FROM <table or view name, make sure you align with DB experts to use proper prefix before the name of the view or table>;”
- When saving data source record, check that connection URL and SQL statement which you have provided are not overwritten by ServiceNow with something else. If it is overwritten, change them again and save the record again.
5. Test the connection by loading 20 records:
- There might be an error saying something about wrong hostname in certificate. The text of the error will mention the name in the actual certificate from the SQL server. You can remediate this error by adjusting the hostNameInCertificate parameter in your JDBC connection URL.
