Rajendra Gupta

Access Azure SQL Database from On-premises SQL Server instance

August 4, 2021 by

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.

Linked Servers (Database Engine) - SQL Server | Microsoft Docs

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

Azure environment

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

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

Select the record

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

Blocked access for Ad hoc queries

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

Enable ad hoc distributed queries

  • 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

Select using OPENDATASOURCE

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

View records

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.

Configure a 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

Enter Azure server FQDN

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.

Login credentials

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.

Expand linked connection error

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

recreate the linked server

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

enter your Azure credentials

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.

RPC configuration

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

Change the RPC and RPC Out to True

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.

View catalog objects

Linked Server validations

Select statements

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

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

Select statements

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’ 

Stored procedure execution

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’

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

OPENQUERY rowset

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.

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

OPENQUERY update

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

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

OPENQUER Delete 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.

Rajendra Gupta
Latest posts by Rajendra Gupta (see all)
168 Views