Learn how to Connect to Azure SQL using Active Directory and Grant Access outside users outside of your Organization. This comes in handy when you have a user that sits outside of your organization and they need to log into a SQL environment you have provisioned for them. They will need to be invited as a guest user and then the appropriate access setup so they can login with out needed a hard coded SQL authentication login which creates risk.
1. Allowing Active Directory to Authenticate to SQL Server
- Navigate to your server and Click on “Not Configured” for Active Directory admin inside the Server you are interested in allowing AD access to.
- Set Admin and set your Azure Portal account as admin.
- Leave the check box for Azure Active Directoy authentication only set to unchecked. This way you can continue to use defined crecentianls when needed.
- Be sure to click Save to save changes.
- Head back to SSMS and log in using Azure Active Directory – Universal with MFA or use the correct setting approved by your administrator.
2. Invite User to Your Organization
- Add a new Guest User from the User section in your Azure Portal
- User will get an email to activate their account
3. Adding a External AD user to your Database
- Create a new Query window and run the following commands. and edit the user name and business to the users created in step 2
CREATE USER [username.com#EXT#@business.onmicrosoft.com] FROM EXTERNAL PROVIDER;
ALTER ROLE [db_datareader] ADD MEMBER [username.com#EXT#@business.onmicrosoft.com];
4. Conclusion
And that is all you need to Connect to Azure SQL using Active Directory and Grant Access outside Organization.