Access Azure SQL Database from On-premises SQL Server instance

This article explores OPENROWSET, OPENDATASOURCE and Linked Server to connect Azure SQL Database from on-premises SQL Server.

Introduction

Usually, Database professionals configure Linked Servers to access data from another SQL Server or data source using the OLE DB or ODBC connections. These enable users to implement distributed databases. It helps to read or execute queries using the remote data source. You do not need to customize the application code to load data from another data source. It can access SQL Server, Oracle, Microsoft Excel, Access, MySQL etc., from the SQL Server instance.

Most organizations use a hybrid infrastructure that is a blend of on-premises and cloud infrastructure. Suppose you have an Azure SQL DB and you want to retrieve data from an on-premises SQL Server to Azure SQL DB. The configuration of a linked server for Azure SQL DB requires a few additional steps. Let’s explore it in this article.

Environment details

For this article, I have configured the following Azure DB environment.

Azure Server: azuredemosqldemo.database.windows.net
Azure SQL Database: Azuredemodatabase

In an ad-hoc requirement, SQL Server gives OPENROWSET to access the remote data from the OLE DB data source. In the OPENROWSET, specify the following parameters values.

Server: Azure server name
Database: Azure SQL DB name
UID and Pwd: Credentials for connecting to Azure SQL DB
Query: T-SQL statement

SELECT * FROM OPENROWSET('SQLNCLI',

'Server=azuredemosqldemo.database.windows.net;

Database=Azuredemodatabase;UID=sqladmin;PWD=India@123;',

'SELECT * FROM SalesLT.Product')

As shown below, the query retrieves the result from the Azure database without a linked server.

You can get the following error message while executing the OPENROWSET queries for your SQL Server instance.

To resolve the error, enable the ad hoc distributed query for the on-premise SQL Server using the following query.

EXEC sp_configure 'show advanced options', 1

RECONFIGURE

EXEC sp_configure 'ad hoc distributed queries', 1

RECONFIGURE

Note: You can refer to Microsoft docs for more details on OPENROWSET

Similarly, the OPENDATASOURCE uses a four-part object name without a linked server name. It uses ODBC drivers for connecting to Azure SQL Server. The MSDBSQL enables OLE DB applications to use the ODBC data source.

In the following query, we specify the Azure SQL DB details using the following ODBC connection details for the [Server].[database].[schema].[object] format. It is similar to query a remote table using a linked server; instead, we specify connection details for the ad-hoc connection.

Server name: ‘Driver={SQL SERVER};Server=azuredemosqldemo.database.windows.net;Database=Azuredemodatabase;UID=sqladmin;PWD=India@123
Azure SQL Database: Azuredemodatabase
Schema: SalesLT
Object: Product

1	SELECT * FROM OPENDATASOURCE('MSDASQL', 'Driver={SQL SERVER};Server=azuredemosqldemo.database.windows.net;Database=Azuredemodatabase;UID=sqladmin;PWD=India@123).Azuredemodatabase.SalesLT.Product

Similarly, the following query retrieves specific columns and uses the ORDER BY clause on the [ProductCategoryID] column.

1 2	SELECT ProductID,[Name],[ProductNumber] FROM OPENDATASOURCE('MSDASQL', 'Driver={SQL SERVER};Server=azuredemosqldemo.database.windows.net; Database=Azuredemodatabase;UID=sqladmin;PWD=India@123).Azuredemodatabase.SalesLT.Product order by ProductcategoryID

For more details on OPENDATASOURCE, refer to the Microsoft docs.

However, if you need frequent data retrieval from Azure SQL DB using a remote query, the OPENROWSET is not a recommended way. For regular data access, use the linked server. In the following section, we configure the linked server pointing to Azure SQL DB.

Note: Azure SQL DB runs on port 1433. Therefore, your firewall must allow connecting to this port for the Azure network or IP address

Configure a linked server for Azure SQL database using SSMS

In this part, we configure the Linked server similar to an on-premises SQL Server instance. To configure it, connect to your on-premises SQL Server in SSMS and navigate to Server Objects in the Object Explorer. In the server objects folder, right-click on Linked Server and create New Linked Server.

In the New Linked Server window, enter the following information:

Linked Server Name: Enter the FQDN of Azure SQL Server. In my case, it is [azuredemosqldemo.database.windows.net]
Server type: Select SQL Server for Azure DB

Now, click on the Security tab, select the option – Be made using this security context. In the remote login and password, enter the Azure SQL Database credentials with access to objects you want to retrieve from the linked server.

Click Ok, and it creates the Linked Server from on-premises SQL Server to Azure SQL DB. If you try exploring the tables inside it, you get an error 40515. The following figure displays an error message the current SQL Server version does not support this feature.

To resolve this error, we need to recreate the linked server with a different approach. On the new linked server page, do the following things:

Linked Server: Put a name for your linked server connection
Server type: Choose other data source and enter the Azure SQL Server name in the data source, as shown below
Catalog: Enter the Azure SQL Database name in the catalog text box

Click on the Security and enter your Azure credentials using the option – Be made using this security context.

By default, remote procedural call (RPC) is disabled in the linked server connection. Therefore, you cannot execute a stored procedure in the Azure SQL Server from the on-premises SQL Server. To verify it, click on the Server Options in the new linked server window.

Change the RPC and RPC Out to True to execute remote codes for Azure SQL DB.

Click Ok, and it creates the Linked Server to Azure SQL Database from the on-premises SQL Server. To validate it, refresh object explorer and expand the Linked Server catalog and view the tables.

Linked Server validations

Select statements

To fetch data from Azure SQL Database, run the select statement in the following format:

1	[LinkedServerName].[Azure SQL Database].[schema].[object]

Therefore, in my demo, the following query retrieves the customer table data from Azure DB:

1	select * from [MYAZURESQL].[azuredemodatabase].[dbo].[Customer]

Stored procedure execution

Similarly, you can execute the remote stored procedure from the on-premise SQL Server using the following query. In the query, we have the following components.

Linked Server Name: MyAzureSQL
Azure SQL Database: azuredemodatabase
schema: dbo
Stored procedure: GetCustomersDetails
Parameter value: ‘5C151F78-E82E-4C9D-9618-2A681F0EBE50’

1	EXECUTE [MYAZURESQL].[azuredemodatabase].[dbo].[GetCustomersDetails] '5C151F78-E82E-4C9D-9618-2A681F0EBE50'

OPENQUERY rowset

We can use the OPENQUERY rowset as well to fetch the result set using the linked server. It uses an OLE DB data source. For example, in the following query, we specify the two parameters in the OPENQUERY function.

Linked Server name: MyAzureSQL
SQL query: ‘SELECT top 5 * FROM SalesLT.Product’

1	SELECT * FROM OPENQUERY([MYAZURESQL],'SELECT top 5 * FROM SalesLT.Product')

It executes the specified query on the specified Linked Server and retrieves the result set, as shown below.

We can update the record in an Azure SQL Database using the linked server from the on-premises SQL instance with a linked server OPENQUERY. For example, in the following query, we update the name in the [SalesLT].[Product] table for product id 706.

SELECT * FROM OPENQUERY([MYAZURESQL],'SELECT name as BeforeUpdate FROM SalesLT.Product WHERE Productid = 706')

UPDATE OPENQUERY ([MYAZURESQL], 'SELECT name FROM SalesLT.Product WHERE Productid = 706')

SET [name] = 'AzureSQLDB';

SELECT * FROM OPENQUERY([MYAZURESQL],'SELECT name as AfterUpdate FROM SalesLT.Product WHERE Productid = 706')

As shown below, the product name is updated using the OPENQUERY for the linked server.

Similarly, we can perform INSERT, Delete transactions as well. The following query deletes the records for the product id 706.

SELECT * FROM OPENQUERY([MYAZURESQL],'SELECT name as BeforeUpdate FROM SalesLT.Product WHERE Productid = 706')

DELETE OPENQUERY ([MYAZURESQL], 'SELECT name FROM SalesLT.Product WHERE Productid = 706')

SELECT * FROM OPENQUERY([MYAZURESQL],'SELECT name as AfterUpdate FROM SalesLT.Product WHERE Productid = 706')

We do not get any row after update the select statement because the row was deleted using the DELETE OPENQUERY statement.

Conclusion

This article explored configuring a linked server from an on-premises SQL Server to Azure SQL Database. You can access the linked server directly or using the OPENQUERY procedure call. If you do not require frequent data access remotely, consider using the OPENROWSET or OPENDATASOURCE.

Author
Recent Posts

Rajendra Gupta

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.

I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.

Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.

Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

Understanding PostgreSQL SUBSTRING function - September 21, 2024
How to install PostgreSQL on Ubuntu - July 13, 2023
How to use the CROSSTAB function in PostgreSQL - February 17, 2023