Connect to Autonomous REST database from SQL Server Linked Server

Progress DataDirect Autonomous REST Connector delivers seamless, real-time connectivity between REST data and your ODBC tools and applications. Autonomous REST Connector rapidly decreases the amount of time required for applications to understand and consume APIs through its codeless configuration and intelligent sampling. 

With intelligent sampling, Autonomous REST Connector can point to an API and automatically create a configuration file that allows you to start working with the data immediately — no code, no hassle. The configuration file can also be customized to determine how to navigate the various APIs you need to leverage. 

In addition, Autonomous REST Connector supports a wide variety of authentication methods and normalizes the API’s response schema to a SQL-friendly format.  It’s always been easy to create a connector using an Autonomous REST Connector, but we have simplified this even further with the Autonomous REST Connector UI.  The new UI helps you create connectors faster. You don’t need to manually create or edit configurations or worry about the correctness of syntax to configure your API. Just add your configurations in the UI and the UI will take care of the rest. Simply download the configuration file. 

In this blog, we will take you through the steps on how to connect REST API from SQL Server Linked Server, SQL Management Studio using Autonomous REST connector. To connect SQL Management Studio with a REST API using Autonomous REST connector ODBC, you will need to first set up an ODBC data source that points to the API you want to connect to. In this tutorial, we will generate a model and establish a connection with it.  

The Autonomous REST Composer allows you to generate and edit Model files, instead of manually creating a file. The primary purpose of the Model file is to define endpoints and table mapping, but it is also capable of configuring several driver behaviors, such as paging and pushdowns. After generating the Model file, you can share it among multiple installations of the driver. See "Using the Model file method" for an overview of the Model file. 

To generate your model file: 

1. Downloadand install the Progress DataDirect Autonomous REST ODBC driver using the 15-day trial software.  

2. After installation a shortcut file is created on the desktop. Open the Autonomous REST Composer by selecting the Autonomous REST Composer (ODBC) icon from your desktop or the Windows Start menu. The Autonomous REST Composer opens in your default web browser. 

3. You can create a Model, or you can use the model files. If you want to create a new model go through the detailed documentation on Generating a Model file with the Autonomous REST Composer. In this tutorial, we are using the SpaceX model. Click on the model, you will be redirected to the SpaceX composer. 

4. Go to the “Configure Endpoints” option present on the left side and download it. All the endpoints will be downloaded.  

1. After the DataDirect Autonomous REST ODBC driver has been installed, open ODBC Administrator to configure the connection and click on “Add”.  

2. Choose the “DataDirect X.X Autonomous REST Connector” and click on “Finish.”  

3. The ODBC Autonomous REST configuration manager will open in the default browser. 

Enter Data Source Name (DSN), and then you need to enter the REST config file. 

4. To get the REST config file, go to downloads and copy the path of the model file downloaded before. Click on properties > security and copy the object name. 

5. Paste the copied path in the REST config file, click on save, and then test connect.