ODBC for Dynamics tests but won't connect in SSMS
Posted: 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:
If I use the connection string, that works, but when I do a select statement it takes a few seconds and comes back with:
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?
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)
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".
Any thoughts?