Azure SQL: Tightening the Security using Integrated Authentication only

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 entirely block any SQL User from login to your Azure SQL Database. Only integrated authentication will be accepted. Only AD users will be accepted on Azure SQL DB if you enable this feature.

Enabling Azure Active Directory Authentication

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

The new feature appeared in the same place we need to enable the integrated authentication.

 

Let’s make some tests to identify all the challenges we may face if we enable this feature.

1. Let’s set the administrator of our Azure SQL. I have a group for SQL Admins, that’s the best way to manage the administrators.

A) Click the button Set Admin

B) Select the administrator. It may be a user or a group.

C) Click the Save button

 

 

Creating some SQL Users

Creating SQL users with DB_Owner permissions will lead to some challenging scenarios. Let’s explore these situations to fully understand our challenges.

2) First, execute the script below to create some users

CREATE USER jonh WITH password=‘6964xpahmW’

CREATE USER jane WITH password=‘6964xpahmW’

ALTER role db_owner ADD member jonh

ALTER role db_owner ADD member jane 

 

3) You can already login as Jonh. It’s important to mind this is a contained database, so we need to specify the database name when login in.

 

 

4) Execute the script below to create some objects

CREATE SCHEMA jschema
go

CREATE TABLE jschema.jtesttable
  (
     id       INT IDENTITY(1, 1) NOT NULL PRIMARY KEY,
     [values] NUMERIC (15, 2)
  )
go 

 

5) Enable AD Authentication Only

 

 

6) Try to login as Jonh. It will fail, because now we can only login using Azure AD users

 

 

7) Login using an Azure AD user

8) Execute the following script to identify the owner of the jsschema

SELECT ss.NAME  [schema],
       sdp.NAME [user]
FROM   sys.schemas ss
       INNER JOIN sys.database_principals sdp
               ON ss.principal_id = sdp.principal_id 

 

 

Jonh can’t 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.

 

Auditing this configuration

 

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. 

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’t. 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.

My blog about policies provide a more complete explanation about this.

 

Creating the Policy

Our challenge is to correctly build the policy to check this new feature.

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

 

 

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

 

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

 

 

This will be our policy:

    “policyRule”: {

      “if”: {

            “field”: “type”,

            “equals”: “Microsoft.Sql/servers”

      },

      “then”: {

        “effect”: “auditIfNotExists”,

        “details”: {

          “type”: “Microsoft.Sql/servers/azureADOnlyAuthentications”,

          “existenceCondition”: {

                “field”: “Microsoft.Sql/servers/azureADOnlyAuthentications/azureADOnlyAuthentication”,

                “equals”: true

          }

        }

      }

Some details about how the policy is built:

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

Policy Result

After creating and assigning a policy, I enabled this option in a single SQL Server in my subscription and checked the policy compliance result.

What’s important is how we are able to easily identify all SQL Servers non-compliant with the policy across the entire company. We could be talking about a worldwide company.

 

Conclusion

This is an important new feature on Azure SQL and together with the policies, we can tighten the security on the entire company environment.

 

The post Azure SQL: Tightening the Security using Integrated Authentication only appeared first on Simple Talk.

from Simple Talk https://ift.tt/3zLvWy8
via