Connect to Autonomous REST database from SQL Server Linked Server
Introduction
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.
Generating a Model file with the Autonomous REST Composer
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.
Configure Connection to Autonomous REST ODBC Driver Using the ODBC Administrator
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.
6. A dialogue box will appear showing the preview of the data. It also shows the connection status.
Configure the DataDirect ODBC Autonomous REST Driver with Microsoft SQL Server Management Studio.
1. Launch SQL Server Management Studio.
2. Navigate to Server Objects > Linked Servers > Providers > MSDASQL > and confirm that the “Allow inprocess” and “Non transacted updates” options are Enabled.
3. In the object explorer navigate to Server Objects > Linked Servers. Right-click and select New Linked Server.
4. Fill in all the details.
Name your linked server.
Check the Server type as another data source.
Select provider as Microsoft OLE DB Provider for ODBC Drivers from the drop-down menu.
Enter the Product name as DataDirect.
Enter the Data Source name created in the ODBC configuration process.
5. Click on the Security page and select “Be made using this security context”. Then enter your Autonomous REST username and password.
6. Click on the Server Options tab and ensure that they match the following options shown in the following screen capture.
7. That’s it! You’re all done! Now you can find Autonomous REST in the provider's list. Navigate to Autonomous REST > Catalogs > default > Tables. You should see your tables now and be able to query your Autonomous REST data from the linked server
Conclusion
Get started today with a free 15-day trial of Progress DataDirect Autonomous REST ODBC drivers, and connect to Microsoft SQL Server Management Studio.
Contact Us for assistance with any questions you may have, and we will be happy to help!