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.
Error creating linked server in SSMS
Re: Error creating linked server in SSMS
Please, use SQL Server Authentication instead of Windows Authentication and try running NT SERVICE\MSSQLSERVER under your Windows user name
Re: Error creating linked server in SSMS
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.
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.
Re: Error creating linked server in SSMS
We are glad that you could find a necessary solution. Contact us on any questions about our driver use.