Mitch Wheat - Treat the cause, not the symptoms!

* SQL Server 2008 onwards

The use of SQL Server’s sp_addlinkedserver can be a little arcane. I recently needed to connect from an on-premise SQL Server to SQL Azure for the purpose of querying and downloading data to a reporting server, and there a couple of things to note.

One is that you should ensure that data is encrypted on the wire (* and also when connecting to remote servers from SSMS).

 

The other is that you have to may specify the server name as a DNS name (depending on the client connection library you are using). This is the case if you receive this error message:

“Server name cannot be determined.  It must appear as the first segment of the server's dns name (servername.database.windows.net).  Some libraries do not send the server name, in which case the server name must be included as part of the user name (username@servername).  In addition, if both formats are used, the server names must match. “

Here’s the working TSQL script I used:

 

-- If linked server already exists, drop it and any associated logins.

begin try

    exec sp_dropserver 'LocalLinkedServername', 'droplogins'

end try

begin catch

end catch

 

-- Create the linked server: 

EXEC sp_addlinkedserver

    @server     = 'LocalLinkedServername',

    @srvproduct = N'Any',

    @provider   = 'SQLNCLI',

    @datasrc    = '??????????.database.windows.net', -- Azure server name

    @location   = '',

    @provstr    = N'Encrypt=yes;',       -- * Important!

    @catalog    = 'RemoteDatabaseName';  -- remote(Azure) database name

go

 

-- Create the login credentials for the linked server 

EXEC sp_addlinkedsrvlogin

    @rmtsrvname  = 'LocalLinkedServername',

    @useself     = 'false',

    @rmtuser     = 'remotesqlusername@??????????.database.windows.net',

    @rmtpassword = 'remote password';

go

 

 

EXEC sp_serveroption 'LocalLinkedServername', 'rpc out', 'true';

go

 

-- Don’t elevate to distributed transactions

EXEC sp_serveroption 'LocalLinkedServername', 'remote proc transaction promotion', 'false';

go

 

---- Finally, check you can access remote data via your linked server:

select top 100 *

from [LocalLinkedServerName].[RemoteDatabaseName].[RemoteSchemaName].[RemoteTableName];

go

 

 

 

Another issue you might run into is if you have SQL Database auditing turned on in Azure, Azure wants all connections to be from a secure connection string, and if you run the above script from certain versions of SSMS (I believe SQL Server 2012 and below but not verified) then you might get an error saying only connections with secure connection string are allowed (despite a secure connection being specified). The fix is easy, change

myServerName.database.windows.net

to

myServerName.database.secure.windows.net

Keep in mind that prior to SQL Server 2012 SP1, remote server statistics won’t be able to be used to determine query plans unless admin credentials are used (not a good idea!). Prior to SQL Server 2012 SP1 there is a workaround shown here: SQL Server Linked Servers and Remote Statistics

 

Refs.:

TOP 3 PERFORMANCE KILLERS FOR LINKED SERVER QUERIES