In this article, we will discuss the process of creating a linked server between a SQL Server instance on a local computer and an Azure SQL Database.

First, let’s assume that we have a database named “AzureDatabase” on an Azure server named “companyemployee.database.windows.net”. On our local machine, we have installed a default instance of SQL Server 2016 and created a database named “DemoDatabase”.

To create a linked server between these two instances, we need to open SQL Server Management Studio and connect to the local instance. In the Object Explorer, expand Server Objects > Linked Servers and right-click to select “New Linked Server”.

In the “New Linked Server” dialog box, choose “SQL Server” as the server type and enter the network name of the server we are connecting to, which in this case is “COMPANYEMPLOYEE.DATABASE.WINDOWS.NET”.

Next, we can configure the security options on the “Security” page. These options determine the security context used to connect to the linked server. There are several options available:

1. Local server login to remote server login mappings: This option allows a specific login to use the linked server. Local logins can use SQL Server authentication or Windows authentication to connect to the remote SQL Server.
2. Not to be made: This option is used when we do not want to use the security context for logins that are not defined in the list.
3. Be made without using the security context: This option is used when we want to use the security context for logins that are not defined in the list.
4. Be made using the login’s current security context: This option is used when we want to use the security context of the login that is connected to the server. If the login is connected using Windows authentication, it will use the Windows credentials to connect to the remote server. If it is connected using a SQL Server login, it will use SQL Server authentication to connect to the remote server.
5. Be made using this security context: This option is used when we want to connect to the remote server using a specific SQL username and password. We must provide the username and password in the remote login and password text boxes.

Once the linked server is created, we can query the linked server database using the four-part naming convention (server.database.schema.object). For example:

SELECT * FROM [COMPANYEMPLOYEE.DATABASE.WINDOWS.NET].AzureDatabase.dbo.Employees

However, there might be some errors when querying the database. One common error is “Reference to database and/or server name is not supported in this version of SQL Server”. This error occurs because the Azure master database does not allow direct connections. To fix this, we need to make some changes to the linked server configuration:

• Provide a desired name for the linked server in the “Linked Server” textbox.
• Choose “Microsoft OLEDB Provider for SQL Server” from the Provider drop-down box.
• Enter the network name of the Azure SQL Server instance in the Data source textbox.
• Enter the Azure database name in the Catalog textbox.

Once the linked server is configured, we can run the query again to verify that the linked server is working properly.

In addition to querying the linked server, we can also execute stored procedures on the remote server. However, there might be some errors when executing stored procedures. One common error is “Server ‘AZURE LINKED SERVER’ is not configured for RPC”. To fix this, we need to enable RPC and RPC Out for the linked server. This can be done by executing the following code on the local server:

USE [master]
GO
EXEC master.dbo.sp_serveroption @server=N'AZURE LINKED SERVER', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'AZURE LINKED SERVER', @optname=N'rpc out', @optvalue=N'true'
GO

Alternatively, we can change these settings from the Linked Server Properties in SQL Server Management Studio.

Once RPC and RPC Out are enabled, we can execute the stored procedure on the linked server.

In summary, this article discussed the process of creating a linked server between a local SQL Server and an Azure SQL Server. We covered the steps to configure the linked server, query the linked server database, and execute stored procedures on the remote server.