ODBC Driver for Salesforce: Access Linked Server Object in SSMS

Question

I have problems to access a linked server for a Salesforce ODBC data source with our MS SQL Server 2016 (Standard 64-Bit) using the Devart ODBC Driver for Salesforce in SSMS and SQL Studio Management Studio (17.8.1).

I have created a linked server in SSMS as described in the documentation.

Testing the connection to the ODBC source only works in SSMS when I use the 64-bit source. When I use the 32-bit ODBC source no connection can be established and the following error is shown:

The linked server has been created but failed a connection test. Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "SF". OLE DB provider "MSDASQL" for linked server "SF" returned message "[Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application". (Microsoft SQL Server, Error: 7303)

Whilst I can connect to the 64-bit source I cannot access the linked server object in SQL Server Management Studio 17.x (32 bit - I believe there is no 64-bit version of SSMS):

When I try to expand the Server Object tree view I get the following error:

Select all Error: "Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "MSDASQL" for linked server

Or when I try to query something, e.g. SELECT TOP 1 * from SFDC...[Contact]

Cannot create an instance of OLE DB provider "MSDASQL" for linked server "SF". (Microsoft SQL Server, Error: 7302)

I have tried a few solutions that are associated with error 7302/3 but nothing seems to work:

• MSDASQL provider: disable Allow inprocess [this was required]
• Restart (the database engine and even Windows)
• SQL Server create procedure sp_tables_info_rowset_64 [does not fix the issue]

I found out I can indeed connect to the Salesforce ODBC source and access the data using a 3rd Party Database Manager (Database5Pro 64bit, an SSMS alternative) - but this does not allow me to access both data sources at the same time (no linked objects available).

The Devart driver requires (as mentioned in the installation guide, see page 41) the same bitness of all moving parts.

The driver, studio, and SQL Server must be of the same bitness.

Can anyone suggest a way to use a Salesforce source as a linked server along with SQL Server 2016 in an SSMS-like way? I am open to alternative solutions and 3rd-party tools.

I have also tried an older version of SSMS (2012) and the beta version of SQL Operations Studio (basically VS Code like SSMS, actually a 64-bit application) to no avail.

Answer 1

As guessed by Mark this was a permission problem, however a not so obvious one. I had to fiddle with the Registry and the DCOM configuration.

What I did: Edit DCOM MSDAINITIALIZE security settings and permissions

This MSDN post showed me the way to solve the issue: Permissions needed to set up linked server with out-of-process provider

There are certain permissions that have to be set on MSDAINITIALIZE to be able to initialize the provider out-of-process and run linked server queries successfully locally and remotely. (Highlighting added by me)

There is clearly a permission problem case when you get: Msg 7399, ... Access denied. but this may also be the root cause for Msg 7301 and 7302 if the underlying error is 0x80070005.

0x80070005 - Essentially implies Access denied.

Another preparational step... see this MSDN Blog post for reference: What is the RPC and RPC Out option on a SQL Server linked-server?

To be able to execute linked server queries, also set RPC OUT to true on the linked server properties (SSMS).

Permissions needed to set up linked server with out-of-process provider:

Verify below settings in DCOMCNFG: Start -> Run –> Dcomcnfg

1. Component services ->My Computer -> Properties verify that below options are set:

   In the 'Default Properties' tab:

   • 'Enable Distributed COM on this computer' is checked.
   • Default Authentication = Connect.
   • Default Impersonation Level = Identify or Impersonate.

2. Component services -> My computer -> DCOM Config -> MSDAINITIALIZE

   • Right click on MSDAINITIALIZE -> Properties ->Security
   • Add the SQL Server service account (if connected to SQL server using SQL login) or windows user account under "Launch and Activation Permissions", "Access permissions" and "Configuration Permissions".
   • Give full rights to these accounts.
   • Restart the server (was not required in my case)

And the final step:

3. Go to dcomcnfg > My computer > Properties > COM Security > Edit Defaults for Access Permissions & Launch and Activation Permissions, and add the SQL Proxy account (or specific users groups...).

This preparational step might be required if you cannot edit the DCOM settings of MSDAINITIALIZE

1. Take ownership of HKEY_CLASSES_ROOT\AppID{2206CDB0-19C1-11D1-89E0-00C04FD7A829} MSDN Blog: Troubleshooting “Cannot create an instance of OLE DB provider”,
   compare with this instructions for Oracle:
   • Run (CTRL + R) dcomcnfg. Navigate to "Component Services -> Computers -> My Computer -> DCOM Config".
   • Open the properties page of MSDAINITIALIZE. Copy the Application ID on the properties page. Close.
   • Run regedit. Navigate to HKEY_CLASSES_ROOT\AppID{???} using the “Application ID obtained above.
   • Right Click -> Permissions -> Advanced -> Owner Tab: Change the owner to Administrators. In my case, the administrator was already the owner.
   • Back to Permissions: Add the local administrator's group, the service account or user that is supposed to run the client, grant them full control.

Just for completeness...

• In an MSDN forum post creffner suggested to run SSMS as Admin. This did not solve my problem. But that always something to try.
• Restart SQL Server (Engine) [this was actually required]

Answer 2

I'm going to take a chance and suggest an answer but in this case without the specific product and setup, of course I'm unable to verify. I believe what you will need to do is go into the Security settings of the Linked Server and specify a particular account to use to make the connection. That is, create an account on the server you want to connect to, like SalesForceReader, with the corresponding privileges needed, and use that account and password in your Linked Server.