Creating Linked Server in SSMS using ODBC

SQL Server Management Studio is an ideal environment for managing infrastructure of SQL Server database and SQL Server itself. With SSMS, you can easily connect to your SQL Server instance, configure and monitor database objects, as well as execute queries. Besides this, there is a possibility to create an ODBC connection to external data from SSMS.

Thus, you can work with other databases and clouds used by your applications.

In this article, we will describe a step-by-step procedure of establishing a connection to Salesforce from SSMS using our ODBC Driver for Salesforce. So, let’s start.

Prerequisites

To avoid any issues when using our ODBC driver with SSMS, you should consider the following requirements:

  • Make sure that the driver, SSMS and SQL Server are of the same bitness.
  • The data source should represent a configured system DSN. Read more about its configuration here.
  • ODBC Driver for Salesforce and SQL Server must be installed on the same computer.
  • .NET Framework 4.5 must be installed on the computer.

To learn more about solving issues that may be encountered while creating a linked server, refer to the Troubleshooting in SSMS article.

Creating a linked server

There are several ways to create a linked server in SSMS. We will show the most common one – using Object Explorer.

Once the driver is installed and configured, open SSMS and connect to your SQL Server instance using the proper authentication (Windows or SQL Server one).

Connecting to SQL Server

Go to the Linked Servers tab, find the MSDASQL provider and double-click on it.

Disabling the Allow inprocess option

In the opened Provider Options window, uncheck Allow inprocess.

Disabling the Allow inprocess option

Right-click on the Linked Servers tab and click New Linked Server.

Creating a new linked server

In the opened window, fill the corresponding fields to connect to Salesforce with our ODBC driver. Make sure that Microsoft OLE DB Provider for ODBC Drivers is selected in the Provider dropdown.

Creating a new linked server

To view the required table data retrieved directly from Salesforce, execute a corresponding SELECT query.

Retrieving data from Salesforce

Conclusion

This article covers all the stages of data retrieval process from Salesforce to SSMS using our ODBC driver: specific requirements, creating a linked server and extracting the required data. As you can see, Devart ODBC drivers can provide you with a fast and reliable access to your data whether in the cloud or on the server.

Leave a Comment