I was trying to create a linked server from an on-premises SQL Server (BadMachine1), to an Azure SQL Server.
Code for creating the linked server
EXEC sp_addlinkedserver D365BI_Testing, '', 'SQLNCLI11', @datasrc='blabla.database.windows.net', @catalog = 'DbName'
EXEC sp_addlinkedsrvlogin D365BI_Testing, 'false', null, 'blabla_Admin', 'thepassword'
Created fine, but when testing connection on BadMachine1, this error message:
TITLE: Microsoft SQL Server Management Studio
------------------------------
The test connection to the linked server failed.
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
TCP Provider: The specified network name is no longer available.
Client unable to establish connection because an error was encountered during handshakes before login. Common causes include client attempting to connect to an unsupported version of SQL Server, server too busy to accept new connections or a resource limitation (memory or maximum allowed connections) on the server.
OLE DB provider "SQLNCLI11" for linked server "D365BI_Test2" returned message "Client unable to establish connection due to prelogin failure".
OLE DB provider "SQLNCLI11" for linked server "D365BI_Test2" returned message "Client unable to establish connection". (Microsoft SQL Server, Error: 64)
For help, click: https://learn.microsoft.com/sql/relational-databases/errors-events/mssqlserver-64-database-engine-error
------------------------------
BUTTONS:
OK
------------------------------
OK, tested on two other machines we have (GoodMachine1, GoodMachine2) - all of these are in the same data center. The test connection succeeded on both GoodMachine1 and GoodMachine2, and I could successfully write a query from Goodmachine1 and GoodMachine2's SQL Server, to the Azure SQL Server.
Next test:
Tried logging into each of the 3 machines, and opening up SSMS to try to connect to the Azure SQL Db. Connection worked on GoodMachine1 & GoodMachine2. Failed on BadMachine1, with the following error message.
Cannot connect to blabla.database.windows.net.
------------------------------
ADDITIONAL INFORMATION:
A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: TCP Provider, error: 0 - The specified network name is no longer available.) (Microsoft SQL Server, Error: 64)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=64&LinkId=20476
------------------------------
The specified network name is no longer available
------------------------------
BUTTONS:
OK
------------------------------
I don't have access to check, but I'm told the firewall rules are OK on the Azure side.
There are some characteristics that differ among the 3 machines:
Could the old ODBC driver on BadMachine1 be it? Other ideas? Apologies in advance, I've never tired to setup a linked server before, and don't really know what I'm doing very well.
Turns out our IT was blocking those outbound connections from BadMachine1. Now that they're unblocked, the linked server works fine.