creating linked server in sql server 2008R2 from on-prem to Azure

Question

creating linked server in sql server 2008R2 from on-prem to Azure

Newtojob 66

Hi,

I am trying to create a linked server in sql server 2008R2 from on-prem to Azure but it is giving me an error. when i try to create in sql server 2012 it is working.

Please suggest.

Thank you for the help

Olaf Helper 47,436 Reputation points

2023-09-27T04:54:56.3033333+00:00

but it is giving me an error

And which exact & fully error message do you get; we can't guess that?

Note: SQL Server 2008 R2 is out-of-support since a very long time.
ZoeHui-MSFT 41,491 Reputation points

2023-09-27T08:04:23.53+00:00

Hi @Newtojob,

Is there any error message?
Newtojob 66 Reputation points

2023-09-27T13:17:29.1233333+00:00

Hi,

Sorry, missed the error message.

error message is :

failed to retrieve data for this request,

additional information:

An exception occurred while execution a sql statement or batch.

servaer name cannot be determined, it appears as the first segment of the server's dns name(servername.database.usgovclodapi.net).some libraries do not send the servername, in which case the server name must be included as part of the username (username@servername).

in addition if both formats are used, the server name mst match (MS error : 40531)

i have tried above option, still it is not working.

Thank you
Newtojob 66 Reputation points

2023-09-27T13:30:31.37+00:00
Newtojob 66 Reputation points

2023-09-27T13:31:02.4866667+00:00

Received above error message also

Thank you

1 answer

Your answer

Olaf Helper 47,436 Reputation points

2023-09-27T04:54:56.3033333+00:00

but it is giving me an error

And which exact & fully error message do you get; we can't guess that?

Note: SQL Server 2008 R2 is out-of-support since a very long time.
ZoeHui-MSFT 41,491 Reputation points

2023-09-27T08:04:23.53+00:00

Hi @Newtojob,

Is there any error message?
Newtojob 66 Reputation points

2023-09-27T13:17:29.1233333+00:00

Hi,

Sorry, missed the error message.

error message is :

failed to retrieve data for this request,

additional information:

An exception occurred while execution a sql statement or batch.

servaer name cannot be determined, it appears as the first segment of the server's dns name(servername.database.usgovclodapi.net).some libraries do not send the servername, in which case the server name must be included as part of the username (username@servername).

in addition if both formats are used, the server name mst match (MS error : 40531)

i have tried above option, still it is not working.

Thank you
Newtojob 66 Reputation points

2023-09-27T13:31:02.4866667+00:00

Received above error message also

Thank you

Answer 1

Erland Sommarskog 121.4K MVP Volunteer Moderator

It seems like an uphill battle to do it the right way from SQL Server 2008, that is using an SQL Server OLE DB provider. I got the same error message as you. I also tried SQLNCLI11, which took me a little longer, but, no, it did not work.

Mysteriously enough, I got it working with MSDASQL+ODBC:

EXEC sp_addlinkedserver AzureSQLDB, '', 'MSDASQL', @provstr = 'Driver={SQL Server};Server=xxxxxx.database.windows.net;Database=xxxxx'
EXEC sp_addlinkedsrvlogin AzureSQLDB, 'false', NULL, 'xxxxxx', 'cccccccc'

But you should install a new ODBC Driver; "SQL Server" is a 20 year old driver which lacks support for many newer functions.

Newtojob 66 Reputation points

2023-09-28T01:46:59.8766667+00:00

Thank you Erland, I am able to create the linked server now with the above queries but data is not coming.

Receiving attached error message.

Thank you
Erland Sommarskog 121.4K Reputation points MVP Volunteer Moderator

2023-09-28T20:53:14.48+00:00

I don't see any error message?

I ran a very quick test with my linked server, and I did get data back. But that does not mean that it will always work.

Share via

creating linked server in sql server 2008R2 from on-prem to Azure

1 answer

Your answer