How to create Linked server from SQL Server to Azure SQL Database
Here in this blog, we are going to demo how to configure Linked server from on-prem SQL Server instance to Azure SQL database. I will have a reference to this blog in my subsequent blogs that will soon be available for Dynamic Data Masking & Cross database/server queries. We have taken example of two databases, Database1 (Copy of AdventureWorks 2019) as Azure SQL database & Database2 hosted in On-prem SQL Server Instance. In this demo we will be querying Database1 tables in the context of Database2. Database1: Azure SQL database Database2: SQL Server Instance on-prem Here is the sequence of steps that you need to follow to configure Linked Server using SSMS tool. Step-1: Connect to SQL Server Instance in SSMS tool and go to Object Explorer. Expand the Server Objects, right click on Linked Server and create a New Linked Server. Step-2: Go to General tab in the new Linked Server window. Under the Server type section, choose the Other data source option. Give a suitable name to the Linked Server as per your choice in the Linked Server section. Choose the “Microsoft OLE DB Provider SQL Server” in the Provider dropdown. In the Data source section, specify the Azure database logical server name for e.g., logicalservername.database.windows.net. You just need to change the logicalservername to the actual Azure server name which you can get from the Azure portal. Enter the Azure database name that you want to create the linked server to in the catalog field. Step-3: Now go to Security tab and choose the option “Be made using this security context”. Enter the SQL login credentials which is already present on the Azure DB server and has access to Database1. Please note this option is the least secure way to address the security configuration of Linked Server as any user who uses the Linked Server will be authenticated on the remote server using credentials provided here. Use of this option should be limited to testing environment. Step-4: Once the Linked Server is successfully created you can see it when you expand Linked Server section in Object Explorer and expand it further to view the list of tables. Step-5: Open a new query window in SSMS and switch to Database2 context on the on-prem SQL Server. Run the SELECT query to fetch data using the Linked Server that you just created in the previous steps. select * from [AZURE DATABASE DDMTEST].[Database1].[Person].[PersonPhone] Hope you find the blog helpful. Please share your questions or feedback.Enabling Azure Key Vault for SQL Server on Linux
Enhancing Security with EKM using Azure Key Vault in SQL Server on Linux: We’re excited to announce that Extensible Key Management (EKM) using Azure Key Vault in SQL Server on Linux is now generally available from SQL Server 2022 CU12 onwards, which allows you to manage encryption keys outside of SQL Server using Azure Key Vaults. In this blog post, we’ll explore how to leverage Azure Key Vault as an EKM provider for SQL Server on Linux. Azure Key Vault: The Bridge to Enhanced Security is a cloud-based service that securely stores keys, secrets, and certificates. By integrating Azure Key Vault with SQL Server, you can benefit from its scalability, high performance, and high availability. Refer Set up Transparent Data Encryption (TDE) Extensible Key Management with Azure Key Vault - SQL Server | Microsoft Learn for more details. Setting Up EKM with Azure Key Vault Here’s a streamlined version of the setup process for EKM with Azure Key Vault on SQL Server for Linux: Initialize a Microsoft Entra service principal. Establish an Azure Key Vault. Set up SQL Server for EKM and register the SQL Server Connector. Finalize SQL Server configuration. The full guide for setting up AKV with SQL Server on Linux is available here Set up Transparent Data Encryption (TDE) Extensible Key Management with Azure Key Vault - SQL Server | Microsoft Learn . For SQL on Linux, omit steps 3 and 4 and proceed directly to step 5. I’ve included screenshots below for your quick reference that covers the SQL Server configuration to use AKV. Run the below commands to enable EKM in SQL Server and register the SQL Server Connector as EKM provider. Please note: SQL Server requires manual rotation of the TDE certificate or asymmetric key, as it doesn’t rotate them automatically. Regular key rotation is essential for maintaining security and effective key management. Conclusion Using Azure Key Vault for EKM with SQL Server on Linux boosts security, streamlines key management, and supports compliance. With data protection being paramount, Azure Key Vault’s integration offers a robust solution. Stay tuned for more insights on SQL Server on Linux! :old_key:️:locked: Official Documentation: Extensible Key Management using Azure Key Vault - SQL Server Setup Steps for Extensible Key Management Using the Azure Key Vault Azure Key Vault Integration for SQL Server on Azure VMsPart 4 - SQL Server TDE and Extensible Key Management Using Azure Key Vault
Configure SQL Server This is Part: 4 (SQL) of a 4-part blog series: After setting up Azure Active Directory and registering the AAD Application and additionally creating an Azure Key Vault, the next step is to put it all together in SQL Server where you can create credentials (to talk to AKV), create an asymmetric key and use that key to configure/encrypt a database with TDE.
