Integrate to Microsoft SQL DB Server via Windows AD & Native SQL Authentication

[Niamul Arifin]

This is my first community article. Appreciate your feedback on this.

This article talks about how to connect to microsoft sql server via JDBC step (integration hub) via Windows AD authentication and Native SQL authentication. 

Difference between Windows AD Authentication vs. Native SQL Authentication 

Windows AD Authentication uses authentication mechanism using the service account that it is connecting to. Service account is a non-human account generally used by applications for performing services like integrations, authentications etc.

Native SQL Authentication means MSSQL server local account. Usually a DBA creates these local account and provides the username and password to the Servicenow team. This type of authentication is also referred to local account authentication.

For Windows AD authentication connection configuration:

1. Create a JDBC connection from Connection and fill out the required details including hostname, port, database name, and format = SQLserver and Use MID server checked. It should have JDBC, PowerShell capability and Orchestration as Application.

2. The connection URL is in read-only mode at this point. You need to add one property (integratedSecurity=true) in this connection URL.

3. Go to jdbc_connection table, Bring the Connection URL field from the gear icon (if not there), and here you can edit the text (double click on the value in this list) and add the property at the very end of the connection string. 

4. No credential is necessary as it is going to authenticate using Windows AD authentication.

5. THIS PART IS IMPORTANT. Make sure your midserver is using the same service account (Log on As) as the Service account that is a member of MSSQL hosting server.

For example,

Service account Name = abcd.xyz

MSSQL hosting server = qwerty123.example.com (IP ADDR: 70.9.91.2)

MID server service should be running with abcd.xyz and abcd.xyz should be a member of qwerty123.example.com (Based on Active directory concept)

6. Set MID server property: 

Name: mid.property.jdbc_operations

Value: select,update,insert,delete,show,create,describe,begin,if,end,not,exists 

Make sure the service account has the read/write privileges in the database.

7. If you have a dedicated midserver only for DB integration, if you can have the include type IP range in the midserver configuration. Also, midserver should have JDBC and Powershell capabilities.

8. Once this is complete, assuming you have integrationhub subscription with JDBC step action, you can select the connection and Connection alias (You have to create a connection alias and add the connection in the connection tab that you created in step 1).

9. Write down your SQL queries and Click Test with JDBC. (THIS IS THE ONLY WAY I HAVE FOUND TO TEST WHETHER THE CONNECTION IS ESTABLISHED VIA WINDOWS AD AUTHENTICATION)

10. If you see data retrieval is successful, then you have done a great job.

For Native SQL authentication connection configuration:

For Local account authentication, MSSQL admin needs to create a local admin in the SQL server side and provide to you. It should have read/write privileges.

1. Create a JDBC credential from credential and provide the username and password provided by the SQL server administrator.

2. Go to your JDBC connection, Select the credential that you created in the credential reference field. Provide all the required fields including hostname, port, database name, and format = SQLserver and Use MID server checked. It should have JDBC, PowerShell capability and Orchestration as Application.

2. Select the midserver. Make sure to add the midserver property mid.property.jdbc_operations and values mentioned previously.

3. Click Test Credentials, if everything is okey, you should be successful.

For Data retrieval/insertion, you can follow the JDBC step action mentioned above and perform a Test with JDBC.