Our configuration:

  • Windows Server 2016 VM in Azure, running SQL Server (same as on-prem version)
  • Azure Active Directory with users etc.
  • Local Windows 10 PCs are joined to the AAD domain
  • SQL Server is configured for mixed-mode auth.
  • Azure firewall allows connections to the SQL Server from the Internet (for now at least).

One of our developers is trying to setup an SSIS package to run in the server, and is running into the error stating you can only deploy/run SSIS packages from SSMS if you are using Windows authentication. I therefore am now looking into setting up "proper" authentication.

I've looked into Azure AD Domain Services and I can set that up to allow the Windows Server VM to join the domain.

My questions:

  • If I join the Windows Server VM to Azure AD using Azure AD DS, will the Windows authentication work from client PCs, out on the Internet, that are joined to the Azure AD via Windows 10's "Azure AD Join" feature? I noticed that in SSMS, the username is listed as "AzureAD\".
  • If I need to add more infrastructure, I'm obviously looking to minimize cost, so the options I see are as follows - which one of these makes the most sense/actually works?:
    • Deploy a local Windows Server on prem. This is at least a $500 cost (Server Essentials) and would restrict people from only being able to log in while on prem, unless I also setup a VPN service. It also excludes anyone from working on a home machine or basically any machine not joined to the domain - I think?
    • Not sure if this is possible, but, setup a site-to-site VPN with the Azure VNet containing Azure AD DS, and then use a Linux box on prem (free of software cost, could be a very minimal mini-box or even an ARM-based board) to connect local to the Azure VNet. Then, I should be able to theoretically just join all my machines to Azure AD DS as if I had a local server? This would also potentially enable us to restrict access to the SQL Server and just have everyone use the same VPN to connect to Azure and get to the SQL Server. Still doesn't really help people using non-joined machines...
    • Forget AD DS entirely. Not a good idea since we also have Office365 licensing already set up for everyone along with Email addresses, which integrates nicely with the Azure AD.

I've done a lot of work on local Windows Server infrastructure but am still new to Azure and how all of it fits together. Again, the ultimate goal here is to allow SQL Server devs and admins to use Windows Authentication against an SQL Server running in an Azure VM, with the directory being an Azure AD directory.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.