Managed Identity on SQL Server On-Prem: The End of Stored Secrets
The Problem with Credentials in SQL Server For an On-Premises SQL Server to access Azure services, you traditionally need to store secrets: Common Scenarios Requiring Credentials Scenario Required Credential Backup to URL (Azure Blob) Storage account key or SAS token Extensible Key Management (Azure Key Vault) Service principal + secret Calling Azure OpenAI from T-SQL API key PolyBase to Azure Data Lake Service principal or key Associated Risks Manual Rotation Secrets expire. You need to plan and execute rotation and not forget to update all references. Secure Storage Where to store these secrets? In SQL Server via CREATE CREDENTIAL? In a config file? Each option has its risks. Attack Surface A compromised secret gives access to associated Azure resources. The more secrets you have, the larger the attack surface. Complex Auditing Who has access to these secrets? When were they used? Tracking is difficult. The Solution: Azure Arc + Managed Identity SQL Server 2025 connected to Azure Arc can geta Managed Identity : This identity: Is managed by Microsoft Entra ID Has no secret to store or rotate Can receive RBAC permissions on Azure resources Is centrally audited in Entra ID How It Works SQL Server 2025 On-Prem Azure Arc Agent installed on the server Managed Identity (automatically created in Entra ID) RBAC assignment on Azure resources -free access to Blob Storage, Key Vault, etc Step-by-Step Configuration Step 1: Enable Azure Arc on the Server and/or Register SQL Server in Azure Arc Follow the procedure describes in this article to onboard your server in Azure Arc. Connect Your SQL Server to Azure Arc Remember that you can also evaluate Azure Arc on a Azure VM (test use only) How to evaluate Azure Arc-enabled servers with an Azure virtual machine Step 2: Retrieve the Managed Identity The Managed Identity can be enabled and retrieved from Azure Arc | SQL Servers > “SQL Server instance” > Settings > Microsoft Entra ID Note: The Managed Identity is server-wide (not at the instance level) Step 3: Assign RBAC Roles Granting access to a Storage Account for backups $sqlServerId = (az resource show --resource-group "MyRG" --name "ServerName" --resource-type "Microsoft.HybridCompute/machines" --query identity.principalId -o tsv) az role assignment create --role "Storage Blob Data Contributor" ` --assignee-object-id $sqlServerId ` --scope "/subscriptions/xxx/resourceGroups/MyRG/providers/Microsoft.Storage/storageAccounts/mybackupaccount" Ex: Backup to URL Without Credential Before (with SAS token) -- Create a credential with a SAS token (expires, must be rotated) CREATE CREDENTIAL [https://mybackup.blob.core.windows.net/backups] WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 'sv=2022-11-02&ss=b&srt=sco&sp=rwdlacup...' BACKUP DATABASE [MyDB] TO URL = 'https://mybackup.blob.core.windows.net/backups/MyDB.bak' WITH COMPRESSION After (with Managed Identity --No secret anymore CREATE CREDENTIAL [https://mybackup.blob.core.windows.net/backups] WITH IDENTITY = 'Managed Identity' BACKUP DATABASE [MyDB] TO URL = 'https://mybackup.blob.core.windows.net/backups/MyDB.bak' WITH COMPRESSION Extensible Key Management with Key Vault EKM Configuration with Managed Identity CREATE CREDENTIAL [MyAKV.vault.azure.net] WITH IDENTITY = 'Managed Identity' FOR CRYPTOGRAPHIC PROVIDER AzureKeyVault_EKM_Prov; How Copilot Can Help Infrastructure Configuration Walk me through setting up Azure Arc for SQL Server 2025 to use Managed Identity for backups to Azure Blob Storage @mssql Generate the PowerShell commands to register my SQL Server with Azure Arc and configure RBAC for Key Vault access Identify Existing Credentials to Migrate List all credentials in my SQL Server that use SHARED ACCESS SIGNATURE or contain secrets, so I can plan migration to Managed Identity Migration Scripts I have backup jobs using SAS token credentials. Generate a migration script to convert them to use Managed Identity Troubleshooting My backup WITH MANAGED_IDENTITY fails with "Authorization failed". What are the steps to diagnose RBAC permission issues? @mssql The Azure Arc agent shows "Disconnected" status. How do I troubleshoot connectivity and re-register the server? Audit and Compliance Generate a report showing all Azure resources my SQL Server's Managed Identity has access to, with their RBAC role assignments Prerequisites and Limitations Prerequisites Azure Arc agent installed and connected SQL Server 2025, running on Windows Azure Extension for SQL Server. Current Limitations Failover cluster instances isn't supported. Disabling not recommended Only system-assigned managed identities are supported FIDO2 method not currently supported Azure public cloud access required Documentation Overview Managed identity overview Set Up Managed Identity and Microsoft Entra Authentication for SQL Server Enabled by Azure Arc Set up Transparent Data Encryption (TDE) Extensible Key Management with Azure Key Vault
Passed AZ-500 Exam
I have passed my exam. Initially, I found AZ-500 exam preparation difficult but later I got https://www.p2pexams.com/products/az-500 material from P2PExams. It was a great help for me. This AZ-500 exam study material was well-designed by professionals. It helped me understand all key concepts related to an actual exam. On the exam day. I easily attempted all the questions and got brilliant results on the exam. I especially thank P2PExams. It was really a great resource.
ERP decommissioning
Hello, We have the problem of decommissioning unmaintained ERPs from the acquisition. We replaced them with the group's standards. Details : - IFS version 8.0 on WS 2008 - DB Server 2012 on AWS Finance asks us to keep them accessible for the next 10 years on isolated servers. Guaranteeing access for the next 10 years to software that no one in IT knows anymore with older technologies seems impossible to me or at least very difficult. Do you have similar issues and if so how do you respond? Thank you for your return.
New Blog | Microsoft Purview DevOps policies for Azure SQL MI enters Public Preview
Microsoft Purview DevOps policies already support integrations with Azure SQL Database (GA) and SQL Server 2022 via Azure Arc (GA). DevOps policies is already Generally Available (GA) for those two data sources. We are pleased to announce that today we are adding one more data source to the list: Azure SQL Managed Instance into Public Preview. Read the full update here: Microsoft Purview DevOps policies for Azure SQL MI enters Public PreviewNew Blog | Enhancements to Microsoft Purview policies for Arc-enabled SQL Server
A recent change in Microsoft Purview extends and makes it easier to create data policies for Azure Arc-enabled SQL Servers. With this change: Policies on Linux Arc-SQL are now supported. Prior to this, only policies on Windows Arc-SQL were supported. Microsoft Purview seamlessly adapts to updates made to the app registration used to enable Azure AD authentication for Arc-SQL. It is now simpler to register an Arc-SQL data source in Microsoft Purview and then enable policies, given that capturing the Application Id of the Arc-SQL app registration is not needed anymore. Policies on whole resource groups and subscriptions are now enforced on Arc-SQL data sources without the need to individually and explicitly register them in Microsoft Purview. Read the full blog post: Enhancements to Microsoft Purview policies for Arc-enabled SQL ServerNew Blog Post | Microsoft Purview Data Policy for SQL DevOps access provisioning in public preview
Microsoft Purview Data Policy for SQL DevOps access provisioning now in public preview - Microsoft Tech Community Discovering the right data and getting timely and compliant access to data are huge problems for modern enterprises. The Microsoft Purview Data Catalog already addresses the data discovery problem. Microsoft Purview Data Policy is the next leap in this journey. It brings a business user friendly policy authoring experience and ambient enforcement for data sources. Microsoft Purview Data Policy covers three different scenarios – first, a business data owner can provision access to data through an intuitive authoring experience. We further extended this technology to greatly enhance self-service data access by automatically provisioning access for specific supported data sources. Now, we have enabled devops policy to manage system data at scale for Azure SQL DB and SQL Server 2022.New Blog Post | Easier filtering and improved PowerBI integration in Azure Purview search
Easier filtering and improved PowerBI integration in Azure Purview search - Microsoft Tech Community The Azure Purview data catalog has released a new set of features that make it easier find important and interesting data to accomplish your data-driven tasks. Object Type Filter The object type filter is a new facet that allows users to look at similar data types across different source types. For example, say you are looking for a finance table, but you aren't sure if that data lives in Azure SQL Database or an Azure Synapse Dedicated SQL Table. The object type filter let's you filter for "Tables" and shows all results across different sources that have a tabular structure. Another example is "Data pipelines" includes results from different data integration tools such as Azure Data Factory, Azure Synapse Analytics, and SSIS.New Blog Post | Azure Defender PoC Series – Azure Defender for SQL
Azure Defender PoC Series – Azure Defender for SQL - Microsoft Tech Community This article is a continuation of Azure Defender PoC Series which provides you guidelines on how to perform a proof of concept for a specific Azure Defender plan. For a more holistic approach where you need to validate Azure Security Center and Azure Defender, please read How to Effectively Perform an Azure Security Center PoC article. There can be many security vulnerabilities in databases that are sometimes taken advantage of by malicious actors. According to the Github 2020 report, a vulnerability typically goes undetected for 218 weeks (just over four years) before being disclosed and fixed. Injection attacks, such as those on SQL and NoSQL, are among the most popular types of cyberattacks for web applications (as per OWASP Top 10). SQL Injection attacks, brute-force attacks, SQL shell OS attacks leading to crypto-mining and ransomware, can be detected and remediated by the Azure Defender for SQL plan.Unable to connect to the destination mentioned in the KeyVault URL
I am trying to use the Dynamics 365 Data export service to connect with my Azure SQL with Azure AD connection. When following this tutorial : https://www.youtube.com/watch?v=txms2Yvn6Vc and many more; i figured out how to export my D365 data export service but this tutorial is based on my SQL user. When I try to use a Azure AD user to do the authentication I keep getting the error "Unable to connect to the destination mentioned in the keyvaul URL error" What I did ATM is , used the D365 Powershell script like the tutorial mentioned to create a keyvault with the connection string and paste the keyvault url inside my D365 settings to validate. This works if i use the second connection string in the pic below But when I do the exact same thing with an Active directory connection string (second connection string in picture) This does not work !! Inside the SQL server I ensured that my Azure AD user has active directory admin roles. And the userID I use inside my connection string has all the rights inside the server and database to do the minimum for D365 Export service (create, insert table, ....) But still i got a fail inside my D365 . Tried everything ATM don't know where to look. Any body who had the same issues as me ? OR know which step I am missing ?
