{"id":1235,"date":"2022-02-13T20:08:58","date_gmt":"2022-02-13T20:08:58","guid":{"rendered":"http:\/\/jackofalltradesmasterofsome.com\/blog\/?p=1235"},"modified":"2022-03-15T00:50:24","modified_gmt":"2022-03-15T00:50:24","slug":"connect-to-azure-sql-using-active-directory-and-grant-access-outside-organization","status":"publish","type":"post","link":"https:\/\/jackofalltradesmasterofsome.com\/blog\/2022\/02\/13\/connect-to-azure-sql-using-active-directory-and-grant-access-outside-organization\/","title":{"rendered":"Connect to Azure SQL using Active Directory and Grant Access outside Organization"},"content":{"rendered":"\n<p>Learn how to Connect to <a href=\"http:\/\/desmasterofsome.com\/blog\/2020\/04\/13\/modern-data-architecture-part-4-setting-up-a-sql-server\/\">Azure SQL<\/a> using <a href=\"https:\/\/docs.microsoft.com\/en-us\/azure\/azure-sql\/database\/authentication-aad-configure?tabs=azure-powershell\">Active Directory<\/a> 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. <\/p>\n\n\n\n<h2 class=\"wp-block-heading\">1. Allowing Active Directory to Authenticate to SQL Server<\/h2>\n\n\n\n<ul class=\"wp-block-list\"><li>Navigate to your server and Click on \u201cNot Configured\u201d for Active Directory admin inside the Server you are interested in allowing AD access to.<\/li><\/ul>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"573\" height=\"86\" src=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2022\/02\/image.png\" alt=\"\" class=\"wp-image-1237\" srcset=\"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2022\/02\/image.png 573w, https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2022\/02\/image-300x45.png 300w\" sizes=\"auto, (max-width: 573px) 100vw, 573px\" \/><\/figure>\n\n\n\n<ul class=\"wp-block-list\"><li>Set Admin and set your Azure Portal account as admin.<\/li><\/ul>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"567\" height=\"178\" src=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2022\/02\/image-1.png\" alt=\"\" class=\"wp-image-1238\" srcset=\"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2022\/02\/image-1.png 567w, https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2022\/02\/image-1-300x94.png 300w\" sizes=\"auto, (max-width: 567px) 100vw, 567px\" \/><\/figure>\n\n\n\n<ul class=\"wp-block-list\"><li>Leave the check box for Azure Active Directoy authentication only set to unchecked. This way you can continue to use defined crecentianls when needed.<\/li><\/ul>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"975\" height=\"265\" src=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2022\/02\/image-2.png\" alt=\"\" class=\"wp-image-1239\" srcset=\"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2022\/02\/image-2.png 975w, https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2022\/02\/image-2-300x82.png 300w, https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2022\/02\/image-2-768x209.png 768w\" sizes=\"auto, (max-width: 975px) 100vw, 975px\" \/><\/figure>\n\n\n\n<ul class=\"wp-block-list\"><li>Be sure to click Save to save changes.<\/li><\/ul>\n\n\n\n<ul class=\"wp-block-list\"><li>Head back to SSMS and log in using Azure Active Directory \u2013 Universal with MFA or use the correct setting approved by your administrator.<\/li><\/ul>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"744\" height=\"486\" src=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2022\/02\/image-3.png\" alt=\"\" class=\"wp-image-1240\" srcset=\"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2022\/02\/image-3.png 744w, https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2022\/02\/image-3-300x196.png 300w\" sizes=\"auto, (max-width: 744px) 100vw, 744px\" \/><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\">2. Invite User to Your Organization<\/h2>\n\n\n\n<ul class=\"wp-block-list\"><li>Add a new Guest User from the User section in your Azure Portal<\/li><\/ul>\n\n\n\n<figure class=\"wp-block-image size-large\"><img loading=\"lazy\" decoding=\"async\" width=\"778\" height=\"261\" src=\"http:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2022\/03\/image-11.png\" alt=\"\" class=\"wp-image-1258\" srcset=\"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2022\/03\/image-11.png 778w, https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2022\/03\/image-11-300x101.png 300w, https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-content\/uploads\/2022\/03\/image-11-768x258.png 768w\" sizes=\"auto, (max-width: 778px) 100vw, 778px\" \/><\/figure>\n\n\n\n<ul class=\"wp-block-list\"><li>User will get an email to activate their account<\/li><\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">3. Adding a External AD user to your Database <\/h2>\n\n\n\n<ul class=\"wp-block-list\"><li>Create a new Query window and run the following commands. and edit the user name and business to the users created in step 2<\/li><\/ul>\n\n\n\n<p>CREATE USER [username.com#EXT#@business.onmicrosoft.com] FROM EXTERNAL PROVIDER;<\/p>\n\n\n\n<p>ALTER ROLE [db_datareader] ADD MEMBER [username.com#EXT#@business.onmicrosoft.com];<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">4. Conclusion <\/h2>\n\n\n\n<p>And that is all you need to Connect to Azure SQL using Active Directory and Grant Access outside Organization.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>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 [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":1240,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-1235","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/posts\/1235","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/comments?post=1235"}],"version-history":[{"count":2,"href":"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/posts\/1235\/revisions"}],"predecessor-version":[{"id":1259,"href":"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/posts\/1235\/revisions\/1259"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/media\/1240"}],"wp:attachment":[{"href":"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/media?parent=1235"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/categories?post=1235"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/jackofalltradesmasterofsome.com\/blog\/wp-json\/wp\/v2\/tags?post=1235"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}