How should Azure Synapse Analytics Serverless SQL pool be configured to support end-to-end AD authentication and authorization?

Question

I've been trying for a few days to get end-to-end AD authenticated access established for the Azure Synapse Serverless SQL Pool with some progress but no end-to-end workable solution until today.

I tried creating a SQL Server login for an AD security group using its domain, e.g.

CREATE LOGIN [UG-DataAccess-Confidential-RO@domain.com] FROM EXTERNAL PROVIDER;

and received the error

Principal 'UG-DataAccess-Confidential-RO@domain.com' could not be found or this principal type is not supported.

Answer 1

Today, I fortunately ran across [this helpful article]: https://www.mssqltips.com/sqlservertip/6702/sql-server-windows-authentication-with-users-and-groups/

Which led to the following configuration that works great with Azure Synapse SQL Pool and I imagine SQL Server more generally:

AD Security Group: UG-DataAccess-Confidential-RO

Use master;
go

-- Step 1:  Create a login for the desired security group that should align with the 
--          sensitivity of the data being accessed
--
--          NOTE: logins to SQL Server that use AD security groups only need the 
--          DISPLAY NAME of the group and should not contain the domain.  I assume 
--          that the domain is left off since authentication is against user accounts 
--          while authorization checks group membership. Since authentication occurs first, 
--          the domain is already known by the time authorization happens.  

CREATE LOGIN [UG-DataAccess-Confidential-RO] FROM EXTERNAL PROVIDER;
GO

-- Step 2:  Create a custom server role in the master DB (standard roles cannot be modified with 
--          Azure Serverless SQL) and grant the required SQL privileges to it in order to ensure that
--          users who are members of the security group will have these privileges once the
--          security group has been added as a member of this new role in SQL Server

CREATE SERVER ROLE [custom_role_reader]
GO
 
-- Note: these grants are too liberal and need to be reduced further for tighter security
GRANT CONNECT ANY DATABASE  TO [custom_role_reader]
GRANT CONNECT SQL           TO [custom_role_reader]
GRANT VIEW ANY DATABASE     TO [custom_role_reader]
GRANT VIEW ANY DEFINITION   TO [custom_role_reader]
GRANT VIEW SERVER STATE     TO [custom_role_reader]
GO

-- Step 3.  Add the security group to the new server role to enable login for ANY users who are
--          members of the AD security group
ALTER SERVER ROLE [custom_role_reader] ADD MEMBER [UG-DataAccess-Confidential-RO]
GO

-- Step 4.  Change to the database of interest - in this example, we use a demo database
use demoDB;
GO

-- Step 5.  Create a demo database user for the demo database that maps to the SQL login associated with the AD security group 
drop user [SqlReader]
GO

CREATE USER [SqlReader] FOR LOGIN [UG-DataAccess-Confidential-RO]
GO

-- Step 6.  Create a demo database role and grant the required minimum privileges to it.
--          Then add the new demo db user as a member of the new demo db database role
drop role [db_sql_reader]
go

CREATE ROLE [db_sql_reader]
GO

GRANT SELECT ON SCHEMA::curated TO [db_sql_reader]
GO

alter role [db_sql_reader] add member [SqlReader]
GO

-- Step 7.  FOR AZURE SYNAPSE ANALYTICS ONLY
--          Ensure Gen2 Storage of data lake ACLs provide read access to the AD security group for all files that are backing external table definitions. Ensure the security group has read/execute on all Gen2 directories in the data lake from the container all the way down to the files in the data lake, e.g. parquet, CSVs, etc.
-- 
--          Users who are members of the AD security group should now be able to login 
--          to serverless SQL and execute queries against the tables in the demo DB

Note that this solution doesn't require any special Azure RBAC roles for members of the AD security group, only proper ACL management within the data lake and the SQL permissions described above. This prevents us from having to deal with creating scoped database credentials and it also aligns with MS security best practices for Synapse from what I can tell from the MS docs.