Problem connecting with DevartODBCExactTarget driver

[mkeyes]

I'm attempting to test the Marketing Cloud ODBC driver and am having issues creating a linked server. Here are the details:

Windows 10
SSMS 13.0 (2016)
64 bit driver
Connected to local SQLExpress SQL Server

ODBC 64bit data source set up and "Test Connection" returns a success.

I get this error when I create a linked server:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The OLE DB provider "SQLNCLI11" for linked server "MYSFMCINSTANCE" reported an error. Authentication failed.
Cannot initialize the data source object of OLE DB provider "SQLNCLI11" for linked server "MYSFMCINSTANCE". (Microsoft SQL Server, Error: 7399)

Checking / Unchecking Allow Inprocess on MSDASQL does not make a difference.

Any idea what the issue could be?

[AlexP]

Hello,

When creating Linked Server, you chose the wrong provider. From the Provider drop-down list you need to select Microsoft OLE DB Provider for ODBC Driver

[mkeyes]

Thanks for your reply. That helped in that when I right click on the linked server and choose "test connection" it succeeds. However, when I expand the "table" node in the "default" catalog node I get this error:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Failed to retrieve data for this request. (Microsoft.SqlServer.Management.Sdk.Sfc)

For help, click: http://go.microsoft.com/fwlink?ProdName ... nkId=20476

------------------------------
ADDITIONAL INFORMATION:

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

------------------------------

The OLE DB provider "MSDASQL" for linked server "MYSFMCINSTANCE" reported an error. Access denied.
Cannot obtain the required interface ("IID_IDBSchemaRowset") from OLE DB provider "MSDASQL" for linked server "MYSFMCINSTANCE". (Microsoft SQL Server, Error: 7399)

"Allow Inprocess" is unchecked for MSDASQL

[AlexP]

Which authentication are you using in SSMS: Windows or SQL Server?

[mkeyes]

I'm connecting to the SQL Server on localhost using Windows Authentication

[AlexP]

Try to use SQL Server authentication

[mkeyes]

SQL Server Authentication did not work. I was still getting the initial error in SSMS. However, I was able to use a different query analyzer (one called Database.NET) to see the connection. That tool worked immediately and easily with the System ODBC connection. So, problem solved!

[AlexP]

Glad to see that you have found the solution. If you have any other questions, feel free to contact us