Linked Server to Azure SQL Database

You may (or may not) have a requirement to setup a linked server to Azure SQL Database from a locally installed SQL Server. One reason could be to pull down some reports from an Azure SQL Database to a local file share. Whatever your reason is hopefully you will find this blog post useful because I ran into some complications on the way.

This is what your linked server creation screens in SSMS (SQL Server Management Studio) should look like.

connectionscreen

connection2

This connection uses a security context that is a login which is read only for the SQL Database.

linked

Once created you can then right click on the linked server and test the connection, you will want to see a success message, obviously.

oktest

The code for all this?


USE [master]
GO

/****** Object:  LinkedServer [TO AZURE]    Script Date: 20/11/2017 09:44:06 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'TO AZURE', @srvproduct=N'', @provider=N'SQLNCLI11', @datasrc=N'cloudsql.database.windows.net', @catalog=N'TestDB'
 /* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'TO AZURE',@useself=N'False',@locallogin=NULL,@rmtuser=N'ArunSirpal',@rmtpassword='########'
GO

EXEC master.dbo.sp_serveroption @server=N'TO AZURE', @optname=N'collation compatible', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'TO AZURE', @optname=N'data access', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'TO AZURE', @optname=N'dist', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'TO AZURE', @optname=N'pub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'TO AZURE', @optname=N'rpc', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'TO AZURE', @optname=N'rpc out', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'TO AZURE', @optname=N'sub', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'TO AZURE', @optname=N'connect timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'TO AZURE', @optname=N'collation name', @optvalue=null
GO

EXEC master.dbo.sp_serveroption @server=N'TO AZURE', @optname=N'lazy schema validation', @optvalue=N'false'
GO

EXEC master.dbo.sp_serveroption @server=N'TO AZURE', @optname=N'query timeout', @optvalue=N'0'
GO

EXEC master.dbo.sp_serveroption @server=N'TO AZURE', @optname=N'use remote collation', @optvalue=N'true'
GO

EXEC master.dbo.sp_serveroption @server=N'TO AZURE', @optname=N'remote proc transaction promotion', @optvalue=N'true'
GO

 

Lessons to learn

Couple of tweaks were needed to get this setup.

Learning lesson 1 – make sure you have the correct firewall rule in place. If not, it will fail. Just to prove a point I logon to the Azure Portal and find the server level firewall rules and REMOVE my allowed IP address and save the settings.

Removedfirewall

I now get this when trying to re-create the linked server.

errorfirewall

To add the rule on your Azure SQL Database, go to Firewall settings (under settings).

vnes

Add the IP address save the configuration.

addip

Re-checking SSMS you will now have access to the Azure SQL Database.

Learning lesson 2 – You need to make a further change to allow for stored procedure execution, else you will get a similar error to this:

Msg 7411, Level 16, State 1, Line 4 Server ‘TO AZURE’ is not configured for RPC.

rpccall

You will need to set RPC Out = True.

Voilà – my remote query.

toazure

 

2 thoughts on “Linked Server to Azure SQL Database

  1. Pingback: Dew Drop - June 6, 2018 (#2740) - Morning Dew

  2. Pingback: Connecting To Azure SQL Database From On-Prem – Curated SQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s