Azure SQL Database is Microsoft’s Platform as a service (PaaS) offering for SQL Server. It eliminates hardware, virtual machine, and operating system management and has automatic tuning, high availability, auto-scale capabilities, high-performance options, and much more. It’s easy to create an Azure SQL Server Database, but you may not know about best practices and some of the newer features. This article covers some of the essential configurations of Azure SQL you should be aware of. It explains some of the configurations or leaves you with links explaining the details of the more complex configurations.
These are the items covered in this article:
- Serverless is more than auto-pause
- You should not enable access to Azure Services on the Firewall
- You should not open firewall rules on the portal for database users
- The secondary node is accessible on Business and Premium tiers
- Your Azure applications should not route packages through the public network
- You can dismiss the firewall all together
- You should avoid SQL Standard security
- RCSI configuration ON by default
Once upon a time
Many of the items in this article are about Azure SQL Security. Years ago, before the cloud providers existed, organizations published sites from their own on-premises environments. Usually, an application server would be deployed in a virtual network called DMZ, protected by a firewall. This virtual network would be isolated from the rest of the company network by a second firewall, creating a level of isolation and making only the DMZ accessible from the internet.
The SQL Server would be located inside the company network, outside the DMZ. The application server on the DMZ would be the only server which would have access to the SQL Server, creating a secure connection from the internet to the company data.
Back when the technology was way more complex and difficult, we had all this security, so why would we think that provisioning an Azure SQL Database with a public endpoint in a cloud provider to be a good practice? Shouldn’t we change the defaults to make the security tighter like it was in the past?
Serverless is more than auto-pause
The Serverless option in Azure SQL is well-known for the ability to pause the service when it’s not being used.
However, this ability has many drawbacks:
- There is no way to trigger the pause. As a result, the pause will only happen after the server stays without any request for the entire time of the auto-pause delay. The auto-pause delay has the minimum value of 1 hour
- Several Azure features, such as Azure SQL Defender, will make calls to the serverless instance and prevent the serverless from getting into pause.
- Several features such as long-term backup retention and elastic jobs are not compatible with auto-pause.
- The serverless feature can be implemented only on the vCore mode. At the time of this writing, the cheapest DTU mode you can provision is € 5,00/Month. The most affordable vCore mode you can implement is €200/Month, besides the serverless, of course. The vCore cost of a serverless implementation is also a bit more expensive than other provisioning modes.
All these details will make the auto-pause benefits limited, however, there is one additional configuration: The serverless provisioning is a flexible vCore model, and you can establishing a minimum and maximum vCore amount for the server. In fact, this configuration is enabled by default when you choose the Serverless provisioning.
By doing so, the Azure SQL will have a flexible charge based on the number of vCores actually used between the minimum and maximum established. You can even leave the auto-pause turned on if you want to do so. Auto-scaling is a benefit not available to any other provisioning model.
The image below shows an example of this. The subscription is always charged for minimum number of vCores (assuming that auto-pause is disabled) or the number of vCores used, saving money when the maximum number of vCores is not being used.
(Image from Serverless compute tier – Azure SQL Database | Microsoft Docs)
Being able to scale up and scale down according to workload makes Serverless an attractive option when choosing your provisioning model. If you choose vCores, then you should consider Serverless with a minimum and maximum configuration.
You should not enable access to Azure Services on the Firewall
It’s easy and tempting to turn on the Allow Azure services and resources to access this server setting. You only need to enable it, and you will no longer have firewall problems with any Azure Service.
The problem is what most people ignore: The firewall will not be opened only to the services from your company. The firewall will be opened to any Azure services; it doesn’t matter to whom it belongs.
One security layer will be completely removed from the Azure SQL when this option is enabled. Of course, you still have the authentication, but is this enough?
You should not open firewall rules on the portal for database users
The firewall configuration is a common need in Azure SQL configuration. However, many administrators ignore the fact there are two levels of firewall configuration: You can configure the firewall on the server level, or you can configure the firewall on the database level.
(Image from IP firewall rules – Azure SQL Database and Azure Synapse Analytics | Microsoft Docs)
This happens because the UI in the portal can only configure the firewall at the server level. The firewall configuration on the database level needs to be done using T-SQL.
A basic security rule is always to set the minimum permissions needed by a user. If you open the firewall on the server level, you are not following this rule. A more secure way to open the firewall for database users is to open the firewall only for the database, not on the server level.
The statements below may be useful.
Creating a database firewall rule
EXECUTE sp_set_database_firewall_rule N'Example DB Rule','0.0.0.4','0.0.0.4';
Listing database firewall rules
select * from sys.database_firewall_rules
Delete a database firewall rule
EXECUTE sp_delete_database_firewall_rule N'Example DB Rule';
Once you adopt the practice of opening database firewall rules, the user connection needs to specify the database when connecting. Connections on the server level will not be accepted.
Using SSMS, this configuration is not typical and can be found on the advanced connection settings.
Application connection strings always contain the database name, so this is not a big difference.
References
- Azure SQL Database and Azure Synapse IP firewall rules
- Tutorial: Secure a database in Azure SQL Database
- Azure SQL Database and Azure Synapse Analytics network access controls
The secondary server is accessible on Business and Premium tiers
The Read Scale-Out of the Business or Premium tiers is a recent feature, but it’s quite interesting. It’s straightforward to set when provisioning the database.
Microsoft uses the Always On feature in these tiers to ensure high availability. The image below illustrates how the failover is automatically implemented.
Enabling Read Scale-Out to the secondary servers makes it possible to create a balance for your applications. Applications that need to write data to the database will connect to the primary server, while applications only reading data will connect to the secondary server.
This connection is defined by the connection string of the application. The application only needs to specify the parameter ApplicationIntent on the connection string. ApplicationIntent can be defined as ReadOnly or ReadWrite. This single parameter will define if the connection will be made with the primary or the secondary server.
You can test this feature using SSMS: Using the Advanced Options you can set the ApplicationIntent parameter to one of these values, make the connection to the server and identify the server you are connected to.
The following query will allow you to identify if you are connected to the primary or secondary server:
SELECT DATABASEPROPERTYEX(DB_NAME(), 'Updateability');
References
Your azure applications should not route packages through the public network
Your Azure SQL is inside Azure (is there a more obvious statement than this one?).
If the applications which will access your Azure SQL are also inside Azure, the communication between both should happen entirely inside Azure. This means the network packages exchanged between the application and the Azure SQL should flow entirely inside the Microsoft Network, never through public network.
Once again, it appears obvious, but this is not the default behaviour. If you only “establish the connection” in the simplest way, the connection will be done through the public internet.
The best way to identify if the connection is done through Microsoft Network or through public internet is the firewall: If the connection is blocked by the firewall or you need to open the firewall for the source IP’s, then the connection is crossing the public internet. When the connection is configured to be go through the Microsoft Network, the IP configurations of the firewall will be bypassed.
For example, if a virtual machine on Azure shows the screen of the image below when connecting to Azure SQL, this means the communication between both will happen using public Ips.
The steps to ensure the communication through the Microsoft Network are:
- You need a virtual network
- You need to ensure your application is being executed inside this virtual network
- Create a service endpoint between your Azure SQL and your virtual network
- On the Azure SQL Server, include permission for the virtual network to access the server
References
- Virtual Network Service Endpoints
- Use Virtual Network Service Endpoints and rules for servers in Azure SQL Database
Additional Details
Your application is in a Virtual Machine: Virtual Machines already include a virtual network when they are provisioned, you only need to ensure the creation of the service link to your Azure SQL
PaaS: Each PaaS service in Azure has a different way to be included inside a virtual network. You need to verify the correct procedures for the PaaS you are using. However, this is a feature that may not be included on the lowest cost service levels available. That’s when you will need to decide to pay a bit more for the security or only ensure a good configuration of the firewall.
You can dismiss the Firewall all together
If you manage to configure all your applications to access Azure SQL from the Microsoft Network, either because the application is in the network or using network communication such as VPN connections, you can completely Deny public network access on Azure SQL.
Once this change is complete, Azure SQL can only access the database from inside your company’s virtual network on Azure or through VPN.
The following configuration details are critical to this scenario:
- Service Endpoint doesn’t work because it’s intended to establish a route to a feature with public access enabled
- You need to create a Private Link on your virtual network
- Private Links are based on DNS zones. When a private link is created, a DNS Zone is created as well (except on custom scenarios).
- Peered virtual networks need to be configured to use the same DNS Zone for DNS resolution
- Gateway clients (such as VPN clients), need to be configured to redirect DNS resolutions to Azure DNS. This can be done in many ways.
This configuration is similar to the one found on-premises: The database server will be completely private.
References
- Tutorial: Connect to an Azure SQL server using an Azure Private Endpoint – Azure portal
- What is Azure Private Endpoint?
- Azure Private Endpoint DNS configuration
You should avoid SQL Standard security
Although the SQL Standard authentication evolved a lot over the years, it’s still a simple login/password authentication. Recently the Azure SQL team included features to allow limiting the authentication to Azure AD Authentication, preventing the use of SQL Standard Security.
Besides the configuration itself, you can use policies to control the use of SQL Standard authentication and identify which servers have this configuration disabled, identifying which servers are not compliant with the company rules on an enterprise level.
Here are some blogs I wrote about this subject:
- How Essential Are Azure Policies
- Azure SQL: Tightening the Security using Integrated Authentication only
- Azure SQL: Enforcing Azure AD Only Authentication
RCSI configuration is ON by default
SQL Server has many isolation levels to control the integrity of the data.
However, in read-heavy applications, many developers have the terrible habit of using NOLOCK on all statements to avoid the contention of creating lock records on the server. The developers ignore the risk of incorrect results.
Over the years, Microsoft created a solution for SQL Server: Two new isolation levels, RCSI (Read Committed Snapshot Isolation) and Snapshot Isolation; both also called optimistic isolation levels.
RCSI is considered a special isolation level. It’s different from the other isolation levels because RCSI is configured as a database property. There is no need to change the code. Every transaction that arrives on the server using Read Committed isolation (the default) is converted to Snapshot Isolation. I’m using quotes here because it’s not exactly Snapshot Isolation. RCSI has some differences from Snapshot Isolation, and that’s why they are considered two different isolation levels.
The point is: Azure SQL uses RCSI by default. This is a big change for all existing applications. Any use of NOLOCK that you have in existing applications becomes useless and a problem for the application because it brings the usual issues of NOLOCK with no benefit, since the use of RCSI replaces all the possible benefits of NOLOCK.
You can check this default on Azure SQL running the following query:
select database_id,name,is_read_committed_snapshot_on from sys.databases
Migrating applications to Azure SQL and using RCSI are great opportunities to eliminate all NOLOCKs still found in your SQL code.
References
Summary
It’s very easy to start developing applications using Azure SQL. After the initial development, as this article illustrated, Azure SQL has some secrets to help you improve performance and security.
Developers may need the assistance of Solution Architects for the most complex steps, such as virtual networks, DNS, VPN, and Private Endpoint configuration. This highlights how the cloud requires all roles working together, as it was in the past, with on-premises environments.
The post Eight Azure SQL configurations you may have missed appeared first on Simple Talk.
from Simple Talk https://ift.tt/gea9M7z
via
No comments:
Post a Comment