I would like to connect SQL Server (SSMS) PaaS using Azure AD Multi-Factor Authentication. Look for steps to create
And secondly what default permission will I have have if I connect using SQL Server MFA Authentication
And how can other users login to SSMS using MFA but with only data reader permission to the database.
Hi @NitinG-2179
Configuration steps
1. Configure an Azure Active Directory
2. Configure MFA
3. Configure Azure AD authentication
4. Download SSMS
Refer to the below url's
1. Steps to create - Connect SQL server with MFA authentication
Configure multi-factor authentication for SQL Server Management Studio and Azure AD
Configure Multi-Factor Authentication in Azure SQL Database
2. Other user's granting access to SSMS using MFA
Grant others User access to your database
Troubleshooting problems related to Azure AD authentication with Azure SQL DB
If the Answer is helpful, please click Accept Answer and up-vote, this can be beneficial to other community members.
@learn2skills , Thank you. what would be the default DB level permission if the user connect the SQL server using MFA authentication? Would that be public till we grant any DB level permission? And whats the command to create and grant AD group level permission.
Hey @NitinG-2179 ,
Below are the steps:
1) Add a user in AD admin in SQL server
2) Then login via that AD account through SSMS and execute the below query:
CREATE USER [<<AD Group Name>>] FROM EXTERNAL PROVIDER
exec sp_addrolemember 'db_owner', '<<AD Group Name>>'
Rather than db_owner you can provide other access like data_reader or data_writer etc.
13 people are following this question.
