Quick Track: Beginner’s Guide to Azure SQL- Authentication and Access Management

Picture by George Becker

Authentication and Authorization are the two things that we need to get familiar with when we learn about the Access management.

Authentication is proving that we are the same user who we say we are. Below are the authentication methods available in Azure.

SQL Authentication: Azure SQL Database and for the Managed instance (MI) supports SQL authentication. This is just like any regular SQL Authentication account with username and the password. When you create the server, you will need to create the SQL authentication account which will become the server admin that will have database owner on all the databases. This account can create other SQL authentication accounts on the server as well.

Azure Active Directory: There is a centralized location for the identities and permissions to database users to be stored for the Azure active directory authentication. As the password is only stored at one location, it becomes easier for the administrators to manage the identities and for maintaining the passwords. This authentication is supported for Azure SQL database, Managed instance (MI) and Azure synapse analytics. The Active directory admin will become the server admin where to create other AD logins, only the Active directory admin can create the other AD accounts having permissions to Azure SQL database. Multi factor authentication option is available for this option. Authentication app can be used as the multi factor authentication where it provides phone call/text message/authentication app to confirm the identity. This authentication type will use the contained database users to connect to the database to access the database objects.

Active directory will support the users with the login id and password, Integrated (single sign-on) authentication used when there is a trust built between the Active directory and the Azure AD using windows authentication. Universal/interactive with multi factor authentication is where the password is generated dynamically. There is a token authentication where a token is generated from Azure AD and as we pass this token to the SQL. Source: here.

One of the important point to remember is, the Azure active directory admin has to create the Azure active directory login. SQL authentication logins cannot be created by Azure AD logins. The other type of users that the Azure AD supports are individual user accounts, group accounts, service principles.

To create the Azure AD account, you can create the account by running on virtual master database. please see sample code to create Azure AD login here.

Lets learn a bit about the differences in the logins and users in between the managed instance and Azure SQL database

Managed Instance: You can create the Azure AD server admin along with the SQL server admin. You can create the SQL logins even when you only enable Azure AD only authentication but you cant connect until you enable the SQL authentication. You can create the SQL or the Azure AD logins, Database users and database contained users. You can also create the Server trust group for the distribution transaction scenarios between the two instances.

Azure SQL Database: You can create the Azure AD Server admin along with SQL logins. Additionally you can have roles like loginmanager and dbmanager for limited server admins. You can create the users associated with the SQL logins, create contained users including Azure AD. To create these contained users, the user must be login using the Azure AD server admin.

There are two configuration options for the authentication:

Cloud only option- where you can create the AD azure accounts with out having them connected to the on-prem servers or you can federate services with single sign-in option, Azure AD storing password as a Hash and Azure SQL passthrough with single sign-on is where the password stored in on-prem.

Azure SQL Authentication supports Azure AD authentication or SQL authentication or Azure AD only authentication (disables the SQL authentication automatically).

Azure Role-Based Access control (RBAC) is what can and cannot be done as a user. We have built in roles in azure where we can assign access to the users and groups permissions all the way from the subscription to the individual resource groups and resources. These permissions can be applied to each level of the hierarchy of resources.

There are three RBAC built in roles that we need to remember:

Owner: Owner can do everything and assign other users as well.

Contributor: Can only modify but cant assign other userd

Reader: Can only read the assigned resource

Custom roles can also be created. Once these roles are created, you can assign the users to these groups.

For the SQL permissions, you can use the built in roles or the custom roles making sure you give the least privileges needed. To provide the least privileges, for the azure SQL database the AD user will be assigned to the database user which will have permissions to the database role connecting to the database. With the Azure SQL managed instance, the login at the server level will be associated and connected to the database user which is assigned to the database roles.

The other important thing to remember is that when the owner of the tables and the views objects are the same, we can only grant the user permissions to access the views instead of granting the permissions to tables for security reasons but the user can now access the tables through the views without having to have the access to the tables. This is called Ownership chaining.

Row level security: Securing the data by only providing access to the rows that the users are allowed to see. This comes under the least privilege access as well.

In order to implement the row level security, we need to create the SQL object known as security policy which is assigned to the table where it will filter the rows and provide only the information that is allowed to share.

I did not show any code examples here in this post because we already have several resources in place showing the examples of how to create the logins and users. Please check here and here

In the coming blog post, I will be writing about threat protection.

Thanks for reading!

Resources:

Azure SQL Security: Understanding Access and Authentication (Ep. 2) | Data Exposed

Quick Track: Beginner’s Guide to Azure SQL- Network Security Concepts

Let’s learn a bit about Security in this post.

Securing something you built is as important as creating it. Any resource that you create needs to have in depth layers of protection to save it from the bad eyes.

Let’s take the analogy of the Bank systems where you save your money and other valuables. Starting from the parking lot of the bank till the door of the bank vault where the actual money is stored, there is multi layer of security. Cameras, digital locks, padlocks and more locks to reach the money. This is in depth security system. Breaking any layer can only result in more effort to crack an another layer which is even more difficult.

Same goes with the Data. No matter you are on-prem or in the cloud, Data needs to be protected and there are no exceptions.

Securing data in Azure is an important part and there are different security layers available in Azure. Below diagram shows you the different layers of Security we have in Azure to reach the customer data.

In this post, let’s focus on the Network security.

Source: https://docs.microsoft.com/en-us/azure/azure-sql/database/security-overview?view=azuresql

Network Security

The below table will give an idea about different Network security options available and the differences among them.

Allow Access to Azure ServicesFirewall rulesVirtual network rulesPrivate link
Least secureSecure with firewallPrivate IP address of the Azure VMMost secure
Anything in Azure can connectServices with added Firewall rules can connectAny resource in that VN can access Azure SQLBlock all public access
Outside of Azure cannot connectAllow on-prem connectionsApply virtual network technologies to connect on-premPrivate IP address within a specific virtual network
Public IP addressPublic IP addressPrivate IP address within the VNPrivate IP address
DNS hierarchy is publicDNS hierarchy is public (region, cr)DNS hierarchy is still public (region, cr)DNS hierarchy shows private endpoint

In any specific region you have hosting the Azure SQL Databases, your client can connect to Azure SQL database using the Gateways having the public ip addresses. Gateways are the intermediate layer in between the Azure SQL database and the client. Client can connect to Azure SQL database through these gateways in two ways.

Proxy: We can understand this by simple analogy. You want to visit your friend’s apartment frequently but at the security gate, the watch man asks you several questions where you need to prove your authenticity to enter into your friend’s home every time you want to visit him. That is the proxy mode. Here, the DNS will resolve from one of the multiple gateways through port 1433. Each time the client want to connect, the gateway will act as a proxy in between to connect to the database. Proxy mode is the default mode and if you are trying to connect from outside Azure.

Redirection: You want to visit your friend’s apartment frequently and this time, you will receive additional key to the apartment at the entrance as you prove your authentication. Once you receive the key, you can directly go to the apartment with out any intermediate layer of security check at the gate. In the redirection mode, a redirect token is assigned when the client connects to the gateway and all the connections made to the database next doesn’t have to pass through the gateway. The redirect token contains the ip address of the database and the port in between the range 11000 to 11999. Redirection mode is default if you are trying to connect to the database within azure.

These gateways are known as data slices when they are logically grouped together. These data slices are used for the load balancing.

Let’s learn about each of the network security layer:

1. Allow access to Azure Services: Anything in azure can connect to your database if you enable this option. Any azure resource from any subscription and from any region. On-prem Servers cannot connect using this option. This option is a kind of the firewall rules where the start and end ip addresses were all zeros.

2. Firewall rules: These are the firewall rules that you create like any other on-prem Servers. By adding the client ip addresses here in the firewall rules, the client can connect to the Azure SQL database. On-prem servers can connect to Azure by using this option.

3. Virtual network rules: Clients from the specific virtual network subnets can connect to the Azure SQL through the private IP address.

4. Private Link: Private link is creating a private ip address from the virtual network which can connect to your database privately. Internet will be disconnected here. To create a private endpoint, we need to know what resource type you are creating the private endpoint for. It can be Azure SQL Database or the storage account, then on which virtual network and subnet we are going to create this endpoint.

Here is the youtube link you can follow to create a Private link for your database.

In the next article, we will be learning about the next security later, Access Management.

Thanks for reading!

Quick Track: Beginner’s Guide to Azure SQL- Learn to deploy

Okay, so you would like to deploy your databases in Azure! Great. Your resource limits are based on the deployment you chose. Managed instance and Azure SQL database resource limits depends on the Service tier, compute tier, the number of vCores you chose.

Before migrating the databases to Azure, knowing the details of what deployment model (IaaS/PaaS), deployment method (Azure portal, Azure CLI, PowerShell, ssms tool), region you wish to deploy, Service tier, compute tier, purchasing model, hardware and size of your databases is crucial (Source).

The deployment option and the service tier you chose directly depends on the resource limits that you get. Compute resource limits like the memory, storage limits like max size of your database log file, how long the backups are secured, IOPS, tempdb size limit etc.;(Source).

There are couple of things that we need to focus on while deploying the Azure SQL database. The region where you would like to deploy, under which resource group you would like to place the database, logical server name to connect to the Azure SQL using ssms, Admin SQL login credentials, purchasing model and service tier.

Creating the Azure SQL database using the portal is the basic method of creating a single database. Remember, you cannot restore a native backup in Azure SQL database but restore using the bacpac file. In MI, you can natively restore the database using the url.

Any resource that you create requires the subscription, resource group, database name, logical server name and credentials,

While configuring, you will have an option to chose if the database needs to be created as a part of the elastic pool (databases in the pool will share the compute resources together).

As you click on the configure compute and storage, you can chose the service and compute tier here.

If you chose the general purpose, you will have the compute tier options “provisioned”/”serverless”.

Chose the hardware configuration.

Select the hardware

Scale up or down to adjust the number of vCores and data max size.

Backup storage redundancy has three options. To find the differences among those, see here.

Networking tab:

Network connectivity:

No access: no one will have access

Public endpoint: quickly connect but not secure.

Private end point: Most secure way to connect by creating the private ipaddress to connect to from your virtual network.

Firewall rules:

Allow Azure services and resources to access this server: Any resources in azure can connect to your server. Not a secure option. try to avoid choosing this option.

Add current IP address: enabling this will add the client IP address so it can connect to the server.

Under the Security tab, you will see the Azure defender for SQL. This is not a free service. You will have to pay 15$ per server per month.

In the additional settings, chose how you want the database to be created. None will create just the database name and no objects. Backup is to get the database from the backup file and sample creates a sample of the adventureworks database. Select the collation while deploying the database. For the Azure SQL database, you cannot chnage the collation later.

Chose the maintenance window for the options provided.

Under the tags, name the tag for this resource and a value. This will help to identify the resources later.

Click on review and create. Click on create at the bottom of the window. This will create a new Azure SQL database.

After the deployment is complete, you will be able to copy the server name, open the ssms and connect to the server using the admin login and password credentials.

Connect to the server using the credentials for the logical server admin with password

Things to remember:

  1. You can chose the size and the number of the files for the Managed instance. You cannot chose the storage in MI and Azure SQL database. For Azure SQL, you cannot chose the number of database files.
  2. T-log file for the Azure SQL database is 30% of the data max size.
  3. Compatibility level is available for both MI and Azure SQL database
  4. Query_store is turned on for all Azure SQL databases
  5. Azure SQL Databases recovery mode is always full
  6. You cannot stop and start the Managed instance and Azure SQL database
  7. Proxy and redirection are the two ways that we can connect to Azure SQK database. Proxy connection is like a friend who visits your apartment always need to connect to the security guard (known as gateway here) before reaching your apartment (database). This is the default setting for connections outside Azure.
  8. Redirect connection is like your friend gets an additional key as an authorized person for the apartment by connecting to the security guard (the gateway) and from that point the next connections can be directly reach the apartment (database). No need of additional hops in between. Redirect connection is default connection type within azure. To use redirect connection, ports 11000- 11999 needs to be opened.

We are going to learn about fundamentals of security in azure in the next blog post. Thanks for reading!

References:

  1. Learn Live – Azure SQL Fundamentals: Deploy and Configure Servers, Instances, and Databases (Ep.2)
  2. Deploy and configure servers, instances, and databases for Azure SQL

Quick Track: Beginner’s Guide to Azure SQL

Source: Image

As I started learning Azure couple of months ago, I was confused in the beginning of where to start and was overwhelmed by looking at the amount of information that I not only have to learn but also remember what I learn.

I believe you can treat this series as my consolidated learnings put together in a simple way. A quick guide to understanding the concepts if you are a beginner or to revise the concepts you already learnt to make your foundations stronger.

Let’s start with a Why Question. Why should you even start learning Azure or any cloud platform?

Azure is one of the cloud platforms available in the market which provides services that you can use to host your applications, build your databases, virtual machines, storage, analytical and many other services without having to buy or manage any hardware. As the Azure supports Infrastructure and platform as a service, you can build, configure and run your databases and related services pretty quickly and with ease of deployments through Azure portal online/command line interface/PowerShell.

Deployment options

Source: Microsoft

IaaS: Infrastructure as a Service

  • SQL Server on Azure VM
    • SQL Server hosted on Azure Infrastructure
    • Automated backups and patches
    • Extended security updates for the older versions of SQL Server (SQL Server 2008)
    • You will have access to operating system and can configure instance level features for SQL Server
    • Easy Install- through Azure portal using images (various version combinations of available packages for both Windows and SQL Server together)

PaaS: Platform as a Service

  • SQL Managed Instance
    • OS is taken care by Microsoft
    • You will not have access to the OS
    • Use and configure all features of the SQL Server instance
    • Useful for lift and shift scenarios where applications don’t have to change their code in order to move to this service
  • Azure SQL Database
    • You will just get the database
    • Database level features can be configured
    • OS and Instance capabilities taken care by Microsoft
    • very useful for Modern cloud application built
    • Very quick to deploy

For the full list of feature comparison between Azure SQL Database and SQL managed instance, read the Microsoft doc here.

Unlike the SQL Server on Azure VM, Azure SQL database and SQL managed instance are versionless. Databases, OS are upgraded, patched behind the scenes by Microsoft.

Purchasing models:

There are two purchasing models, DTU and vCore.

In the DTU (database transaction per unit) model, we choose the number of DTU’s and the resources comes in bundle together (compute and storage resources). vCore model has the advantage of using the Azure hybrid benefit and reserved capacity if you can pay for the resources ahead of time up to three years. When you use vCore model, it is easy to convert the on-prem workloads to the azure as you can independently choose the compute, storage and other resources like memory.

DTUvCore
Bundle togetherIndividual compute and storage
Easy and simpleCustomize and control
Not flexibleFlexible
Azure Hybrid benefit – NO Azure Hybrid benefit – YES
Available for Azure SQL Database onlyAvailable for MI and Azure SQL Database

The cost of the vCore model depends on the service tier, compute, storage, hardware you choose, backup storage you choose.

Note: you can convert the DTU model to vCore model. Know more here.

Service Tiers

We have three Service tiers for Azure SQL database and Managed instance – General purpose, Business critical and Hyperscale. Especially under the General purpose tier, we have two compute tiers. We will learn that in coming section. For the main differences among the service tiers, please follow below table.

Service TierGeneral purposeBusiness criticalHyperscale
PurposeGeneral workloadsLow latency workloadsOLTP, large databases
Compute80 vCores80 vCores80 vCores
Storage typeRemote 5 GB – 4 TBLocal SSD 5 GB – 4 TBLocal SSD cache Up to 100 TB
Backups1 to 35 days (7 as Default)1 to 35 days (7 as Default)1 to 35 days (7 as Default)
Availability1 replica, no Read Scale-out, zone-redundant HA (preview), no local cache3 replicas, 1 Read Scale-out, zone-redundant HA, full local storageMultiple replicas, up to 4 Read Scale-out, zone-redundant HA (preview), partial local cache
In-memoryNot supportedSupportedNot supported
IOPS500 per vCore with max 7,0005,000 with max 200,000Multi-tiered architecture Effective IOPS will depend on the workload

Compute Tiers

Especially under the General purpose service tier with vCore model, we have two Compute tiers, Provisioned and Serverless.

ProvisionedServerless
Predictable workloadsUnpredictable workloads
Manual scalingAutomatic scaling
No pausePause the database
Pay for the fixed resources you choosePay only while database in use
Per hour billingPer second billing

For example, you will need to chose provisioned compute tier if your database has users connected most of the times having more usage of the compute resources and you cannot predict the traffic to the database. On the other hand, if you have predictable workloads with the specific timings of the users connecting to the database, you can use the serverless compute model.

You will be choosing the max amount of resources ahead of time and scale manually and you will be paying even if you are not using the resources in full. With the serverless, it is automatic scaling. You will be choosing the min and max number of compute resources so the automatic scaling happens only in between those min and max limits.

The advantage you have by using the serverless is to pause the database during the specific timings you choose. These are the timings when the users will not be connected to the database. Once the database goes to the pause mode, you will be only paying for the storage and not for the compute. If you are using Serverless option and do not place your database in auto pause mode, then you will be charged for the min amount of resources when users are not connected and charged per second basis on the resources you use during the activity. (Source)

For the provisioned compute tier, the billing is per hour basis where as for the serverless compute tier, billing is per second basis.

Summary:

In this Quick track of basics to Azure SQL, we learnt about the overview about the deployment options, purchasing models, service tiers, compute tiers and how they differ from each other. In the next blog posts, we are going to learn more about what Azure SQL has to offer.

Thanks for reading!

References:

  1. https://docs.microsoft.com/en-us/azure/azure-sql/database/purchasing-models?view=azuresql
  2. https://docs.microsoft.com/en-us/learn/modules/azure-sql-intro/
  3. https://docs.microsoft.com/en-us/azure/azure-sql/database/service-tier-hyperscale
  4. https://docs.microsoft.com/en-us/azure/azure-sql/database/serverless-tier-overview
  5. https://www.youtube.com/watch?v=wcRb5RHriUc