Authentication to SQL Server on a VM in the Cloud

I’ve come across a situation recently to run SQL Server Reporting Services (SSRS) on a VM in the cloud. I won’t go in to the details, but hopefully it won’t be needed for too long :). As this is the only server that we need, I don’t see the need for an AD Domain. But wait, we have an Azure AD – can’t we join it to that?

Firstly, a “traditional” Active Directory Domain (AD Domain) is different from Azure Active Directory (AAD). For an AD Domain, you have two options;

  1. Set up Domain Controllers like in the past (either on-prem or as VMs in the cloud).
    1. For this you need to pay for the Domain Controller VM(s) and the time to set them up and maintain them.
  2. Use the managed Azure Active Directory Domain (note, this is not Azure AD) service: https://azure.microsoft.com/en-us/pricing/details/active-directory-ds/.
    1. In our case this would cost around AU$150/month at the time of writing.

With one of the above options in place you can then sync Azure AD with your AD Domain to provide Single Sign-On.

Now we can come back to our server. Windows (Pro/Desktop or Server OSes) can be either in a “workgroup” or “domain-joined”. What’s new is “connecting” to your “work or school”, but more on that later. When you install Windows, it is in a workgroup by default. You can then join that machine to an AD Domain. Note you can still only join an AD Domain, not Azure AD. Seeing as we only have this one server for SSRS and don’t plan on running any more servers, I don’t think it warrants an AD Domain in our case.

Okay, so what about this new “connect to your work or school”? This has only come about in the last few years, and applies to Azure AD. When you “connect to your work” (Azure AD), technically the computer is still in “workgroup” mode. I don’t think you can do this with a server in any case (the option isn’t there as it is for a desktop). And this won’t really help for our situation as authentication works quite differently in Azure AD (hence still the need for AD Domains in general).

Finally, you may have noticed some new Authentication options in SQL Server Management Studio (SSMS):

  1. Active Directory – Universal with MFA support
  2. Active Directory – Password
  3. Active Directory – Integrated
2019-01-03 16_58_26-Mouse Highlight Overlay
SQL Server Management Studio Authentication Options

I believe they only apply to Azure SQL (that is, the managed database service). This is because our SQL Server isn’t aware of our Azure AD, and as far as I’m aware the only way of connecting to it is via federation with an AD Domain. Here is some more info: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-ssms-mfa-authentication.

Have I missed anything? Let me know if the comments below!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s