AttilaVarga
Mega Sage

Have you ever heard about the SQL API in ServiceNow? Either way, it doesn't matter, because it is now called Live Connect .

This product was introduced in the Zurich release as the SQL API. This feature opens the door for use cases where data needs to be gathered from the ServiceNow database by external systems.

The purpose of my article is not just to describe what Live Connect is. I would like to introduce how it can be used and explain the value behind it. So, let's get started!

 

My plan is to write two articles on this topic. The first one is about introducing the basics, showing how it can be configured, and executing the first couple of SQL queries. Then, in the second article, I would like to show the real value of using SQL commands instead of GlideRecord.

 

Live Connect in general

Live Connect is ServiceNow's query-only SQL interface for external clients. It lets ODBC- or JDBC-compatible applications execute SQL queries against authorized ServiceNow tables and receive tabular results. Typical clients include business-intelligence, reporting, and data-analysis tools.

An external client sends a query to the ServiceNow instance when it needs data, rather than relying on a separately synchronized copy. The connection only reads records. It is for analysis and reporting, not for creating or changing ServiceNow records.

I would like to highlight that it is not a direct connection to the database. The client connects through an ODBC or JDBC driver, which is provided by ServiceNow. Web services process the query and enforce instance-side controls.

 

Use cases for Live Connect

Let's look at some common use cases where this product can be helpful:

  • Dashboards and BI reports: Connect systems such as Power BI, Tableau, or other ODBC/JDBC-compatible tools to ServiceNow data.
  • Ad-hoc analysis and investigation: Use queries to look into trends or find details about a problem.
  • Custom reports: Design reports with the reporting tools the organization already uses and set their schedule there.
  • Cross-source analysis: Bring ServiceNow results together with information from other systems inside an analytics tool.
  • Data transformation: Filter and summarize records before sending results to another analysis or transformation step. This can reduce the amount of data that moves across the network.

 

How does this product work?

The following steps describe how an external system can get data from the ServiceNow database:

  1. A BI tool, reporting application, or other SQL client connects using the Live Connect ODBC or JDBC driver.

  2. The client authenticates to the ServiceNow instance.

  3. Live Connect processes the SQL request through its security controls and driver-specific service.

  4. The service runs the allowed query against ServiceNow data and returns tabular results to the client.

AttilaVarga_1-1794585103783.png

 

Security and access

Live Connect does not grant blanket access to all instance data. Access is configured for the connecting account and remains subject to ServiceNow security controls. The following image comes from the ServiceNow documentation and represents the security elements very well:

 

AttilaVarga_0-1794833273221.png

 

 

  • The account needs the driver-specific role: sn_odbc_rest_access for ODBC or sn_jdbc_rest_access for JDBC.
  • For the authentication, Basic Authentication, OAuth 2.0, and client certificates (mTLS) also can be used. Regarding Basic Authentication it can be expected to be removed, so it's better to use the other two ones.
  • Permissions are set per table. Need to create both egress_sql and read ACLs for each table.
  • With the sn_live_connect_privileged_mode role, row and field checks are skipped, but the table access check still applies. This role can only be added to users who has the security_admin role.
  • Depending on the instance policy and network configuration, IP filtering and authentication requirements also apply.

I recommend following the principle of least privilege: use dedicated accounts where appropriate, grant access only to the required tables, and ask an instance or security administrator to validate the policy. Keep in mind that the read-only nature of SQL does not replace ACLs or other access controls.

 

Good to know

I collected some useful information about this application from the official documentation:

Area Details
Entitlement RaptorDB Professional v2 (It can be tested on a PDI)
ServiceNow release Australia Patch 2 or later, or Zurich Patch 8 or later
ODBC driver Version 4.0.0 or later
JDBC driver Version 4.0.0 or later
Java Development Kit JDK 17
Basic Authentication Supported (It is recommended to use OAuth instead.)
OAuth 2.0 Zurich Patch 13 or later, or Australia Patch 6 or later
Query limits 500 queries per hour per driver type (ODBC and JDBC), across all user accounts; 5-minute timeout per query

 

Main configuration in ServiceNow

In the following sections, I'm going to focus on using the JDBC driver.

In order to use this feature, you need to complete some configuration steps in ServiceNow. The official documentation describes all the details of the configuration. I don't want to duplicate that content, so I will just highlight the key elements (illustrated with images) to make this article a complete guide.

 

Plugin installation

There is only one plugin that needs to be installed on the instance. It provides all the artifacts that make the feature available, except for the JDBC (or ODBC) driver. I will cover the driver later in this article.

 

AttilaVarga_0-1794814316404.png

 

Once the plugin is installed, we can check if the REST API endpoints are in place:

 

AttilaVarga_1-1794814900061.png

 

Setup API access

Referring back to the image above, there are two areas where API access needs to be configured. First, we need a technical user who has access to the API endpoints and the corresponding objects, and second, we need to set up the security configurations.

 

Setup technical user

We need to create a user whose identity type is set to Machine. In order to access the REST API endpoint, the sn_jdbc_rest_access role must be assigned to the user.

(As a reminder, roles should not be assigned directly to users. Instead, they should be inherited through group membership.)

AttilaVarga_2-1794815914798.png

 

I will cover some additional user-related configuration steps later in this article.

 

Setup security access

ServiceNow has a built-in security layer related to REST/SOAP API access, called API Access Policy. The Live Connect plugin contains a policy related to the JDBC REST connector, called JDBC Live Connect Access Policy. As a first step, this one has to be configured.

 

AttilaVarga_3-1794816426018.png

 

The policy itself cannot be modified, but the related profiles are configurable:

 

AttilaVarga_4-1794816513592.png

 

The Basic authentication works fine, but I recommend using the OAuth profile.

 

AttilaVarga_5-1794816941663.png

The next level of configuration is the Authentication Policy. Authentication policies evaluate authentication requests based on specified policy conditions and either allow or deny access depending on the matching criteria.

In my configuration, I set up an IP address-based policy. This means that the REST API endpoint can only be used if the request comes from a predefined IP address.

 

If the authentication policy criteria are not met, the following error message is provided by ServiceNow:

 

INFO: connecting: jdbc:servicenow://https://dev123.service-now.com
WARNING: User is not authenticated: Required to provide Auth information

This message can be a bit misleading, but from a security point of view, I understand why ServiceNow doesn't provide the exact error message.

 

So, now the authentication setup is done. But what about authorization? It is not enough to just create a user and set up access to the REST API endpoints. We also need to define which objects the technical user can access and at what level (so ACL-level setup is also required).

 

For example, if we want to get a list of incidents, but the technical user does not have read access to this table, we get the following error message:

INFO: JDBCStatement: OAuth is enabled
INFO: Using cached OAuth access token (valid until 1794823299790)
WARNING: Error: This user (jdbc.api.test) is not allowed access to table: incident

 

The next step is to assign the required roles to the technical user so that they can access the necessary objects.

 

There is an additional tricky part here (again, referring back to the image about security elements). It is not enough to set up read access to the table at the ACL level. There is a special ACL operation, called egress_sql, which also has to be set up at the table level.

 

AttilaVarga_0-1794823013544.png

 

Without this configuration, you receive the same error message (...is not allowed access to table: incident).

Now that all the security setup is done, let's test it using a simple Java application.

 

Execute SQL queries

In order to test running SQL commands, we need JDK 17 (Java Development Kit), an IDE (Integrated Development Environment), and, of course, the JDBC driver. This driver is available on the ServiceNow Store. The official documentation also provides useful information on how to configure it.

 

I used NetBeans as my IDE, which worked well in all use cases.

The application structure is very simple (of course, I had to spend some time figuring out the values of all the parameters 🙂😞

 

package sn_sqlapi_test;

import com.snc.db.jdbc.JDBCDriver;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 *
 * @author Attila Varga
 */
public class SnJdbcApi {

    private static final String DB_URL = "jdbc:servicenow://https://dev123.service-now.com";
    private static final String USER = "jdbc.api.test";
    private static final String PASS = "...";
    private static final String CLIENT_ID = "...";
    private static final String CLIENT_SECRET = "...";

    public static void main(String... args) {
        String query = """
                      TODO SQL HERE
                       """;

        // Authentication properties
        Properties props = new Properties();
        props.setProperty("user", USER);
        props.setProperty("password", PASS);
        props.setProperty("useoauth", "true");
        props.setProperty("oauthclientid", CLIENT_ID);
        props.setProperty("oauthclientsecret", CLIENT_SECRET);

        JDBCDriver driver = new JDBCDriver();
        try (Connection conn = driver.connect(DB_URL, props); 
                Statement stmt = conn.createStatement(); 
                ResultSet rs = stmt.executeQuery(formatSql(query))
            ) {

            while (rs.next()) {
                // TODO process the result set
            }

        } catch (SQLException se) {
            System.err.println(se.getMessage());
        }
    }
    
    /**
     * This function is used for transforming the SQL query into processable format
     * @param rawSql
     * @return 
     */
    public static String formatSql(String rawSql) {
        if (rawSql == null) return "";
        return rawSql
            // Remove single-line comments (-- comment)
            .replaceAll("--.*?\n", " ")
            // Replace newlines and tabs with spaces
            .replaceAll("[\r\n\t]+", " ")
            // Collapse multiple spaces into a single space
            .replaceAll("\\s+", " ")
            .trim();
    }

}

 

Troubleshooting issues

I found a KB article that describes several possible issues and their resolutions. I think this is very useful, so I would like to share it with you. The article is available HERE.

 

Live connect in action

So, the first example gathers all incident numbers and their callers where the state is not Resolved or Closed:

String query = """
                SELECT number, caller_id 
                FROM incident
                WHERE state NOT IN (6, 7) -- 6 = Resolved, 7 = Closed 
               """;

JDBCDriver driver = new JDBCDriver();
try (Connection conn = driver.connect(DB_URL, props); 
        Statement stmt = conn.createStatement(); 
        ResultSet rs = stmt.executeQuery(formatSql(query))
    ) {

    while (rs.next()) {
        System.out.printf("Incident number: %s, Caller: %s\n", rs.getString("number"), rs.getString("caller_id"));
    }

} catch (SQLException se) {
    System.err.println(se.getMessage());
}

 

Result:

INFO: JDBCStatement: OAuth is enabled
INFO: Using cached OAuth access token (valid until 1794825195345)
Incident number: INC0008001, Caller: 005d500b536073005e0addeeff7b12f4
Incident number: INC0010001, Caller: da419c1fc312310015519f2974d3ae15
Incident number: INC0000039, Caller: 46c6f9efa9fe198101ddf5eed9adf6e7
Incident number: INC0000019, Caller: 5137153cc611227c000bbd1bd8cd2005
Incident number: INC0000020, Caller: 5137153cc611227c000bbd1bd8cd2005
Incident number: INC0000025, Caller: 9ee1b13dc6112271007f9d0efdb69cd0
Incident number: INC0000027, Caller: 5137153cc611227c000bbd1bd8cd2005
...
Incident number: INC0001990, Caller: 3883f4c0730123002728660c4cf6a754
Incident number: INC0000048, Caller: 46d96f57a9fe198101947a9620895886
Incident number: INC0000054, Caller: 97000fcc0a0a0a6e0104ca999f619e5b
BUILD SUCCESSFUL (total time: 1 second)

 

In the example above, the caller is just a sys_id, but I need the value of the "User ID" field. The solution is simple: we just need to add a table join. This will be our second example:

 

String query = """
                SELECT inc.number, usr.user_name
                FROM incident inc
                INNER JOIN sys_user usr ON usr.sys_id = inc.caller_id
                WHERE inc.state NOT IN (6, 7) -- 6 = Resolved, 7 = Closed 
               """;

JDBCDriver driver = new JDBCDriver();
try (Connection conn = driver.connect(DB_URL, props); 
        Statement stmt = conn.createStatement(); 
        ResultSet rs = stmt.executeQuery(formatSql(query))
    ) {

    while (rs.next()) {
        System.out.printf("Incident number: %s, Caller: %s\n", rs.getString("number"), rs.getString("user_name"));
    }

} catch (SQLException se) {
    System.err.println(se.getMessage());
}

Result:

INFO: JDBCStatement: OAuth is enabled
Nov 16, 2026 11:18:31 AM com.snc.db.jdbc.oauth.OAuthTokenManager getAccessToken
INFO: Using cached OAuth access token (valid until 1794825195345)
Incident number: INC0008001, Caller: survey.user
Incident number: INC0000015, Caller: fred.luddy
Incident number: INC0000016, Caller: bow.ruggeri
Incident number: INC0000018, Caller: taylor.vreeland
Incident number: INC0000019, Caller: fred.luddy
Incident number: INC0000020, Caller: fred.luddy
Incident number: INC0000025, Caller: don.goodliffe
Incident number: INC0000027, Caller: fred.luddy
Incident number: INC0000029, Caller: charlie.whitherspoon
Incident number: INC0000031, Caller: employee
...
Incident number: INC0000055, Caller: carol.coughlin
Incident number: INC0000003, Caller: employee
BUILD SUCCESSFUL (total time: 2 seconds)

 

The third example shows a ServiceNow-specific SQL function for getting the display value of a reference field instead of its sys_id (the DV() function comes from the official ServiceNow documentation, which I refer to later):

 

String query = """
                SELECT number, DV(caller_id) as caller
                FROM incident 
                WHERE state NOT IN (6, 7) -- 6 = Resolved, 7 = Closed 
               """;

JDBCDriver driver = new JDBCDriver();
try (Connection conn = driver.connect(DB_URL, props); 
        Statement stmt = conn.createStatement(); 
        ResultSet rs = stmt.executeQuery(formatSql(query))
    ) {

    while (rs.next()) {
        System.out.printf("Incident number: %s, Caller: %s\n", rs.getString("number"), rs.getString("caller"));
    }

} catch (SQLException se) {
    System.err.println(se.getMessage());
}

 

Result:

INFO: JDBCStatement: OAuth is enabled
INFO: Using cached OAuth access token (valid until 1794829282032)
Incident number: INC0008001, Caller: survey user
Incident number: INC0010001, Caller: John Jason
Incident number: INC0000058, Caller: Bow Ruggeri
Incident number: INC0009001, Caller: David Miller
Incident number: INC0008111, Caller: System Administrator
...
Incident number: INC0000051, Caller: Joe Employee
Incident number: INC0000052, Caller: Bud Richman
Incident number: INC0000053, Caller: Margaret Grey
BUILD SUCCESSFUL (total time: 1 second)

 

Both a KB article and the official documentation detail the available SQL functions. I would say the list is quite impressive. There are even more options available than inside ServiceNow when using GlideRecord or GlideDBFunction.

 

At this point, I will wrap up this general introduction to the Live Connect application. In the next article, I will share some interesting use cases that show the real value of using Live Connect compared to the other solutions available on the platform.

 

What’s next?

We have now connected a Java application to ServiceNow, queried incidents, and joined tables to get more useful results. But getting a query to work is only the first step.

 

When does Live Connect offer real value? How does it compare with GlideRecord or the Table API, and how does its performance measure up?

 

In Part 2, I will explore these questions using practical examples. I will compare the options for the same tasks and look at the results, so we can see where each approach works best. Live Connect isn’t automatically faster or a replacement for every other option, but it may be a useful choice when you need to bring ServiceNow data into external reporting or analysis tools.

 

The key question is not just whether you can run SQL against ServiceNow, but when it’s the right tool for the job.

 

To ensure a fair performance comparison, I will use the same task and similar conditions for each approach, and share the exact measurements rather than simply assuming one is faster.

 

Stay tuned!