ODBC for Dynamics tests but won't connect in SSMS

Discussion of open issues, suggestions and bugs regarding usage of ODBC Drivers
Post Reply
MarkLeavesley
Posts: 6
Joined: Fri 17 Jun 2016 12:57

ODBC for Dynamics tests but won't connect in SSMS

Post by MarkLeavesley » Thu 17 Sep 2020 06:11

I'm trying out the ODBC connector for Dynamics and after several hours I still can't get it to work.

I've setup the ODBC connection, tested it, it's fine. Created the linked server as described (though it's unclear what the product name should be) and I get a security error popup:

Code: Select all

The OLE DB provider "SQLNCLI11" for linked server "Z" reported an error. Authentication failed.
Cannot initialize the data source object of OLE DB provider "SQLNCLI11" for linked server "Z".
OLE DB provider "SQLNCLI11" for linked server "Z" returned message "Invalid authorization specification". (Microsoft SQL Server, Error: 7399)
If I use the connection string, that works, but when I do a select statement it takes a few seconds and comes back with:

Code: Select all

Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSDASQL" for linked server "x" 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 "x".
Ultimately what I want is to be able to insert the contents of every Dynamics table into it's SQL equivalent. I can connect to Dynamics directly through SSMS but I have yet to find a way to do cross server SQL other than linked servers.

Any thoughts?

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

Re: ODBC for Dynamics tests but won't connect in SSMS

Post by MaximG » Mon 21 Sep 2020 06:13

To solve the described issue, please perform the following steps :

- use the recommendations in our documentation :

https://www.devart.com/odbc/dynamics/do ... ager_s.htm
https://www.devart.com/odbc/dynamics/do ... g_ssms.htm

- use SQL Server Authentication instead of Windows Authentication

MarkLeavesley
Posts: 6
Joined: Fri 17 Jun 2016 12:57

Re: ODBC for Dynamics tests but won't connect in SSMS

Post by MarkLeavesley » Tue 29 Sep 2020 06:08

After installing, and uninstalling a competitor's product this magically started working. Case closed.

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

Re: ODBC for Dynamics tests but won't connect in SSMS

Post by MaximG » Thu 01 Oct 2020 13:34

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

Post Reply