question

NitinG-2179 avatar image
0 Votes"
NitinG-2179 asked ·

Connecting SQL Server with MFA Authnetication and limited permission

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.

azure-sql-database
· 1
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@NitinG-2179 - Let us know if you need any further help.

0 Votes 0 ·
learn2skills avatar image
0 Votes"
learn2skills answered ·

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.


· 1 ·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@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.

0 Votes 0 ·
NandanHegde-7720 avatar image
0 Votes"
NandanHegde-7720 answered ·

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.

·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.