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).
Go to the Linked Servers tab, find the MSDASQL provider and double-click on it.
In the opened Provider Options window, uncheck Allow inprocess.
Right-click on the Linked Servers tab and click 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.
To view the required table data retrieved directly from Salesforce, execute a corresponding SELECT query.
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.
The linked server is created but when I try tu run a query i’m getting the following error:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider “MSDASQL” for linked server “SALESFORCE” reported an error. Access denied.
Msg 7301, Level 16, State 2, Line 1
Cannot obtain the required interface (“IID_IDBCreateCommand”) from OLE DB provider “MSDASQL” for linked server “SALESFORCE”
Hello, Sebastian!
When creating Linked Server, use SQL Server authentication and make sure that the “Allow inprocess” option is disabled. For this, in SSMS, go to Linked Servers -> Providers and double-click MSDASQL. In the opened window, uncheck the Allow inprocess option. In addition, read more about the issues that may occur when using Microsoft SQL Server Management Studio at devart.com/odbc/salesforce/docs/index.html?troubleshooting_ssms.htm
And furthermore, try running NT SERVICE\MSSQLSERVER under your Windows user name.