How to create Linked server from SQL Server to Azure SQL Database
Here in this blog, we are going todemohow to configure Linked serverfromon-prem SQL Serverinstanceto Azure SQL database.Iwillhave a reference tothis blog in my subsequent blogsthat will soon be available for Dynamic Data Masking &Cross database/serverqueries. We have taken example of two databases,Database1(Copy of AdventureWorks 2019)as Azure SQL database&Database2hosted in On-prem SQL Server Instance. In this demo we willbequerying Database1tablesin the context of Database2. Database1:Azure SQLdatabase Database2:SQL Server Instance on-prem Hereisthe sequence of stepsthat you needtofollow toconfigure Linked Server using SSMS tool. Step-1:Connectto SQL Server Instance in SSMStooland go toObject Explorer.ExpandtheServer Objects, right click onLinked Serverand create aNew Linked Server. Step-2:Go toGeneraltab in the newLinked Server window. Under the Server type section, choose the Otherdata source option. Give a suitable name to the Linked Server as per yourchoicein theLinked Serversection. Choose the “Microsoft OLE DB Provider SQL Server”in theProviderdropdown.In theDatasourcesection, specify the Azure database logical server name fore.g.,logicalservername.database.windows.net. You just need to change the logicalservername to the actualAzure servername which youcanget from the Azure portal.EntertheAzuredatabase namethat you want to create the linked server toin thecatalogfield. Step-3:Nowgo toSecuritytaband choose the option “Be made using this security context”. Enter theSQLlogin 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 itwhen you expandLinked Server section in Object Explorerandexpand it further to viewthe list of tables. Step-5:Open a new query windowin SSMSandswitch to Database2 contexton the on-prem SQL Server. Run the SELECT query to fetch data using the Linked Serverthat you just created in theprevious 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.Dynamic SQL & SQL injection
First published on MSDN on Jan 04, 2007 Iknow there are a lot of papers that talk about dynamic SQL in more depth than what I am going to cover, but as SQL injection is still one of the biggest security problems in the relation databases world, that I decided to include this part as a quick (and hopefully helpful) reminder.
