Login to SQL Server in Virtual Machine using Azure Active Directory

%3CLINGO-SUB%20id%3D%22lingo-sub-1794517%22%20slang%3D%22en-US%22%3ELogin%20to%20SQL%20Server%20in%20Virtual%20Machine%20using%20Azure%20Active%20Directory%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1794517%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20Virtual%20Machine%20Windows%20with%20SQL%20Server%20installed.%20How%20can%20I%20use%20Azure%20Active%20Directory%20Account%20to%20login%20to%20the%20SQL%20Server%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1796969%22%20slang%3D%22en-US%22%3ERe%3A%20Login%20to%20SQL%20Server%20in%20Virtual%20Machine%20using%20Azure%20Active%20Directory%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1796969%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F837312%22%20target%3D%22_blank%22%3E%40vikitheolorado%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20could%20be%20wrong%2C%20but%20I%20think%20that%20Microsoft%20SQL%20Server%20(unlike%20Azure%20SQL%20Database)%20does%20not%20support%20Azure%20AD%20authentication.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20theory%2C%20you%20could%20try%20to%20use%20the%20preview%20feature%20and%20sign-in%20to%20Azure%20VM%20using%20AAD%20credentials%20-%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Factive-directory%2Fdevices%2Fhowto-vm-sign-in-azure-ad-windows%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%22%3Ehttps%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Factive-directory%2Fdevices%2Fhowto-vm-sign-in-azure-ad-windows%3C%2FA%3E%26nbsp%3B-%20but%20I%20don't%20believe%20this%20can%20be%20%22extended%22%20to%20MSSQL%20software.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20were%20talking%20about%20Azure%20SQL%20Database%2C%20then%20for%20sure%2C%20Azure%20AD%20is%20the%20primary%20authN%20method.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1797623%22%20slang%3D%22en-US%22%3ERe%3A%20Login%20to%20SQL%20Server%20in%20Virtual%20Machine%20using%20Azure%20Active%20Directory%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1797623%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F837312%22%20target%3D%22_blank%22%3E%40vikitheolorado%3C%2FA%3E%2C%26nbsp%3Bif%20this%20is%20a%20Windows%20Server%20VM%20(and%20I%20assume%20it%20is)%20you%20can%20try%20to%20domain%20join%20it.%20First%2C%20you%20need%20Azure%20AD%20Domain%20Services%20(AD%20DS)%20deployed%2C%20and%20your%20domain%20initialized.%20Then%2C%20you%20can%20join%20a%20specified%20VM%20to%20a%20domain%20following%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Factive-directory-domain-services%2Fjoin-windows-vm%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%22%3Ethis%20instruction%3C%2FA%3E.%20You%20have%20to%20keep%20in%20mind%20this%20is%20just%20a%20domain%20mgmt.%20service%20and%20you%20won't%20find%20'add%20user'%20type%20of%20functionality%20there.%20It%20works%20'on%20top'%20of%20Azure%20AD%20and%20most%20of%20the%20users%20will%20be%20synchronized%20from%20there.%20Once%20you%20enlist%20your%20VM%20to%20the%20AD%20like%20this%2C%20you%20should%20be%20able%20to%20add%20domain%20users%20to%20the%20SQL%20Server%20instance.%20You%20would%20need%20a%20really%20good%20justification%20for%20all%20this%20due%20to%20the%20amount%20of%20work%20that%20needs%20to%20be%20performed.%20Managed%20SQL%20is%20a%20lot%20simpler%20in%20that%20regard%2C%20of%20course.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Visitor

I have Virtual Machine Windows with SQL Server installed. How can I use Azure Active Directory Account to login to the SQL Server?

4 Replies

Hi @vikitheolorado ,

 

I could be wrong, but I think that Microsoft SQL Server (unlike Azure SQL Database) does not support Azure AD authentication.

 

In theory, you could try to use the preview feature and sign-in to Azure VM using AAD credentials - https://docs.microsoft.com/en-us/azure/active-directory/devices/howto-vm-sign-in-azure-ad-windows - but I don't believe this can be "extended" to MSSQL software.

 

If you were talking about Azure SQL Database, then for sure, Azure AD is the primary authN method.

@vikitheolorado, if this is a Windows Server VM (and I assume it is) you can try to domain join it. First, you need Azure AD Domain Services (AD DS) deployed, and your domain initialized. Then, you can join a specified VM to a domain following this instruction. You have to keep in mind this is just a domain mgmt. service and you won't find 'add user' type of functionality there. It works 'on top' of Azure AD and most of the users will be synchronized from there. Once you enlist your VM to the AD like this, you should be able to add domain users to the SQL Server instance. You would need a really good justification for all this due to the amount of work that needs to be performed. Managed SQL is a lot simpler in that regard, of course.

Please keep in mind, that Azure AD Domain Services (AADDS) is not the same service as Azure AD. It is correct that AADDS is getting users and groups objects from Azure AD - there is a synchronization mechanism established once you deploy AADDS to your Azure subscription - but they have quite different specs and purposes:

  • Azure AD is a multi-tenant cloud-based directory and identity management service, designed for massive scale and supporting modern authentication protocols like SAML, OIDC, and OAuth
  • Azure AD Domain Services is a Microsoft-managed traditional Windows Server AD (X.500 standard) that is designed for closed networks, have a hierarchy (OUs), and use Kerberos and NTML protocols

Even if you enable AADDS in your VNet and join your VM (with MSSQL) to that managed domain, you won't be using "modern authentication" protocols from Azure AD, but Integrated Windows Authentication with credentials from AADDS.

@David Pazdera  Do we know if Azure AD auth into SQL Server in Virtual Machine is on the roadmap?

I can see the options to login via Azure AD but the commands to create the login or user to not work yet.

 

Thanks,

Jeremy