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.
Best Answer
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.