How to add Linked Server in SSMS?

Discussion of open issues, suggestions and bugs regarding usage of ODBC Drivers
sharchies
Posts: 1
Joined: Fri 10 Mar 2017 10:21

Re: How to add Linked Server in SSMS?

Post by sharchies » Fri 10 Mar 2017 10:47

Same problem as 'raranchado' for me.
I'm testing ODBC driver for Ms CRM with :
- Windows 2016 server
- SQL Server 13.0.1728.2
- CRM Online
- Devart ODBC driver 1.01.03.00
- an Oracle OLE DB Provider and the Microsoft.ACE.OLEDB12.0 provider are also installed

- No problem with the ODBC settings, successfully connected
- The linked server does not work if 'allow inprocess'
- I'm not able to expand the catalog :
Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "MSDASQL" for linked server "CRM 365". The provider supports the interface, but returns a failure code when it is used. (Microsoft SQL Server, Error: 7311)
- querying CRM Online table directly works using a select

sjc
Posts: 1
Joined: Tue 04 Apr 2017 19:24

Re: How to add Linked Server in SSMS?

Post by sjc » Tue 04 Apr 2017 19:31

Is there any solution available for the scenario where the linked server for SALESFORCE Connects but receive the following error when trying to expand the tables list:

"Cannot obtain the schema rowset "DBSCHEMA_TABLES" for OLE DB provider "MSDASQL" for linked server "SALESFORCE". The provider supports the interface, but returns a failure code when it is used. (Microsoft DQL Server, Error: 7311)"

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

Re: How to add Linked Server in SSMS?

Post by MaximG » Wed 31 May 2017 11:02

Try to test the work of Linked Server again by using the following parameters:

- use the credentials of a local user to run MSSQLSERVER service ( https://docs.microsoft.com/en-us/sql/da ... ns#Windows ). After changing the startup parameters, restart the service.
- use SQL Server Authentication when connecting to the server
- disable the "Allow inprocess" option (from Microsoft SQL Server Management Studio, navigate to Linked Servers > Providers and Right click on MSDASQL and click properties)

In our test environment we successfully work with Linked Server using the above parameters.

jlgervais
Posts: 8
Joined: Fri 17 Mar 2017 14:20

Re: How to add Linked Server in SSMS?

Post by jlgervais » Wed 31 May 2017 12:54

- Created a new local admin user and set it up to run MSSQLSERVER (restarted)
- used sa to connect
- Allow Inprocess was disabled
I Still have the same Error (The provider supports the interface, but returns a failure code when it is used. (Microsoft SQL Server, Error: 7311))

Edit : Tested in two environments :
Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) Oct 28 2016 18:17:30 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Standard 6.3 <X64> (Build 14393: ) (Hypervisor)
Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) Oct 28 2016 18:17:30 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows 10 Pro 6.3 <X64> (Build 15063: )


Exact same result on both

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

Re: How to add Linked Server in SSMS?

Post by MaximG » Fri 02 Jun 2017 08:24

Try to test the work of Linked Server again by using the following parameters:

- use the credentials of a local user to run MSSQLSERVER service ( https://docs.microsoft.com/en-us/sql/da ... ns#Windows). After changing the startup parameters, restart the service.
- use SQL Server Authentication when connecting to the server
- disable the "Allow inprocess" option (from Microsoft SQL Server Management Studio, navigate to Linked Servers > Providers and Right click on MSDASQL and click properties)

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

Re: How to add Linked Server in SSMS?

Post by MaximG » Fri 02 Jun 2017 08:44

Please specify whether you performed the steps described in the documentation: https://www.devart.com/odbc/salesforce/ ... g_ssms.htm

poratips
Posts: 3
Joined: Thu 22 Jun 2017 13:44

Re: How to add Linked Server in SSMS?

Post by poratips » Wed 28 Jun 2017 15:21

I am also getting same error even though i tried to apply all above solutions.
Any Help will be appreciated.
I am running Sql server 2012
Error: Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server

"SALESFORCE SQL".
OLE DB provider "MSDASQL" for linked server "SALESFORCE SQL" returned message "[Devart][ODBC]

[Salesforce]"Devart.ODBC.SalesforceEngine.dll" can't be loaded". (Microsoft SQL Server, Error:

7303)

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

Re: How to add Linked Server in SSMS?

Post by MaximG » Wed 05 Jul 2017 11:11

We tested the work of ODBC Driver for Salesforce in SQL Server Management Studio with SQL Server 2012 and found no problems
Please specify :
a) What account does run the 'SQL Server' service (LOCAL SERVICE, NETWORK_SERVICE, etc.) ?
b) What authentication type are you using: SQL Server : Windows Authentication or SQL Server Authentication?
c) What privileges has the Windows user that works with SQL Server Management Studio ?
d) Is the "Allow inprocess" option enabled or disabled (from Microsoft SQL Server Management Studio, navigate to Linked Servers > Providers and Right click on MSDASQL and click properties)
e) The exact version of our driver
Make sure that the used driver bitness matches Server Management Studio bitness

Post Reply