ODBC Driver for Salesforce and SSMS Linked Server - Error

Discussion of open issues, suggestions and bugs regarding usage of ODBC Drivers
Post Reply
gmac33
Posts: 3
Joined: Fri 02 Oct 2020 21:48

ODBC Driver for Salesforce and SSMS Linked Server - Error

Post by gmac33 » Fri 02 Oct 2020 21:54

I am getting the following error when querying a linked server in SSMS:

OLE DB provider "MSOLEDBSQL" for linked server "SALESFORCE" returned message "Login timeout expired".
OLE DB provider "MSOLEDBSQL" for linked server "SALESFORCE" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".
Msg 53, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [53].

Don't think this is actually a timeout issue. Timeout set to 0 in both the ODBC DSN and in the Linked Server properties.

The query is:

SELECT * FROM [SALESFORCE]…[Account]

I can use the same DSN in MS Access to create a linked table and it works fine.

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

Re: ODBC Driver for Salesforce and SSMS Linked Server - Error

Post by MaximG » Mon 05 Oct 2020 15:30

Which version of Microsoft SQL Server Management Studio are you using? Are you able to retrieve data from any other Salesforce table?

gmac33
Posts: 3
Joined: Fri 02 Oct 2020 21:48

Re: ODBC Driver for Salesforce and SSMS Linked Server - Error

Post by gmac33 » Mon 05 Oct 2020 18:02

V18.6

Rechecked linked server properties and realized it did not save properly. Deleted and re-added, now getting 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".

Get the same error on other tables.

Assume the Datasource property is the ODBC DSN; documentation is not clear.

gmac33
Posts: 3
Joined: Fri 02 Oct 2020 21:48

Re: ODBC Driver for Salesforce and SSMS Linked Server - Error

Post by gmac33 » Mon 05 Oct 2020 23:42

I did finally get this to work following the instructions in this post:

https://dba.stackexchange.com/questions ... 726#216726

However they key to this is to set up the permissions/security for the correct account. Since I set up the Linked Server in SSMS in local SQL Express instance, the account had to be the account that the SQL Express service is running under, which is a local machine account that was created during the installation.

Some of what is covered in the linked article is in the troubleshooting section of the Devart pdf document, but not all.

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

Re: ODBC Driver for Salesforce and SSMS Linked Server - Error

Post by MaximG » Thu 08 Oct 2020 10:13

Glad to hear the issue has been resolved! Complexity in the configuration of a Linked Server is caused by the specifics of Microsoft SQL Server Management Studio. We're constantly working to update and improve our software solutions and will cover all possible resolutions in the driver's documentation.

Post Reply