How to add Active Directory user or group as login in SQL Server 2016?

Question

Before posting question here I read the below links but these are related to old versions of SQL Server.

How to add Active Directory user group as login in SQL Server

https://dba.stackexchange.com/questions/2572/how-do-i-assign-an-entire-active-directory-group-security-access-in-sql-server-2/2578#2578

I have a .NET application which is connecting to the SQL Server using SQL server authentication.

We have lot of Active Directory users there for our project. Can you please tell me how to create separate login account for each Active Directory users in SQL Server 2016? Or tell me how to add Azure active directory group as login in SQL Server 2016.

Answer 1

As far as I know, it seems that currently on-premises SQL server database does not enable us to add Azure Active Directory group as login. But it is easy to implement in Azure SQL Database and Azure SQL Data Warehouse, this article "Configure and manage Azure Active Directory authentication with SQL Database or SQL Data Warehouse" shows us how to create and populate Azure AD, and then use Azure AD with Azure SQL Database and SQL Data Warehouse. And we could find it enables us to create user from an Azure Active Directory principal, and the Azure_Active_Directory_principal can be an Azure Active Directory user or an Azure Active Directory group.

CREATE USER [AD group name] FROM EXTERNAL PROVIDER;