{"id":91389,"date":"2021-06-21T17:00:10","date_gmt":"2021-06-21T17:00:10","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=91389"},"modified":"2021-06-18T22:16:12","modified_gmt":"2021-06-18T22:16:12","slug":"azure-sql-tightening-the-security-using-integrated-authentication-only","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/blogs\/azure-sql-tightening-the-security-using-integrated-authentication-only\/","title":{"rendered":"Azure SQL: Tightening the Security using Integrated Authentication only"},"content":{"rendered":"

This new feature was already being expected for a while. Out of the blue, while I was delivering an online class, there it was, looking to me. Waiting for me to figure out how it works and explain it to my students.<\/p>\n

Luckily, the feature is very simple: You click a checkbox and you will entirely block any SQL User<\/strong> from login to your Azure SQL Database<\/strong>. Only integrated authentication will be accepted. Only AD<\/strong> users will be accepted on Azure SQL DB<\/strong> if you enable this feature.<\/p>\n

Enabling Azure Active Directory Authentication<\/h2>\n

First, we need to enable Azure Active Directory Authentication<\/strong>. This usually is one of the first demonstrations I make in the technical sessions, when I talk about Azure SQL<\/strong>. To enable AAD authentication<\/strong>\u00a0we need to set an AAD<\/strong> admin for our Azure SQL Server<\/strong>.<\/p>\n

The new feature appeared in the same place we need to enable the integrated authentication.<\/p>\n

 <\/p>\n

<\/p>\n

Let’s make some tests to identify all the challenges we may face if we enable this feature.<\/p>\n

    \n
  1. Let’s set the administrator of our Azure SQL. I have a group for SQL Admins, that\u2019s the best way to manage the administrators.<\/li>\n<\/ol>\n

    A) Click the button Set Admin<\/strong><\/p>\n

    B) Select the administrator. It may be a user or a group.<\/p>\n

    C) Click the Save<\/strong> button<\/p>\n

     <\/p>\n

    <\/p>\n

     <\/p>\n

    <\/p>\n

    Creating some SQL Users<\/h2>\n

    Creating SQL users with DB_Owner<\/strong> permissions will lead to some challenging scenarios. Let’s explore these situations to fully understand our challenges.<\/p>\n

    2) First, execute the script below to create some users<\/p>\n

    CREATE<\/span>\u00a0USER<\/span>\u00a0jonh<\/span>\u00a0WITH<\/span>\u00a0password<\/span>=<\/span>‘6964xpahmW’<\/span> <\/p>\n

    CREATE<\/span>\u00a0USER<\/span>\u00a0jane<\/span>\u00a0WITH<\/span>\u00a0password<\/span>=<\/span>‘6964xpahmW’<\/span> <\/p>\n

    ALTER<\/span>\u00a0role<\/span>\u00a0db_owner<\/span>\u00a0ADD<\/span>\u00a0member<\/span>\u00a0jonh<\/span> <\/p>\n

    ALTER<\/span>\u00a0role<\/span>\u00a0db_owner<\/span>\u00a0ADD<\/span>\u00a0member<\/span>\u00a0jane<\/span>\u00a0 <\/span><\/div>\n

     <\/p>\n

    3) You can already login as Jonh. It\u2019s important to mind this is a contained database<\/a>, so we need to specify the database name when login in.<\/p>\n

     <\/p>\n

    <\/p>\n

    <\/p>\n

     <\/p>\n

    4) Execute the script below to create some objects<\/p>\n

    CREATE<\/span>\u00a0SCHEMA<\/span>\u00a0jschema<\/span>
    \ngo<\/span> <\/p>\n

    CREATE<\/span>\u00a0TABLE<\/span>\u00a0jschema<\/span>.<\/span>jtesttable<\/span>
    \n\u00a0\u00a0(<\/span>
    \n\u00a0\u00a0\u00a0\u00a0\u00a0id<\/span>\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0INT<\/span>\u00a0IDENTITY<\/span>(<\/span>1<\/span>,<\/span>\u00a01<\/span>)<\/span>\u00a0NOT<\/span>\u00a0NULL<\/span>\u00a0PRIMARY<\/span>\u00a0KEY<\/span>,<\/span>
    \n\u00a0\u00a0\u00a0\u00a0\u00a0[values]<\/span>\u00a0NUMERIC<\/span>\u00a0(<\/span>15<\/span>,<\/span>\u00a02<\/span>)<\/span>
    \n\u00a0\u00a0)<\/span>
    \ngo<\/span>\u00a0 <\/span><\/div>\n

     <\/p>\n

    5) Enable AD Authentication<\/strong>\u00a0Only<\/strong><\/p>\n

     <\/p>\n

    <\/p>\n

     <\/p>\n

    6) Try to login as Jonh. It will fail, because now we can only login using Azure AD users<\/strong><\/p>\n

     <\/p>\n

    <\/p>\n

     <\/p>\n

    7) Login using an Azure AD user<\/p>\n

    8) Execute the following script to identify the owner of the jsschema<\/strong><\/p>\n

    SELECT<\/span>\u00a0ss<\/span>.<\/span>NAME<\/span>\u00a0\u00a0[schema]<\/span>,<\/span>
    \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0sdp<\/span>.<\/span>NAME<\/span>\u00a0[user]<\/span>
    \nFROM<\/span>\u00a0\u00a0\u00a0sys<\/span>.<\/span>schemas<\/span>\u00a0ss<\/span>
    \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0INNER<\/span>\u00a0JOIN<\/span>\u00a0sys<\/span>.<\/span>database_principals<\/span>\u00a0sdp<\/span>
    \n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0ON<\/span>\u00a0ss<\/span>.<\/span>principal_id<\/span>\u00a0=<\/span>\u00a0sdp<\/span>.<\/span>principal_id<\/span>\u00a0 <\/span><\/div>\n

     <\/p>\n

    <\/p>\n

     <\/p>\n

    Jonh can\u2019t login anymore, but he is still the owner of a schema inside the database. I recommend this feature to everyone but take care to not break anything.<\/p>\n

     <\/p>\n

    Creating the Policy<\/h2>\n

    Our challenge\u00a0is to correctly build the policy to check this new feature.<\/p>\n

    First, we need to understand how this option is implemented on the SQL Server<\/strong> object. We can generate the a template of the object by clicking on Export Template<\/strong> option under Automation<\/strong> on the left side blade of the Azure<\/strong>\u00a0SQL Server<\/strong>.<\/p>\n

     <\/p>\n

    <\/p>\n

     <\/p>\n

    On this template we will find the information we need to build a policy. The first important information we discover, is the Type<\/strong> of the SQL Server<\/strong>, which is Microsoft.Sql\/servers<\/strong>. In fact, this would not be so difficult to discover on the documentation as well.<\/p>\n

     <\/p>\n

    \"Graphical<\/p>\n

    Another important information is how this configuration is deployed. You may notice this configuration has its own object of Type<\/strong> Microsoft.Sql\/azureADonlyAuthentications<\/strong> . This object has a collection of properties and one of them is azureADonlyAuthentication<\/strong> . This is the property our policy will need to check.<\/p>\n

     <\/p>\n

    <\/p>\n

     <\/p>\n

    This will be our policy:<\/p>\n

    \u00a0\u00a0\u00a0\u00a0“policyRule”:\u00a0{<\/p>\n

    \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0“if”:\u00a0{<\/p>\n

    \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0“field”:\u00a0“type”,<\/p>\n

    \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0“equals”:\u00a0“Microsoft.Sql\/servers”<\/p>\n

    \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0},<\/p>\n

    \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0“then”:\u00a0{<\/p>\n

    \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0“effect”:\u00a0“auditIfNotExists”,<\/p>\n

    \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0“details”:\u00a0{<\/p>\n

    \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0“type”:\u00a0“Microsoft.Sql\/servers\/azureADOnlyAuthentications”,<\/p>\n

    \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0“existenceCondition”:\u00a0{<\/p>\n

    \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0“field”:\u00a0“Microsoft.Sql\/servers\/azureADOnlyAuthentications\/azureADOnlyAuthentication”,<\/p>\n

    \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0“equals”:\u00a0true<\/p>\n

    \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}<\/p>\n

    \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}<\/p>\n

    \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0}<\/p>\n

    Some details about how the policy is built:<\/p>\n

      \n
    • The main policy criteria is the type, Microsoft.Sql\/servers<\/strong> .<\/li>\n
    • The effect is auditIfNotExists<\/strong>. It will check the existence of the type Microsoft.Sql\/servers\/azureADOnlyAuthentications<\/strong>.<\/li>\n
    • The existence condition will check if the property is enabled, ensuring only Azure AD Authentication <\/strong>is accepted by SQL Server<\/strong>. If the property is not enabled, the auditIfNotExists<\/strong> will not find the object. The policy will report the SQL Server<\/strong> as non-compliant.<\/li>\n<\/ul>\n

      Policy Result<\/h2>\n

      After creating and assigning a policy, I enabled this option in a single SQL Server<\/strong> in my subscription and checked the policy compliance result.<\/p>\n

      What\u2019s important is how we are able to easily identify all SQL Servers<\/strong> non-compliant with the policy across the entire company. We could be talking about a worldwide company.<\/p>\n

       <\/p>\n

      \"Graphical<\/p>\n

      Conclusion<\/h2>\n

      This is an important new feature on Azure SQL<\/strong> and together with the policies, we can tighten the security on the entire company environment.<\/p>\n

       <\/p>\n","protected":false},"excerpt":{"rendered":"

      This new feature was already being expected for a while. Out of the blue, while I was delivering an online class, there it was, looking to me. Waiting for me to figure out how it works and explain it to my students. Luckily, the feature is very simple: You click a checkbox and you will……<\/p>\n","protected":false},"author":50808,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[2],"tags":[143575,143573,136322,143574],"coauthors":[6810],"class_list":["post-91389","post","type-post","status-publish","format-standard","hentry","category-blogs","tag-azure-ad","tag-azure-policies","tag-azure-sql","tag-governance"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/91389","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/50808"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=91389"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/91389\/revisions"}],"predecessor-version":[{"id":91402,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/91389\/revisions\/91402"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=91389"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=91389"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=91389"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=91389"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}

    Auditing this configuration<\/h2>\n

     <\/p>\n

    This configuration is exactly the kind of configuration we may would like to keep enabled in all our Azure SQLs for security purposes. As a result, we need to identify all servers with this configuration enabled or not, ensuring the safety of our cloud environment.\u00a0<\/p>\n

    We can audit this configuration on the entire company, or only parts of the company. Of course, in a big corporation, there may be areas, projects, that can use this configuration and others can\u2019t. We can solve this problem by breaking down the services in different subscriptions and breaking down the subscriptions in different management groups. By doing so, we can use Azure Policies on some management groups or subscriptions and not others.<\/p>\n

    My blog about policies<\/a> provide a more complete explanation about this.<\/p>\n

     <\/p>\n