Cannot get Linked Servers to work with MS SQL Server developer edition - Stack Overflow

admin管理员组

文章数量:1320807

This may seem like an overly verbose post but I appreciate being given all the details when asked a question, so I am doing the same thing here:

I have a situation where I'm running SQL Server Dev edition locally and want to use linked servers and synonyms to create views that consume data on other servers in my dev/test environment. I am a developer with a machine set up identically to other dev machines in my group, and they have no trouble doing this. However, for ANY linked server on my box, I get the following error just testing the connection:

Now, NONE of these linked servers have been set up to use MSOLEDBSQL as a provider. I'm explicitly telling it to use SQLNCLI11:

EXEC master.dbo.sp_addlinkedserver @server = N'srvTest', @srvproduct=N'sql_server', @provider=N'SQLNCLI11', @datasrc=N'REDACTED'

And as verified in the linked server's properties window:

(SEE EDIT at end of description. Please don't suggest just clicking 'SQL Server' here... that does not work)

In fact, every linked server on my system, including those that came when my box was built out, specifies SQL server native client as the provider, yet all of them fail with an error mentioning MSOLEDBSQL. So question 1: where the heck is this coming from? It's not a provider I've specified!

What I've tried:

Well, first, the obvious. I went to Microsoft and downloaded what's supposed to be the MSOLEDBSQL provider and installed it.

I still get the failure.

So I double check myself:

OK, I explicitly downloaded MSOLEDBSQL and end up with a provider named MSOLEDBSQL19. WTH??? '19'? OK, maybe I can roll with this, even though the provider I specify when creating the linked server seems to be ignored:

Yields:

(SEE EDIT at end of description. Please don't suggest just clicking 'SQL Server' here... that does not work)

So I think maybe I'm onto something and get excited and test the connection, only to find I've gone out of the frying pan and into the fire:

In all honesty, I think I've gone down a rabbit hole because of SQL's absolute insistence on looking for 'MSOLEDBSQL' even though I've explicitly configured the linked server to use the native SQLNCLI11. I think this last attempt I made could be due to issues with the MSOLEDBSQL19 provider and our overall security, BUT I DON'T THINK I SHOULD HAVE HAD TO GO DOWN THIS ROAD TO BEGIN WITH. SQLNCLI11 is being used everywhere throughout my and I should be able to roll with that, I would think.

Every other developer on my team is using SQLNCLI11 and they have no trouble with linked servers.

Google is no help here. I see some posts saying "oh edit your registry to explicitly name the MSSQLOLEDB provider," but, as mentioned, when I downloaded what should be that provider from Microsoft, I get MSSQLOLEDB19, and, as I've shown, it's a known provider on my system!

I did kind of lie to you... I say we are all set up exactly the same, but some of the devs see version 15 of SQL Server dev edition and I am running version 16:

I suppose this could be an explanation but it would seem odd to me that this kind of bug (if that's what it is) would be out there in V16 and not be found yet.

Last thing and I'll shut up: we do have a development server in our environment that happens to be running V15 of developer edition. I can connect to that server with SSMS and "test connection" to any linked server and it runs just fine.

EDIT EDIT EDIT

Some have asked "why not just click the 'SQL Server' button and be done with it? I get the same error if I do that. Here's what it looks like:

This may seem like an overly verbose post but I appreciate being given all the details when asked a question, so I am doing the same thing here:

I have a situation where I'm running SQL Server Dev edition locally and want to use linked servers and synonyms to create views that consume data on other servers in my dev/test environment. I am a developer with a machine set up identically to other dev machines in my group, and they have no trouble doing this. However, for ANY linked server on my box, I get the following error just testing the connection:

Now, NONE of these linked servers have been set up to use MSOLEDBSQL as a provider. I'm explicitly telling it to use SQLNCLI11:

EXEC master.dbo.sp_addlinkedserver @server = N'srvTest', @srvproduct=N'sql_server', @provider=N'SQLNCLI11', @datasrc=N'REDACTED'

And as verified in the linked server's properties window:

(SEE EDIT at end of description. Please don't suggest just clicking 'SQL Server' here... that does not work)

In fact, every linked server on my system, including those that came when my box was built out, specifies SQL server native client as the provider, yet all of them fail with an error mentioning MSOLEDBSQL. So question 1: where the heck is this coming from? It's not a provider I've specified!

What I've tried:

Well, first, the obvious. I went to Microsoft and downloaded what's supposed to be the MSOLEDBSQL provider and installed it.

I still get the failure.

So I double check myself:

OK, I explicitly downloaded MSOLEDBSQL and end up with a provider named MSOLEDBSQL19. WTH??? '19'? OK, maybe I can roll with this, even though the provider I specify when creating the linked server seems to be ignored:

Yields:

(SEE EDIT at end of description. Please don't suggest just clicking 'SQL Server' here... that does not work)

So I think maybe I'm onto something and get excited and test the connection, only to find I've gone out of the frying pan and into the fire:

In all honesty, I think I've gone down a rabbit hole because of SQL's absolute insistence on looking for 'MSOLEDBSQL' even though I've explicitly configured the linked server to use the native SQLNCLI11. I think this last attempt I made could be due to issues with the MSOLEDBSQL19 provider and our overall security, BUT I DON'T THINK I SHOULD HAVE HAD TO GO DOWN THIS ROAD TO BEGIN WITH. SQLNCLI11 is being used everywhere throughout my and I should be able to roll with that, I would think.

Every other developer on my team is using SQLNCLI11 and they have no trouble with linked servers.

Google is no help here. I see some posts saying "oh edit your registry to explicitly name the MSSQLOLEDB provider," but, as mentioned, when I downloaded what should be that provider from Microsoft, I get MSSQLOLEDB19, and, as I've shown, it's a known provider on my system!

I did kind of lie to you... I say we are all set up exactly the same, but some of the devs see version 15 of SQL Server dev edition and I am running version 16:

I suppose this could be an explanation but it would seem odd to me that this kind of bug (if that's what it is) would be out there in V16 and not be found yet.

Last thing and I'll shut up: we do have a development server in our environment that happens to be running V15 of developer edition. I can connect to that server with SSMS and "test connection" to any linked server and it runs just fine.

EDIT EDIT EDIT

Some have asked "why not just click the 'SQL Server' button and be done with it? I get the same error if I do that. Here's what it looks like:

• sql-server
• linked-server

Share Improve this question Follow edited Jan 20 at 14:21 markaaronky asked Jan 17 at 21:54 markaaronkymarkaaronky 1,3051212 silver badges3333 bronze badges 5

• Why do you need to spec provider? Just choose Sql Server radio button and enter the name. – siggemannen Commented Jan 17 at 21:58
• Your Product version indicates you have a Microsoft SQL Server 2022 RTM Dev edition. Are you sure you have the right provider? I would try to install it from the installation setup if possible. – wp78de Commented Jan 17 at 22:34
• @siggemannen, I've been creating via script not UI. But in the UI way even if I click SQL Server instead of selecting a provider I still get an error dialogue about MSOLEDBSQL not being registered, even though I've never done anything to mention MSOLEDBSQL – markaaronky Commented Jan 20 at 13:55
• @wp78de I'm using the same provider that all my colleagues are using, native client, and it works for them. – markaaronky Commented Jan 20 at 13:56
• original post edited to show what happens when I simply click the 'sql server' button instead of choosing a provider via script – markaaronky Commented Jan 20 at 14:07

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 2

As others have said, just select "SQL Server" when creating the Linked Server.

SQLNCLI11 was deprecated in SQL Server 2022. See MS Docs here

MSOLEDBSQL is the recommended provider for apps connecting to SQL Server however...

Currently, MSOLEDBSQL19 prevents the creation of linked servers without encryption and a trusted certificate (a self-signed certificate is insufficient). If linked servers are required, use the existing supported version of MSOLEDBSQL.

taken from MS Docs here

本文标签： Cannot get Linked Servers to work with MS SQL Server developer editionStack Overflow