ODBC Driver for Salesforce: Access Linked Server in SSMS 17

Discussion of open issues, suggestions and bugs regarding usage of ODBC Drivers
Post Reply
fglapu
Posts: 5
Joined: Mon 27 Aug 2018 16:42

ODBC Driver for Salesforce: Access Linked Server in SSMS 17

Post by fglapu » Mon 27 Aug 2018 17:05

Hi. I face a similar problem as described in this forum post.

I installed the ODBC Driver for Salesforce and created a linked server in SSMS using ODBC following the documentation.

Testing to connect to the 64-bit Salesforce ODBC source in SSMS 2012/17.8.1 running SQL Server 2016 64-bit works. When I use the 32-bit ODBC source no connection can be established and the following error is shown:

Code: Select all

TITLE: Microsoft SQL Server Management Studio
------------------------------
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)
However, While I can connt to the linked 64-bit source I cannot access the linked server object in SQL Server Management Studio 17.x (32 bit - AFAIK there is no 64-bit version of SSMS). When I try to expand the Server Object tree view I get the following error:

Code: Select all

Error: "Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "MSDASQL" for linked server
and after reboot

Code: Select all

The OLE DB provider MSDASQL for linked server "Salesforce" reported an error. Access denied. Cannot obtain the required interface"IID_IDBSchemaRowset" from OLE DB provider MSDASQL for linked server "Salesforce" (Microsoft SQL Server, Error: 7399) 
Image

Or when I login to SSMS as SA:

Code: Select all

Cannot create an instance of OLE DB provider "MSDASQL" for linked server "SF". (Microsoft SQL Server, Error: 7302)
Trying to query a linked table using `..` operator fails with the same error message: Cannot create an instance...

Code: Select all

SELECT TOP 1 * from SFDC...[Contact]
I have tried a few solutions but nothing seems to work:
  • MSDASQL provider: disable Allow inprocess [done]
  • Restart SQL Server [done]
  • create procedure sp_tables_info_rowset_64 [done, does not fix the issue]
Regardless of that, I can connect to the Salesforce ODBC source and access the data using a Data Flow Task in SSIS and using a 3rd Party Database Manager (Database5Pro 64bit, an SSMS alternative).

I believe the problem is, the devart driver requires the same bitness (as mentioned in the installation guide, see page 41). But I wonder why SMSS doesn't accept the 32-bit ODBC source which would match the architecture of the application. Can anyone confirm or show me way to use my Salesforce source as a linked server? I am open to alternative solutions. Thank you!

MaximG
Devart Team
Posts: 1138
Joined: Mon 06 Jul 2015 11:34

Re: ODBC Driver for Salesforce: Access Linked Server in SSMS 17

Post by MaximG » Tue 28 Aug 2018 14:12

In case if the used SQL Server process is running as 64-bit, it can only access DSN from the 64-bit version of the ODBC driver. That's why you get the error "The specified DSN contains an architecture mismatch between the Driver and Application". To use the x86 version of the ODBC driver, you need SQL Server of the same bitness, i.e. x86

fglapu
Posts: 5
Joined: Mon 27 Aug 2018 16:42

Re: ODBC Driver for Salesforce: Access Linked Server in SSMS 17

Post by fglapu » Thu 30 Aug 2018 00:04

Hi. Thank you for your answer.
However, this is easier said than done. I cannot simply switch to a different database server - or do I miss here something?

Is there no other way? I am open to 3rd party tools - if they help me to work with both in an SSMS-like way with the ODBC data source and SQL Server (in 64 bit).

MaximG
Devart Team
Posts: 1138
Joined: Mon 06 Jul 2015 11:34

Re: ODBC Driver for Salesforce: Access Linked Server in SSMS 17

Post by MaximG » Fri 31 Aug 2018 06:32

When using any ODBC driver with Microsoft SQL Server Management Studio to create a Linked Server (as well as using absolutely any third-party tool with the same functionality), its bitness will be detected by the bitness of SQL Server itself, as in any case to work in the mode described by you ODBC driver installation is required on the computer with the installed SQL Server.

fglapu
Posts: 5
Joined: Mon 27 Aug 2018 16:42

Re: ODBC Driver for Salesforce: Access Linked Server in SSMS 17

Post by fglapu » Tue 04 Sep 2018 21:58

Essentially, it was a permission problem, an ugly one. I had to fiddle with the Registry and the DCOM configuration.

Here is a DBA StackExchange post that describes the necessary steps to edit DCOM MSDAINITIALIZE security settings and permissions which solved my problem.

MaximG
Devart Team
Posts: 1138
Joined: Mon 06 Jul 2015 11:34

Re: ODBC Driver for Salesforce: Access Linked Server in SSMS 17

Post by MaximG » Thu 06 Sep 2018 08:03

We are glad to see the problem resolved. Please don't hesitate to contact us with questions concerning our driver usage.

Post Reply