How to create Azure SQL Admin and can access with "Active Directory-Universal with MFA Support" authentication from SSMS

 To access Azure SQL Database from SSMS through "Active Directory-Universal with MFA Support" authentication please follow the below steps.

1) Create an user Azure Active Directory level with MFA authentication.

a) Search Azure Active Directory in market place

b) Click add user so you can see the below image.

c) Provide initial password and create the user.


d) Now in the below image test user has been created
2) Add this test user to Directory Readers and Directory Writers role and also Global Administraor though the below image does not show it you can add it without missing. Please see the image. Chose those two roles and click on Add button and refresh. And choose authentication method too.


b) Now you could see test user allocated those two roles. and very important to access Azure Active directory users.



c) Now go to authentication provide users or your phone number(if you working at home for practice)
after providing the phone number with +91 XXXXXXXXXX click on save button.



3) Now go to SQL Server(search as 'SQL Server' in market place) not Azure SQL database. Set above user as Admin at SQL Server level. And go to "Azure Active Directory"  under settings tab and Set admin, after you click on set admin it will open another windows and provide your test user in search and the admin should be the above test users that we have created and click save button.


4) Now capture your Azure SQL Server info from SQL Server


5) Now open SQL Server management studio and provide Azure SQL Server name as mentioned below. And also notice you might get error or it will still shows progress without any progress

6) At this stage what you have to do is go to azure portal  switch user and it will ask for MFA authenticating and for new password, provide all those and after that come back to SSMS again access the server , this time it should work.










Comments

Post a Comment

Popular posts from this blog

System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out

I got this error in one of my client's production environment, this type of issues might come up when we are not properly updating statistics. System.Data.SqlClient.SqlException (0x80131904): Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the server is not responding. ---> System.ComponentModel.Win32Exception (0x80004005): The wait operation timed out Workaround: Please run UPDATE STATISTICS command in all the tables on a particular database EXEC sp_updatestats So in my scenario UPDATE STATISTICS on all the tables resolved the issue.
Read more

Always On FailOver Events

Image
We can run below PowerShell and SQL Server commands when we want to find ou when the failover has happened. cls   Get-winEvent -ComputerName   ListerNameHere   -filterHashTable @{logname = 'Microsoft-Windows-FailoverClustering/Operational' ; id = 1641 } |   Where-Object { $_ . Timecreated -like '*05/09/2020*' } | Format-Table -AutoSize -Wrap <# 35201-->timeout alert run this by changing -LogName to System 41-->41 is power shutdown run this in system #> clear Get-EventLog -LogName Application -After ( Get-Date ) . AddHours( -48 ) | Where-Object { $_ . EventID -in ( 1100 , 1074 , 35201,41,1561,7024,1135,7032,1146 )} | Format-Table -AutoSize -Wrap cls  Get-EventLog ` -LogName System |Where-Object{$_.EventID -in (1054,1053,1055,1138,1141,1142,5140,5142,1559,1560,1563,1588,1068,5144,1562,5143,5168,1564,1177)}|Format-Table -AutoSize -Wrap EXEC sp_readerrorlog 0,1,'lease worker' EXEC sp_readerrorlog 0,1,'
Read more

Transparent Data Encryption(TDE) with Master Key and Certificate in SQL Server

Image
/*  There is only one master key, you can not create  multiple */ use master go CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'StrongPasswordHere' GO /*  Creating certificate in the master database, after creating  master key */ use master GO CREATE CERTIFICATE MyFirstCert WITH SUBJECT = 'TDECertificate' ; GO /* Enable databse encryption key but you can not see under this specific user database. */ USE G GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE MyFirstCert GO ALTER DATABASE G SET ENCRYPTION ON;  /*  Backing up certificate */ use master go BACKUP CERTIFICATE MyFirstCert TO FILE = 'C:\Certificates\MyFirstCert' WITH PRIVATE KEY ( FILE = 'C:\Certificates\MyFirstCertKey' , ENCRYPTION
Read more