Using SQL ODBC on a Progress database without multiple licenses

Marc Broers -

Connecting to a Progress database using the SQL ODBC protocol is easy if you are able to obtain multiple driver licenses. Those licenses can either be bought if you are directly hosting your Progress database or can be provided by your software partner.

But what do you do if you are unable to obtain multiple instances of such a license for all your required connections? Well, you can tunnel your ODBC connection using SQL Server!

One of my clients was using an ERP that uses a Progress database that was hosted on-premise. The client required the data of the ERP to be extracted using multiple ODBC connections for business intelligence purposes, but the software partner which owns the ERP was not willing to provide an additional driver license besides the driver that already became preinstalled with the database instance. So I started looking for other solutions to this problem!

Using Progress as a Linked Server

SQL Server has a feature called "Linked Servers" which can be used to establish a connection with another database server and query it.

We will use this feature to create a "tunnel" to our Progress database which will then be leveraged by our business intelligence software to query the Progress data using (T)-SQL syntax and the ODBC drivers of SQL Server, which are free!

Requirements

  • The Progress database is running on a Windows Server which you full administrator rights for.
  • The SQL Server has to be installed on the same server as the ODBC driver.
  • The default Progress ODBC driver must be available, which comes pre-installed with Progress.
  • Have SQL Server Management Studio installed on a computer. We will use this software to add the Linked Server but you can also do it using T-SQL in the terminal.

How-To

  1. Open the ODBC Administrator utility which you can find within the Windows Administrative Tools folder on your Windows menu.
  2. Open the System DSN tab and write down the name of your Progress DODBC data source.
  3. Open SQL Server Management Studio and connect to your SQL Server.
  4. Open Server Objects folder > Linked Servers. Right-click and select New Linked Server.
  5. Enter a name in the "Linked Server" textbox.
  6. Select "Microsoft OLE DB Provider for ODBC Driver" as the Provider.
  7. Set "Data Source" to the name you had written down. This tells SQL Server to connect to the source using your ODBC driver.
  8. Open the "Security" tab on the left and your login information for your Progress database.
  9. Click "OK" to save the Linked Server.

You now have a working "tunnel" to your Progress database using ODBC which you can query using TSQL OpenQuery.