-> I am planning to create a series of blogs on Azure Active Directory and how SQL server can be connected using Azure AD.
Other Blogs in this Series,
-> Integrate Azure Active Directory with Active Directory in Azure Virtual Machine
-> Create a login in Azure Virtual Machine Active Directory and Sync it to Azure Active Directory
-> Login to Azure Virtual machine using Azure Active Directory credentials-> In this blog we will connect an Azure SQL Database using “Azure Active Directory – Password” / “Azure Active Directory – Universal with MFA”.
-> Creating an Azure SQL Database,
Connecting to Azure SQL Database using “Azure Active Directory – Password”
TITLE: Connect to Server
Cannot connect to jbswiki.database.windows.net.
ADDITIONAL INFORMATION:
One or more errors occurred. (mscorlib)
One or more errors occurred. (mscorlib)
AADSTS50055: The password is expired.
Trace ID: bb63ac8d-f575-4470-863a-3bbd8e04c900
Correlation ID: f6f8ac22-4c23-404e-8f94-9835cf279550
Timestamp: 2021-03-08 13:27:21Z (System.Data)
BUTTONS:
OK
-> Open a “New InPrivate Window” from edge or a “New Incognito window” from chrome and navigate to http://portal.azure.com/.
-> Once the password are set. We will move past that error.
TITLE: Connect to Server
Cannot connect to jbswiki.database.windows.net.
ADDITIONAL INFORMATION:
One or more errors occurred. (mscorlib)
One or more errors occurred. (mscorlib)
AADSTS900021: Requested tenant identifier ‘00000000-0000-0000-0000-000000000000’ is not valid. Tenant identifiers may not be an empty GUID.
Trace ID: 9da84098-11f2-4465-862a-27e2d6cdb200
Correlation ID: df8a8735-bfe0-46aa-98eb-095d49cbe2fd
Timestamp: 2021-03-08 09:01:18Z (System.Data)
BUTTONS:
OK
-> Open SQL Database JBSWIKI from Azure portal and then click on “Server name : jbswiki.database.windows.net”,
-> Click on “Active Directory admin”,
-> Click on “Set admin”,
-> Select the required login,
-> Click on “Save”,
-> Once after the above change we should be able to login without any issues.
-> There are scenarios where you might get below error,
TITLE: Connect to Server
Cannot connect to jbswiki.database.windows.net.
ADDITIONAL INFORMATION:
One or more errors occurred. (mscorlib)
One or more errors occurred. (mscorlib)
AADSTS50076: Due to a configuration change made by your administrator, or because you moved to a new location, you must use multi-factor authentication to access ‘022907d3-0f1b-48f7-badc-1ba6abab6d66’.
Trace ID: 722d3a34-5969-499f-b3f8-c08366ca0700
Correlation ID: f8e79ae2-315e-4415-a2a4-693460fa4072
Timestamp: 2021-03-08 09:50:49Z (System.Data)
BUTTONS:
OK
-> In this case we should use “Azure Active Directory – Universal with MFA” instead of “Azure Active Directory – Password”,
-> We have added user as administrators for Azure SQL database. We can use below approach to add users using TSQL for non-administrator users,
use [jbswiki]
go
CREATE USER [Karthik@jvivek2k1yahoo.onmicrosoft.com] FROM EXTERNAL PROVIDER WITH DEFAULT_SCHEMA=[dbo];
go
ALTER ROLE db_datareader ADD MEMBER [Karthik@jvivek2k1yahoo.onmicrosoft.com] 
-> We cannot see security folder anymore. Lets try a simple select and check if it works.
Thank You,
Vivek Janakiraman
Disclaimer:
The views expressed on this blog are mine alone and do not reflect the views of my company or anyone else. All postings on this blog are provided “AS IS” with no warranties, and confers no rights.
2 thoughts on “Connecting Azure SQL Database using Azure Active Directory – Password / Azure Active Directory – Universal with MFA”