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
Token Protection by using Microsoft Entra ID.
As organizations move to the cloud and adopt SaaS applications, identities are becoming increasingly crucial for accessing resources. Cybercriminals exploit legitimate and authorized identities to steal data and access credentials through methods like phishing, malware, data breaches, brute-force/password spray attacks, and prior compromises. As in past years, password-based attacks on users constitute most identity-related attacks. As MFA blocks most password-based attacks, threat actors are shifting their focus, moving up the cyberattack chain in three ways: 1) attacking infrastructure, 2) bypassing authentication, and 3) exploiting applications. They are leaning more heavily into adversary-in-the-middle (AiTM) phishing attacks and token theft. Over the last year, as Microsoft Digital Defense Report (MDDR 2024) a 146% rise in AiTM phishing attacks. In AiTM attack , the attackers steal tokens instead of passwords. The Frameworks used by attackers go far beyond credential phishing, by inserting malicious infrastructure between the user and the legitimate application the user is trying to access. When the user is phished, the malicious infrastructure captures both the credentials of the user, and the token. By compromising and replaying a token issued to an identity that has already completed multifactor authentication, the threat actor satisfies the validation of MFA and access is granted to organizational resources accordingly. Now it is imperative that tokens must be protected from token theft. Let us understand more on tokens. An Entra identity token is a security token issued by Microsoft Entra ID for authentication and authorization. There are several types: Access Tokens: Grant access to resources on behalf of an authenticated user, containing user and resource information. ID Tokens: Authenticate users, issued in the OpenID Connect flow, containing user identity and authentication details. Refresh Tokens: Obtain new access tokens without re-authentication, usually issued with access tokens and have a longer lifespan. Ensuring Token Security By following best practices, you can significantly enhance the security of your tokens and protect your applications from unauthorized access. Use Secure Transmission: Always transmit tokens over secure channels such as HTTPS to prevent interception by unauthorized parties. Token Binding: Implement Token Protection (formerly known as token binding) to cryptographically tie tokens to client secrets. This prevents token replay attacks from different devices. Conditional Access Policies: Use Conditional Access policies to enforce compliant network checks. This ensures that tokens are only used from trusted networks and devices. Continuous Access Evaluation (CAE): Implement CAE to continuously evaluate the security state of the session. This helps in detecting and revoking tokens if there are changes in the user's security posture, such as network location changes. Short Token Lifetimes: Use short lifetimes for access tokens and refresh tokens to limit the window of opportunity for attackers. Secure Storage: Store tokens securely on the client side, using secure storage mechanisms provided by the operating system, such as Keychain on iOS or Keystore on Android. Regular Audits and Monitoring: Regularly audit token usage and monitor for any unusual activity. This helps in early detection of potential security breaches. Now we will discuss Entra ID new features for Token Protection. Token protection using conditional access : This feature will provide refresh token protection. Compliant network check with Conditional Access: This feature will provide both refresh token and Access token protection. Token protection using conditional access: Token protection (sometimes referred to as token binding in the industry) attempts to reduce attacks using token theft by ensuring a token is usable only from the intended device. When an attacker is able to steal a token, by hijacking or replay, they can impersonate their victim until the token expires or is revoked. Token theft is thought to be a relatively rare event, but the damage from it can be significant. Token protection creates a cryptographically secure tie between the token and the device (client secret) it's issued to. Without the client secret, the bound token is useless. When a user registers a Windows 10 or newer device in Microsoft Entra ID, their primary identity is bound to the device. What this means: A policy can ensure that only bound sign-in session (or refresh) tokens, otherwise known as Primary Refresh Tokens (PRTs) are used by applications when requesting access to a resource. Token protection is currently in public preview Create a Conditional Access policy Users who perform specialized roles like those described in Privileged access security levels are possible targets for this functionality. We recommend piloting with a small subset to begin. The steps that follow help create a Conditional Access policy to require token protection for Exchange Online and SharePoint Online on Windows devices. Sign in to the Microsoft Entra admin center as at least a Conditional Access Administrator. Browse to Protection > Conditional Access > Policies. Select New policy. Give your policy a name. We recommend that organizations create a meaningful standard for the names of their policies. Under Assignments, select Users or workload identities. Under Include, select the users or groups who are testing this policy. Under Exclude, select Users and groups and choose your organization's emergency access or break-glass accounts. 6.Under Target resources > Resources (formerly cloud apps) > Include > Select resources Under Select, select the following applications supported by the preview: Office 365 Exchange Online Office 365 SharePoint Online Choose Select. 7. Under Conditions: Under Device platforms: Set Configure to Yes. Include > Select device platforms > Windows. Select Done. Under Client apps: Set Configure to Yes Under Modern authentication clients, only select Mobile apps and desktop clients. Leave other items unchecked. Select Done. 8. Under Access controls > Session, select Require token protection for sign-in sessions and select Select. 9. Confirm your settings and set Enable policy to Report-only. 10.Select Create to create to enable your policy. After administrators confirm the settings using report-only mode, they can move the Enable policy toggle from Report-only to On. Capture logs and analyze Monitoring Conditional Access enforcement of token protection before and after enforcement. Sign-in logs Use Microsoft Entra sign-in log to verify the outcome of a token protection enforcement policy in report only mode or in enabled mode. Sign in to the Microsoft Entra admin center as at least a Conditional Access Administrator. Browse to Identity > Monitoring & health > Sign-in logs. Select a specific request to determine if the policy is applied or not. Go to the Conditional Access or Report-Only pane depending on its state and select the name of your policy requiring token protection. Under Session Controls check to see if the policy requirements were satisfied or not. You can refer below link to know more about license requirements, prerequisites & limitations. Token protection in Microsoft Entra Conditional Access - Microsoft Entra ID | Microsoft Learn Enable compliant network check with Conditional Access Organizations who use Conditional Access along with the Global Secure Access, can prevent malicious access to Microsoft apps, third-party SaaS apps, and private line-of-business (LoB) apps using multiple conditions to provide defense-in-depth. These conditions might include device compliance, location, and more to provide protection against user identity or token theft. Global Secure Access introduces the concept of a compliant network within Microsoft Entra ID Conditional Access. This compliant network check ensures users connect from a verified network connectivity model for their specific tenant and are compliant with security policies enforced by administrators. The Global Secure Access Client installed on devices or users behind configured remote networks allows administrators to secure resources behind a compliant network with advanced Conditional Access controls. This compliant network feature makes it easier for administrators to manage access policies, without having to maintain a list of egress IP addresses. This removes the requirement to hairpin traffic through organization's VPN. Compliant network check enforcement Compliant network enforcement reduces the risk of token theft/replay attacks. Compliant network enforcement happens at the authentication plane (generally available) and at the data plane (preview). Authentication plane enforcement is performed by Microsoft Entra ID at the time of user authentication. If an adversary has stolen a session token and attempts to replay it from a device that is not connected to your organization’s compliant network (for example, requesting an access token with a stolen refresh token), Entra ID will immediately deny the request and further access will be blocked. Data plane enforcement works with services that support Continuous Access Evaluation (CAE) - currently, only SharePoint & Exchange Online. With apps that support CAE, stolen access tokens that are replayed outside your tenant’s compliant network will be rejected by the application in near-real time. Without CAE, a stolen access token will last up to its full lifetime (default 60-90 minutes). This compliant network check is specific to each tenant. Using this check, you can ensure that other organizations using Microsoft's Global Secure Access services can't access your resources. For example: Contoso can protect their services like Exchange Online and SharePoint Online behind their compliant network check to ensure only Contoso users can access these resources. If another organization like Fabrikam was using a compliant network check, they wouldn't pass Contoso's compliant network check. The compliant network is different than IPv4, IPv6, or geographic locations you might configure in Microsoft Entra. Administrators are not required to review and maintain compliant network IP addresses/ranges, strengthening the security posture and minimizing the ongoing administrative overhead. Enable Global Secure Access signaling for Conditional Access To enable the required setting to allow the compliant network check, an administrator must take the following steps. Sign in to the Microsoft Entra admin center as a Global Secure Access Administrator. Browse to Global Secure Access > Settings > Session management > Adaptive access. Select the toggle to Enable CA Signaling for Entra ID (covering all cloud apps). This will automatically enable CAE signaling for Office 365 (preview). Browse to Protection > Conditional Access > Named locations. a. Confirm you have a location called All Compliant Network locationswith location type Network Access. Organizations can optionally mark this location as trusted. You can refer below link to know more about license requirements, prerequisites & limitations Protect your resources behind the compliant network The compliant network Conditional Access policy can be used to protect your Microsoft and third-party applications. A typical policy will have a 'Block' grant for all network locations except Compliant Network. The following example demonstrates the steps to configure this type of policy: Sign in to the Microsoft Entra admin center as at least a Conditional Access Administrator. Browse to Protection > Conditional Access. Select Create new policy. Give your policy a name. We recommend that organizations create a meaningful standard for the names of their policies. Under Assignments, select Users or workload identities. Under Include, select All users. Under Exclude, select Users and groupsand choose your organization's emergency access or break-glass accounts. 6. Under Target resources > Include and select All resources (formerly 'All cloud apps'). If your organization is enrolling devices into Microsoft Intune, it is recommended to exclude the applications Microsoft Intune Enrollmentand Microsoft Intune from your Conditional Access policy to avoid a circular dependency. 7. Under Network. Set Configureto Yes. Under Include, select Any location. Under Exclude, select the All Compliant Network locationslocation. 8. Under Access controls: Grant, select Block Access, and select Select. 9. Confirm your settings and set Enable policy to On. 10. Select the Create button to create to enable your policy. User exclusions Conditional Access policies are powerful tools, we recommend excluding the following accounts from your policies: Emergency access or break-glass accounts to prevent lockout due to policy misconfiguration. In the unlikely scenario all administrators are locked out, your emergency-access administrative account can be used to log in and take steps to recover access. More information can be found in the article, Manage emergency access accounts in Microsoft Entra ID. Service accounts and Service principals, such as the Microsoft Entra Connect Sync Account. Service accounts are non-interactive accounts that aren't tied to any particular user. They're normally used by back-end services allowing programmatic access to applications but are also used to sign in to systems for administrative purposes. Calls made by service principals won't be blocked by Conditional Access policies scoped to users. Use Conditional Access for workload identities to define policies targeting service principals. If your organization has these accounts in use in scripts or code, consider replacing them with managed identities. Try your compliant network policy On an end-user device with the Global Secure Access client installed and running, browse to https://outlook.office.com/mail/ or https://yourcompanyname.sharepoint.com/, you have access to resources. Pause the Global Secure Access client by right-clicking the application in the Windows tray and selecting Pause. Browse to https://outlook.office.com/mail/ or https://yourcompanyname.sharepoint.com/, you're blocked from accessing resources with an error message that says You cannot access this right now. You can refer below link to know more about license requirements, prerequisites & limitations Enable compliant network check with Conditional Access - Global Secure Access | Microsoft Learn
