Connecting SQL Server with MFA Authnetication and limited permission

Nitin G 1 Reputation point
2021-03-01T06:56:10.18+00:00

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

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. SUNOJ KUMAR YELURU 13,921 Reputation points MVP
    2021-03-01T08:22:43.773+00:00

    Hi @Nitin G
    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 person found this answer helpful.
  2. Nandan Hegde 29,886 Reputation points MVP
    2021-03-02T08:01:44.43+00:00

    Hey @Nitin G ,
    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.

    0 comments