How to add Linked Server in SSMS?

Re: How to add Linked Server in SSMS?

Postby 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
sharchies
 
Posts: 1
Joined: Fri 10 Mar 2017 10:21

Re: How to add Linked Server in SSMS?

Postby 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)"
sjc
 
Posts: 1
Joined: Tue 04 Apr 2017 19:24

Re: How to add Linked Server in SSMS?

Postby 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/database-engine/configure-windows/configure-windows-service-accounts-and-permissions#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.
MaximG
Devart Team
 
Posts: 738
Joined: Mon 06 Jul 2015 11:34

Re: How to add Linked Server in SSMS?

Postby 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
jlgervais
 
Posts: 3
Joined: Fri 17 Mar 2017 14:20

Re: How to add Linked Server in SSMS?

Postby 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/database-engine/configure-windows/configure-windows-service-accounts-and-permissions#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: 738
Joined: Mon 06 Jul 2015 11:34

Re: How to add Linked Server in SSMS?

Postby MaximG » Fri 02 Jun 2017 08:44

Please specify whether you performed the steps described in the documentation: https://www.devart.com/odbc/salesforce/docs/index.html?troubleshooting_ssms.htm
MaximG
Devart Team
 
Posts: 738
Joined: Mon 06 Jul 2015 11:34

Re: How to add Linked Server in SSMS?

Postby 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)
poratips
 
Posts: 3
Joined: Thu 22 Jun 2017 13:44

Re: How to add Linked Server in SSMS?

Postby 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
MaximG
Devart Team
 
Posts: 738
Joined: Mon 06 Jul 2015 11:34

Previous

Return to ODBC Drivers