Balancing the needs for security and convenience for user accounts within businesses/organisations can be a real challenge. Typically, it is preferred that individuals have separate accounts, each with their own unique and complex passwords, for the various services that they need to access as part of their daily work. In reality, such an approach will almost always lead to poor password security, increasing the risk for an attacker to more easily penetrate an environment using a single password. In this case, looking at a solution that supports Single Sign-On (SSO) capability, such as Azure Active Directory (AAD), can help not just minimise the proliferation of multiple, weak login identities, but allow you to enforce consistent policies for password complexities, expiry and also introduce additional security steps via Multi-Factor Authentication (MFA). And, what’s even better, these solutions can increase convenience for end users. For me, that is undoubtedly the best kind of solution. 🙂

Microsoft, as you may expect, embed SSO capability within a vast majority of their products, and Azure SQL Server/Database is no exception to this rule. With minimal configuration, you can add AAD users onto an Azure SQL database and manage them as if they were a standard SQL Server user account. Traditionally, this would be done by defining each user account that needs access at the database level - not so bad if you are dealing with a handful of users, but what if you need to grant hundreds of users access to a single Azure SQL database? Also, if you are working across multiple AAD tenants (to, for example, manage changes across your various environments), the solution starts to fall on its face straight away. This is because you must specify the full, unique user name of each AAD account that you want to add to the database - which, as we have seen previously on the blog, will always be different, even if you are using guest accounts across multiple tenants. Fortunately, there is a way to get around this, using what looks like an undocumented feature. AAD Security Groups can be added as login principals to an Azure SQL database in the same manner as AAD user accounts and - most critically - granted the required permissions for your database. For managing database access at scale, it really is a worthwhile feature to have at our disposal and, in addition, can be extremely helpful if you are deploying your database onto separate AAD tenants, as all you need to do is simply ensure a security group with the same name exists within each tenant with its required members.

To get started using this feature, you need first to ensure that you have the following setup within your Azure environment:

• Rather obviously, you need an Azure SQL Server resource with an associated database. In addition, you need to make sure that you have enabled an Active Directory admin at the server level - this will typically be a two-part step, where you first define an appropriate AAD account for this and then associate it with your server instance.
• An appropriate security group object must exist already within the AAD tenant. The example screenshot below illustrates the settings that are needed for this, although you can (optionally) add in any members once the security group has been created:

  • 

When all of the above is sorted, login to your SQL Server instance using the Global AAD Administrator account and run the following script against your target database - be sure to change the user account name so that it matches exactly against the security group that exists within your AAD tenant:

CREATE USER [AAD SSO Test]
  FROM EXTERNAL PROVIDER
  WITH DEFAULT_SCHEMA = dbo

GO

GRANT CONNECT TO [AAD SSO Test]

GO

EXEC sp_addrolemember 'db_datawriter', 'AAD SSO Test';

GO

EXEC sp_addrolemember 'db_datareader', 'AAD SSO Test';

GO

If you get an error message similar to the one below, then you may need to check that the user name matches exactly against what is within the AAD tenant:

We can verify that the account has been created successfully by running the following query, which also confirms that the account type is that of EXTERNAL_GROUP as opposed to EXTERNAL_USER, for users created from a single AAD account:

SELECT name, type_desc, authentication_type_desc
FROM sys.database_principals
WHERE type_desc = 'EXTERNAL_GROUP'

[snippet id=“778”]

All that’s left is to perform a test login using SSMS; in this case, we use a secondary janedoe account that has been added to the Security Group on Azure:

To login successfully, we need to make sure a couple of options are configured within SSMS:

• Naturally, we need to ensure that the correct authentication method is selected, which is dictated by the overall configuration of your AAD tenant:
  • If you are using Active Directory Federation Services (ADFS), then select the Active Directory - Integrated option. The user will not be prompted to enter any credentials and should instead pick this up from the local domain account.
  • If the account in question is enabled for MFA, then select Active Directory - Universal with MFA Support and enter the email address of the account in question. This will launch a separate pop-up window, with the appropriate MFA challenge (where applicable) after logging in.
  • If neither of the above applies or if in doubt, then select the Active Directory - Password option, entering your login details in the appropriate fields.
• Since the account will be created as a contained database user, we need to select the Connection Properties tab and ensure the name of the database we are connecting to is entered in the Connect to database field - so, in the example below, the database name used is JGTest:

  

All being well, our janedoe account can log in without issue and, per the earlier script, should have full read/write access to the entire dbo schema in the database:

Conclusions or Wot I Think

Managing SSO at scale can prove to be a real challenge, so having features like the one described in this post can be a real boon in neatly managing this complexity. As mentioned previously, it doesn’t appear that the feature is well-documented either, which is why I thought I would share it out to increase awareness of it. With minimal setup involved and - most crucially - without the need to perform frequent updates to your database whenever an individual leaves an organisation, you can implement a role-based security model for your Azure SQL databases, that can be freely modified within the Azure portal at will with zero-disruption to your database users or application.