Category: Azure

Azure Defender for SQL, once you enable it will alert you for any SQL injection attacks, brute force attacks or any breached identities trying to access the data of your database. It also provides the vulnerability assessments. Vulnerability assessments give you alerts about the configurations of your database. If your database configuration is not following the standards of Azure, you will receive the alerts in the vulnerability assessment report.

You can enable the Azure Defender at the subscription level or at the Server level or at the resource level as well. Under the recommendations in the security center in the Azure portal, check for the Remediate security configuration. This will show if the Azure defender is configured properly.

Azure defender for SQL consists of two features – Advanced Threat Protection and Vulnerability Assessment.

Advanced Threat Protection

Threat protection will send the alerts if there is any malicious activity going on against your database. You will receive alerts if there are any SQL injection attacks, any suspicious login attempts from new locations.

Vulnerability Assessment

Vulnerability assessment will check for the security misconfigurations based on the Azure standards. It will provide a baseline based on the standards which you can later accept and add these baselines based upon your environment. It will also provide the actionable steps to take and remediate the alert.

Azure defender supports Azure environments and also hybrid environments. For the complete list of where the Azure defender is supported, please check the Microsoft article here.

You can view all the defender collected information in the centralized view at Azure Security Center.

Vulnerability assessment requires the storage account for the PaaS service. This can be setup at the logical SQL Server level. For the IaaS service, you don’t have to specify the storage account separately.

Once you turn on the Azure Defender, we need to give sometime to collect the information and scan the system for any vulnerabilities. At the server level, Go to the Microsoft Defender for Cloud. You can also open at the resource level as well.

To view the details, click on the additional recommendations.

You can also see the recommendations from the resource level as well. Click on the Microsoft defender for cloud from the database level.

Scroll down to see the details

Let’s see the example of the first vulnerability assessment finding. The security check shows Database owners are as expected. Click on the finding to see more details.

You can either look into the details and add the recommendations to the baseline or ignore. This is completely based on your needs and your current environment.

Azure Sentinel

Azure sentinel will help in identifying the security incidents that we need to investigate. You need to create a log analytics workspace to add to Azure sentinel. Multiple data connectors can be used to connect and collect the information from these different connectors and analyze the data for the security incidents.

You can use different dashboards to view the collected data and alerts. For example, you can use the audit logs dashboard to view more details on the alerts. You can setup the alerts for the events. You can also create the playbooks to automate the response to specific alerts using the logic apps.

Learn how to setup the Azure Sentinel here.

In the next blog post, we will learn about the Auditing in Azure SQL.

References

1. Overview of Microsoft Defender for Azure SQL
2. Azure Defender for SQL

Thanks for reading!

Data needs to be protected no matter where it lives, On-prem or in Azure. Data can be protected by using the encryption that Azure provides. What are the types of encryption we have in Azure?

Encryption in Transit: The data needs to be protected as it moves all the way from the database to the client application and vice-versa. The encryption is always by default enforced by TLS (Transport Layer Security). Making sure your application supports the TLS version you choose on your server is important. If for example, you chose the TLS version 1.2 on your server and your client application only supports the 1.2 TLS version, your application cannot connect to the server.

As a best practice from Microsoft, client applications should have the encryption enabled on their connection string and not trust the Server certificate.

Encryption-at-rest: Data at rest on the disks- which includes the data files, log files and backup files. These are encrypted with the technology called Transparent Data Encryption.

This option is enabled by default as well. As the data is moved in and out of the drives, the data is being protected through the transparent data encryption. By default, Microsoft is going to manage the keys known as Microsoft-managed keys but also you can choose bring your own keys (BYOK). In the default option Microsoft-managed keys, the responsibility of the key generation, key rotation and the management of these keys are taken care by the Microsoft. All these keys need to be stored in the Key vault which is a Microsoft product. In the second option BYOK, the customer is responsible for the management of the keys including the key generation, key access to the users and key rotation. The third option we have is Hold your own key (HYOK) which is a IaaS offering only where the key generation including the key management and storing the keys is totally taken care by the customer.

Double layer of encryption is where you can chose the TDE along with the infrastructure encryption (volume encryption). This is an additional layer of security for your data on disk. The second layer Infrastructure encryption is optional choice.

In the case of customer managed key, the server identity should have the access to the key vault. So, when the server wants to encrypt and decrypt the data on the drive, it will use this key. The first step to create the customer managed key is to create the Key Vault if you do not already have one.

Once you created the Key vault, choose the customer managed key. To select a key, first we need to create a key that is saved in the key vault. Here I have created two keys.

Then go to the access policies on the Key vault and give the server (Securitydemo) enough permissions to access the key. That requires Wrap and unwrap permissions.

Now go to the transparent data encryption and choose the key by clicking on the Select a key and then click on change key.

Choose the key you have created and click on select

Once you select the key, click on Save on the transparent data encryption page.

Encryption-in-use: It encrypts the data during the query processing and the technology used is always encrypted. This is the client side encryption technology. Cryptographic keys that are used to encrypt and decrypt the data are never shared with anyone. The SQL client driver in between the client application and the database will serve as the mediator to encrypt and decrypt the data as the data moves in and out of the database. Initially only the equality searches were only supported but to add more functionality in supporting the range queries, pattern matching, sorting, indexing and more operations, a new technology is being introduced in SQL Server 2019 known as Secure Enclaves.

Secure Enclave is a part of memory in the SQL Server that no one can access and this part of memory is used to process the computational operations involving the sensitive encrypted column. In this part of the memory, the secure enclave will have the data which is decrypted safely and queries can be processed here.

How do we know that the Secure enclave is secure to process the sensitive data? There is a service known as Microsoft Azure attestation which will check if the secure enclave is safe and process the queries. By using the Secure Enclaves, even the windows administrators or DBA’s cannot access the sensitive data on always encrypted columns.

Dynamic Data Masking

Dynamic Data masking is a protection of the data from the application users who should not have access to specific information. This is not the encryption where the files are encrypted.

This is limiting the access to the data where the data is masked on the fly when the data is moved from the database to the application. As the data reaches to the application, only the needed information needs to be shown to the application users. You can use the custom built functions or create the custom functions to mask the data.

Ledger

Ledger is a technology which uses blockchain technology to protect the data tampering from the privileged users who have access to the sensitive data. This involves the ledger tables built in as you enable this feature on the database.

Updatable ledger tables allow to capture the data of updates and deletes. The updated rows will be stored in the history tables. The database digests using the block chain technology is saved in the trusted Azure storage where database digests are used to verify the database whether the data in it is being tampered.

Because the Ledger captures all the changes happening to the data in the database and if the tampering happens through application, these changes can be also be identified through the ledger views. This is one of the great features in Azure in protecting the data.

This blog post covered multiple data protection technologies available in Azure. In upcoming blog post, we will learn about the next security layer Threat prevention and detection.

Thanks for reading!

References:

Azure SQL Security: Data Protection

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

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.

Network Security

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

Allow Access to Azure Services	Firewall rules	Virtual network rules	Private link
Least secure	Secure with firewall	Private IP address of the Azure VM	Most secure
Anything in Azure can connect	Services with added Firewall rules can connect	Any resource in that VN can access Azure SQL	Block all public access
Outside of Azure cannot connect	Allow on-prem connections	Apply virtual network technologies to connect on-prem	Private IP address within a specific virtual network
Public IP address	Public IP address	Private IP address within the VN	Private IP address
DNS hierarchy is public	DNS 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!

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

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

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.

DTU	vCore
Bundle together	Individual compute and storage
Easy and simple	Customize and control
Not flexible	Flexible
Azure Hybrid benefit – NO	Azure Hybrid benefit – YES
Available for Azure SQL Database only	Available 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 Tier	General purpose	Business critical	Hyperscale
Purpose	General workloads	Low latency workloads	OLTP, large databases
Compute	80 vCores	80 vCores	80 vCores
Storage type	Remote 5 GB – 4 TB	Local SSD 5 GB – 4 TB	Local SSD cache Up to 100 TB
Backups	1 to 35 days (7 as Default)	1 to 35 days (7 as Default)	1 to 35 days (7 as Default)
Availability	1 replica, no Read Scale-out, zone-redundant HA (preview), no local cache	3 replicas, 1 Read Scale-out, zone-redundant HA, full local storage	Multiple replicas, up to 4 Read Scale-out, zone-redundant HA (preview), partial local cache
In-memory	Not supported	Supported	Not supported
IOPS	500 per vCore with max 7,000	5,000 with max 200,000	Multi-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.

Provisioned	Serverless
Predictable workloads	Unpredictable workloads
Manual scaling	Automatic scaling
No pause	Pause the database
Pay for the fixed resources you choose	Pay only while database in use
Per hour billing	Per 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