Make Your Azure SQL Managed Instance Passwordless

Published Nov 02 2021 07:40 AM 1,474 Views
Microsoft

Azure SQL Managed Instance announces limited public preview for Windows Authentication protocol for Azure Active Directory users, and support for Managed Identity credential across Managed Instance surface area.

 

No one loves passwords. They are the most common attack target and require extra caution and maintenance to use them in a secure way. To address this, Microsoft is making continued investments in Azure Active Directory (Azure AD) authentication and Managed identities to pave the way to a passwordless future.

 

Within the Azure SQL team, we are building on top of those investments, and with our latest improvements that future is about to begin for users of Managed Instance.

Azure SQL Managed Instance Passwordless Authentication ScenariosAzure SQL Managed Instance Passwordless Authentication Scenarios

You can disable password-based access (SQL Authentication) to your Managed Instance even today by using the Azure AD-only authentication setting. But what if you have an app that doesn’t support Azure AD authentication, or what if you are still using Windows Authentication? For you, we bring Windows Authentication to Azure AD.

 

Windows Authentication protocol for Azure AD users

Windows Authentication is an additional Single-Sign-On authentication option for Azure AD users that supports Azure AD authentication with the Kerberos protocol. From a compatibility perspective it enables legacy apps or just apps that do not yet support Azure AD authentication to connect to Managed Instance. In that regard, your existing portfolio of applications, no matter how old, will no longer represent a barrier for identity management and security modernization in Azure.

 

Yet this feature is not only about compatibility, but also about hardening the security and modernizing the infrastructure. If your application is running on Windows 11 Azure AD joined or Hybrid Azure AD joined machine that you’ve logged into with modern authentication (i.e., Windows Hello), that would count as Multi Factor Authentication (MFA) and you’ll be granted secure access to your Managed Instance.

 

Though I’m sure you’d agree this is great, you may wonder what exactly it was that we’ve done to enable passwordless connection between Managed Instance and other Azure resources.

 

Configuring Managed Instance secure access to the Azure resources

Managed identity has been used for some time to provide Managed Instance with secure, passwordless, and RBAC-based, access to Azure AD to support authentication, and access to Azure Key Vault for TDE customer managed key. Now we are expanding these to a new set of scenarios.

 

Once you create a server or database level credential with Managed Identity, you will be able to use this credential to authenticate to an Azure Storage account while doing backup / restore, bulk loading of data, and creating a server audit.

 

 

 

CREATE CREDENTIAL [https://mitutorials.blob.core.windows.net/backups]
WITH IDENTITY = 'Managed Identity'

RESTORE FILELISTONLY FROM URL =
'https://mitutorials.blob.core.windows.net/backups/WideWorldImporters-Standard.bak'

 

 

 

And this is not all. In this wave of improvements, we’ve added Managed identity support to a linked server feature, effectively extending passwordless access to any Azure SQL resource with Azure AD authentication enabled.

 

 

 

EXEC master.dbo.sp_addlinkedserver
    =N'MyLinkedServerMSI', 
    =N'MSOLEDBSQL', 
    @provstr=N'Server=lsdemomi.a3c42d4b14e.database.windows.net;Authentication=ActiveDirectoryMSI'

EXEC master.dbo.sp_addlinkedsrvlogin 
    @rmtsrvname=N'MyLinkedServerMSI',@useself=N'False'

 

 

 

If the linked server is a Managed Instance in the same Server Trust Group, then you could configure this linked server in such a way that the authentication context flows from the primary instance to the linked instance.

 

 

 

EXEC master.dbo.sp_addlinkedserver 
    =N'MyLinkedServer', 
    =N'MSOLEDBSQL', 
    @datasrc=N'lsdemomi.a3c42d4b14e.database.windows.net'

EXEC master.dbo.sp_addlinkedsrvlogin 
    @rmtsrvname=N'MyLinkedServer',
	@useself=N'True'

 

 

 

Next steps

You can start improving the security of your Managed Instance today:

 

Going forward

Improvements don’t stop here.  In the near term we plan to support user-assigned managed identity as a primary managed identity for Managed Instance, as well to enable the use of Managed Identity for passwordless authentication in transactional replication and log replay service.

 

Keep an eye on the AzureSQL TechCommunity Blog to learn more on these topics.

 

%3CLINGO-SUB%20id%3D%22lingo-sub-2908166%22%20slang%3D%22en-US%22%3EMake%20Your%20Azure%20SQL%20Managed%20Instance%20Passwordless%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2908166%22%20slang%3D%22en-US%22%3E%3CP%3E%3CFONT%20color%3D%22%23808080%22%3EAzure%20SQL%20Managed%20Instance%20announces%20limited%20public%20preview%20for%20Windows%20Authentication%20protocol%20for%20Azure%20Active%20Directory%20users%2C%20and%20support%20for%20Managed%20Identity%20credential%20across%20Managed%20Instance%20surface%20area.%3C%2FFONT%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ENo%20one%20loves%20passwords.%20They%20are%20the%20most%20common%20attack%20target%20and%20require%20extra%20caution%20and%20maintenance%20to%20use%20them%20in%20a%20secure%20way.%20To%20address%20this%2C%20Microsoft%20is%20making%20continued%20investments%20in%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Factive-directory%2Fauthentication%2Foverview-authentication%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EAzure%20Active%20Directory%20(Azure%20AD)%20authentication%3C%2FA%3E%20and%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Factive-directory%2Fmanaged-identities-azure-resources%2Foverview%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EManaged%20identities%3C%2FA%3E%20to%20pave%20the%20way%20to%20a%20passwordless%20future.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EWithin%20the%20Azure%20SQL%20team%2C%20we%20are%20building%20on%20top%20of%20those%20investments%2C%20and%20with%20our%20latest%20improvements%20that%20future%20is%20about%20to%20begin%20for%20users%20of%20Managed%20Instance.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-center%22%20image-alt%3D%22Azure%20SQL%20Managed%20Instance%20Passwordless%20Authentication%20Scenarios.png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F322187iC221AAC96111C5E9%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Azure%20SQL%20Managed%20Instance%20Passwordless%20Authentication%20Scenarios.png%22%20alt%3D%22Azure%20SQL%20Managed%20Instance%20Passwordless%20Authentication%20Scenarios%22%20%2F%3E%3CSPAN%20class%3D%22lia-inline-image-caption%22%20onclick%3D%22event.preventDefault()%3B%22%3EAzure%20SQL%20Managed%20Instance%20Passwordless%20Authentication%20Scenarios%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%3CSPAN%20style%3D%22font-family%3A%20inherit%3B%22%3EYou%20can%20disable%20password-based%20access%20(SQL%20Authentication)%20to%20your%20Managed%20Instance%20even%20today%20by%20using%20the%20%3C%2FSPAN%3E%3CA%20style%3D%22font-family%3A%20inherit%3B%20background-color%3A%20%23ffffff%3B%22%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fazure-sql%2Fdatabase%2Fauthentication-azure-ad-only-authentication%3Ftabs%3Dazure-cli%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EAzure%20AD-only%20authentication%3C%2FA%3E%3CSPAN%20style%3D%22font-family%3A%20inherit%3B%22%3E%20setting.%20But%20what%20if%20you%20have%20an%20app%20that%20doesn%E2%80%99t%20support%20Azure%20AD%20authentication%2C%20or%20what%20if%20you%20are%20still%20using%20Windows%20Authentication%3F%20For%20you%2C%20we%20bring%20Windows%20Authentication%20to%20Azure%20AD.%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId--299690014%22%20id%3D%22toc-hId--299688961%22%3EWindows%20Authentication%20protocol%20for%20Azure%20AD%20users%3C%2FH2%3E%0A%3CP%3EWindows%20Authentication%20is%20an%20additional%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fazure-sql%2Fdatabase%2Fauthentication-aad-configure%3Ftabs%3Dazure-powershell%23active-directory-integrated-authentication%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3ESingle-Sign-On%20authentication%20option%3C%2FA%3E%20for%20Azure%20AD%20users%20that%20supports%20Azure%20AD%20authentication%20with%20the%20Kerberos%20protocol.%20From%20a%20compatibility%20perspective%20it%20enables%20legacy%20apps%20or%20just%20apps%20that%20do%20not%20yet%20support%20Azure%20AD%20authentication%20to%20connect%20to%20Managed%20Instance.%26nbsp%3BIn%20that%20regard%2C%20your%20%3CSTRONG%3Eexisting%20portfolio%20of%20applications%3C%2FSTRONG%3E%2C%20no%20matter%20how%20old%20is%20it%2C%20will%20n%3CSTRONG%3Eot%20represent%20any%20longer%20barrier%20%3C%2FSTRONG%3Efor%20identity%20management%20and%20security%20modernization%20in%20Azure.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EYet%20this%20feature%20is%20not%20only%20about%20compatibility%2C%20but%20also%20about%20hardening%20the%20security%20and%20modernizing%20the%20infrastructure.%20If%20your%20application%20is%20running%20on%20Windows%2011%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Factive-directory%2Fdevices%2Fconcept-azure-ad-join-hybrid%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EAzure%20AD%20joined%3C%2FA%3E%20or%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Factive-directory%2Fdevices%2Fconcept-azure-ad-join-hybrid%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EHybrid%20Azure%20AD%20joined%3C%2FA%3E%20machine%20that%20you%E2%80%99ve%20logged%20into%20with%20modern%20authentication%20(i.e.%2C%20%3CA%20href%3D%22https%3A%2F%2Fsupport.microsoft.com%2Fen-us%2Fwindows%2Flearn-about-windows-hello-and-set-it-up-dae28983-8242-bb2a-d3d1-87c9d265a5f0%23WindowsVersion%3DWindows_11%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EWindows%20Hello%3C%2FA%3E)%2C%20that%20would%20count%20as%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Factive-directory%2Fauthentication%2Fconcept-mfa-howitworks%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EMulti%20Factor%20Authentication%20(MFA)%3C%2FA%3E%20and%20you%E2%80%99ll%20be%20granted%20secure%20access%20to%20your%20Managed%20Instance.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThough%20I%E2%80%99m%20sure%20you%E2%80%99d%20agree%20this%20is%20great%2C%20you%20may%20wonder%20what%20exactly%20it%20was%20that%20we%E2%80%99ve%20done%20to%20enable%20passwordless%20connection%20between%20Managed%20Instance%20and%20other%20Azure%20resources.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId--2107144477%22%20id%3D%22toc-hId--2107143424%22%3EConfiguring%20Managed%20Instance%20secure%20access%20to%20the%20Azure%20resources%3C%2FH2%3E%0A%3CP%3EManaged%20identity%20has%20been%20used%20for%20some%20time%20to%20provide%20Managed%20Instance%20with%20secure%2C%20passwordless%2C%20and%20RBAC-based%2C%20.%20Now%20we%20are%20expanding%20these%20to%20a%20new%20set%20of%20scenarios.%3C%2FP%3E%0A%3CP%3EOnce%20you%20create%20a%20server%20or%20database%20level%20credential%20with%20Managed%20Identity%2C%20you%20will%20be%20able%20to%20use%20this%20credential%20to%20authenticate%20to%20an%20Azure%20Storage%20account%20while%20doing%20backup%20%2F%20restore%2C%20bulk%20loading%20of%20data%2C%20and%20creating%20a%20server%20audit.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3ECREATE%20CREDENTIAL%20%5Bhttps%3A%2F%2Faadauthdemoshare.blob.core.windows.net%2Fbackups%5D%0AWITH%20IDENTITY%20%3D%20'Managed%20Identity'%0A%0ARESTORE%20FILELISTONLY%20FROM%20URL%20%3D%0A'https%3A%2F%2Faadauthdemoshare.blob.core.windows.net%2Fbackups%2FWideWorldImporters-Standard.bak'%0A%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAnd%20this%20is%20not%20all.%20In%20this%20wave%20of%20improvements%2C%20we%E2%80%99ve%20added%20Managed%20identity%20support%20to%20a%20linked%20server%20feature%2C%20effectively%20extending%20passwordless%20access%20to%20any%20Azure%20SQL%20resource%20with%20Azure%20AD%20authentication%20enabled.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3EEXEC%20master.dbo.sp_addlinkedserver%0A%20%20%20%20%20%3D%20N'MyLinkedServerMSI'%2C%20%0A%20%20%20%20%3DN'MSOLEDBSQL'%2C%20%0A%20%20%20%20%40provstr%3DN'Server%3Dlsdemomi.90f61e4b22c.database.windows.net%3BAuthentication%3DActiveDirectoryMSI'%0A%0AEXEC%20master.dbo.sp_addlinkedsrvlogin%20%0A%20%20%20%20%40rmtsrvname%3DN'MyLinkedServerMSI'%2C%40useself%3DN'False'%0A%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EIf%20the%20linked%20server%20is%20a%20Managed%20Instance%20in%20the%20same%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fazure-sql%2Fmanaged-instance%2Fserver-trust-group-overview%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EServer%20Trust%20Group%3C%2FA%3E%2C%20then%20you%20could%20configure%20this%20linked%20server%20in%20such%20a%20way%20that%20the%20authentication%20context%20flows%20from%20the%20primary%20instance%20to%20the%20linked%20instance.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-sql%22%3E%3CCODE%3EEXEC%20master.dbo.sp_addlinkedserver%20%0A%20%20%20%20%20%3D%20N'MyLinkedServer'%2C%20%0A%20%20%20%20%3DN'MSOLEDBSQL'%2C%20%0A%20%20%20%20%40datasrc%3DN'lsdemomi.90f61e4b22cf.database.windows.net'%0A%0AEXEC%20master.dbo.sp_addlinkedsrvlogin%20%0A%20%20%20%20%40rmtsrvname%3DN'MyLinkedServer'%2C%40useself%3DN'True'%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId-380368356%22%20id%3D%22toc-hId-380369409%22%3ENext%20steps%3C%2FH2%3E%0A%3CP%3EYou%20can%20start%20improving%20the%20security%20of%20your%20Managed%20Instance%20today%3A%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%3ESwitch%20your%20Managed%20Instance%20to%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fazure-sql%2Fdatabase%2Fauthentication-azure-ad-only-authentication%3Ftabs%3Dazure-cli%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EAzure%20AD-only%20authentication%3C%2FA%3E%3C%2FLI%3E%0A%3CLI%3ESwap%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Ft-sql%2Fstatements%2Fcreate-credential-transact-sql%3Fview%3Dsql-server-ver15%23d-creating-a-credential-using-a-sas-token%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3ESAS%20token%20credentials%3C%2FA%3E%20with%20Managed%20Identity%20credentials%3C%2FLI%3E%0A%3CLI%3E%3CA%20href%3D%22https%3A%2F%2Faka.ms%2Fmi-wa-signup%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EApply%3C%2FA%3E%20for%20Windows%20Authentication%20protocol%20for%20Azure%20AD%20users%20limited%20public%20preview%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CH2%20id%3D%22toc-hId--1427086107%22%20id%3D%22toc-hId--1427085054%22%3EGoing%20forward%3C%2FH2%3E%0A%3CP%3EImprovements%20don%E2%80%99t%20stop%20here.%20%26nbsp%3BIn%20the%20near%20term%20we%20plan%20to%20support%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Factive-directory%2Fmanaged-identities-azure-resources%2Fhow-manage-user-assigned-managed-identities%3Fpivots%3Didentity-mi-methods-azp%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Euser-assigned%20managed%20identity%3C%2FA%3E%20as%20a%20primary%20managed%20identity%20for%20Managed%20Instance%2C%20as%20well%20to%20enable%20the%20use%20of%20Managed%20Identity%20for%20passwordless%20authentication%20in%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fazure-sql%2Fmanaged-instance%2Freplication-transactional-overview%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Etransactional%20replication%3C%2FA%3E%20and%20%3CA%20href%3D%22https%3A%2F%2Fazure.microsoft.com%2Fen-us%2Fupdates%2Flog-replay-service-for-azure-sql-managed-instance-in-public-preview%2F%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Elog%20replay%20service%3C%2FA%3E.%3C%2FP%3E%0A%3CP%3EKeep%20an%20eye%20on%20the%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fazure-sql%2Fbg-p%2FAzureSQLBlog%22%20target%3D%22_blank%22%3EAzureSQL%20TechCommunity%20Blog%3C%2FA%3E%20to%20learn%20more%20on%20these%20topics.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-2908166%22%20slang%3D%22en-US%22%3E%3CP%3ENo%20one%20loves%20passwords.%20They%20are%20the%20most%20common%20attack%20target%20and%20require%20extra%20caution%20and%20maintenance%20to%20use%20them%20in%20a%20secure%20way.%20To%20address%20this%2C%20Microsoft%20is%20making%20continued%20investments%20in%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Factive-directory%2Fauthentication%2Foverview-authentication%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EAzure%20Active%20Directory%20(Azure%20AD)%20authentication%3C%2FA%3E%20and%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Factive-directory%2Fmanaged-identities-azure-resources%2Foverview%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3EManaged%20identities%3C%2FA%3E%20to%20pave%20the%20way%20to%20a%20passwordless%20future.%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2908166%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAzure%20SQL%20Security%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EAzure%20SQL%20Managed%20Instance%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EAzure%20SQL%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Co-Authors
Version history
Last update:
‎Nov 02 2021 05:55 AM
Updated by: