Error creating linked server in SSMS

Discussion of open issues, suggestions and bugs regarding usage of ODBC Drivers
Post Reply
vmartin
Posts: 2
Joined: Thu 24 May 2018 16:16

Error creating linked server in SSMS

Post by vmartin » Thu 24 May 2018 16:25

Windows 10, SSMS 2014, connected to an instance of SS 2017 Express. I configured the data source in Administrative Tools -> ODBC Data Sources (64-bit) and the test connection there succeeds.

I've unchecked 'Allow inprocess' for MSDASQL.

If I connect to the SS 2017 Express instance using Windows auth, I am able to create the linked server. But receive the following error when I try to expand the Tables node in Object Explorer.

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

If I use SQL Server auth with a user set up with all permissions on the server, I get the following error when I try to create the linked server.

Cannot create an instance of OLE DB provider "MSDASQL" for linked server "BIGCOMMERCE". (Microsoft SQL Server, Error: 7302)

I've seen the advice to use the NT SERVICE account to log in as a SQL Server user, but I don't know the password for that account and cannot reset the password for a service account.

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

Re: Error creating linked server in SSMS

Post by MaximG » Fri 25 May 2018 14:44

Please, use SQL Server Authentication instead of Windows Authentication and try running NT SERVICE\MSSQLSERVER under your Windows user name

vmartin
Posts: 2
Joined: Thu 24 May 2018 16:16

Re: Error creating linked server in SSMS

Post by vmartin » Fri 25 May 2018 15:29

This worked for me. I want to restate the solution though, since it was confusing to me.

I opened Windows Services and changed the log on account of the SQL Server service "SQL Server (instance name)" from NT Service to my own Windows user account. I then restarted the SQL Server service.

I was then able to log onto the SQL Server instance in SSMS using SQL Server authentication and create the linked server per the instructions in the devart ODBC documentation.

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

Re: Error creating linked server in SSMS

Post by MaximG » Thu 31 May 2018 13:46

We are glad that you could find a necessary solution. Contact us on any questions about our driver use.

Post Reply