Exploring Azure SQL authentication and access

When planning to modernize your SQL workloads by moving them to Azure SQL, the first requirement is to guarantee that the solution is secure. After all, SQL is about data, and data is probably the most valuable thing your company owns. As admins, it is our sole responsibility to secure it. This article will focus on two basic concepts — Azure SQL firewall and the authentication process — but security will always be at the center.

Understanding the SQL firewall

We need to understand that Azure SQL is a PaaS (platform-as-a-service) offering, which means that the architecture is accessible from the Internet. That is not a security flaw in any shape or form, that is just how the service is architected, and it is up to the cloud administrator/architect to define how we are going to protect our data.

Azure SQL has several options to restrict the traffic. Several options are available based on the security design, including denying public access, using private endpoints, and Azure SQL firewall.

Every Azure SQL has built-in a SQL Server firewall and a SQL database firewall. First, we can configure using Azure Portal and Azure Resource Manager (ARM) interfaces, where we can mention Azure CLI, PowerShell, APIs, and Azure Portal, to mention a few.

When we provision a new Azure SQL database, we are automatically placing it in an Azure SQL Server. If we do not have it upon creation, we will have the define it first.

When we have the Azure SQL Server, we should check the firewall and virtual networks item, which is vital for protecting how your SQL data will be accessible. We will explore some of those options to get a better understanding when accessing a SQL Server.

Items 1 and 6 are related. If we are accessing Azure SQL from the Internet, every time we click on Add client IP (Item 1) button, a new entry will be added to the rules (Item 6), and that is the Azure SQL Server firewall in action. We can define some specific IPs to connect to this server.

Item 2 helps to lock down the environment from the start. If we have a private endpoint, we will have the option to decide if we want to block all traffic from the Internet.

Item 3 defines the minimum TLS version for the SQL clients will use to communicate with Azure SQL. Although the platform (remember, it is a PaaS offering) supports several options, you must use the most secure environment.

We can also define which virtual networks and subnets within Azure can access this SQL Server and its databases (Item 7).

Azure SQL

We left item 4 for last because it requires an image from the Azure SQL architecture (Microsoft provides that picture on their official documentation). There are two options — proxy and redirect. The proxy will use the Azure SQL gateways to proxy all the communication and has a single port to be opened on the client. However, the performance is not the best available. When using Redirect, the connection is made directly to the server, giving us better performance and lower latency.

SQL Server and authentication

Before understanding the scenarios that we will explore in this section, we need to understand how the authentication works in Azure SQL.

For starters, when provisioning a SQL Server, we need to provide a username and password that will allow us to authenticate against this new SQL Server. Keep in mind that connecting to the SQL Server/database must meet the firewall requirements that we defined in the previous section.

Azure SQL

Log on to a workstation that can be approved at the SQL firewall level. Open Microsoft SQL Server Management Studio (you can download it from the Microsoft website), and when prompted, provide the Server Name and select SQL Server Authentication, as well the credentials. Click Connect.

Note: If the server’s public address is not listed on the firewall, you will be prompted to add it as part of the logon process.

After authenticating, we can see the database name and the user used to authenticate. In our case, we can notice the username being batman, which means that SQL Server Authentication was used to authenticate.

Azure SQL integrates with Active Directory to perform authentication. We can configure that by going to Active Directory admin under settings. On the new page, click on Set admin to define a user or a group that will manage the SQL Server, and click on Save to confirm the changes.

Azure SQL

When authenticating using Management Studio, we can select Azure Active Directory-Universal with MFA and the user’s email we configured in the previous step.

Azure SQL

The result will be a connection using the account’s email address, which means that we use Azure Active Directory to connect to the server.

What is the problem? Although we improved the security posture of Azure SQL using Azure Active Directory, SQL Server Authentication is still a valid method to authenticate to the server.

We can disable the SQL Server Authentication using the Enable-AzSQLServerActiveDirectdoryOnlyAuthentication, which will disable the SQL Server Authentication.

Azure SQL

When trying to authenticate with SQL Server, the following error message will be displayed.

Azure SQL: You’re secure and ready to go

This article covered two important topics around Azure SQL management, including the SQL firewall and the authentication process.

Featured image: Shutterstock

Leave a Comment

Your email address will not be published.

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

Scroll to Top