Forum Discussion

vikitheolorado's avatar
vikitheolorado
Copper Contributor
Oct 19, 2020

Login to SQL Server in Virtual Machine using Azure Active Directory

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

  • pazdedav's avatar
    pazdedav
    Steel Contributor

    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.

  • Command0r's avatar
    Command0r
    Iron Contributor

    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.

    • pazdedav's avatar
      pazdedav
      Steel Contributor

      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.

      • JeremyLoe's avatar
        JeremyLoe
        Copper Contributor

        pazdedav  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

Resources