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

Pre login Handshake or Connection Timeout Period

Image
Service was unable to open new database connection when requested. SqlException: Connection Timeout Expired.  The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement.  This could be because the pre-login handshake failed or the server was unable to respond back in time.  The duration spent while attempting to connect to this server was - [Pre-Login] initialization=16225; handshake=14840; Connection string - Data Source=sqlserverintancenamehere;Initial Catalog=CatalogNamehere;Integrated Security=True;Persist Security Info=False;Max Pool Size=1000;Connect Timeout=20;Load Balance Timeout=120;Packet Size=4096;Application Name=Applicationname hereBusinessLayerHost.exe;Workstation ID=HostnameHere setspn -a MSSQLSvc/hostname.doaminname.net:1433 ServiceAccountNameHere need to run the above command in a command prompt, most of the time you would NOT have permissions to run this, please check with Active Directory Team or Windows Admin te
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