kohera-logo-regular.svg

Securing the data tier of an Azure SQL Server Database – Part 2

One of the most prominent questions when working with PaaS databases is how to secure them. In the previous blogpost of these defence series, I talked about limiting access to objects through firewalls and encryption. Today I’ll discuss protecting and auditing your data. Also, I’ll add some extra resources for a complete overview of the available security features on an Azure SQL Sever Database.

 

The second layer of defence: authentication

Authentication is an mechanism that proves the identity validation in case of an attempted connection. SQL PaaS databases currently support two types of authentication:

  • SQL Authentication, which uses a username and password
  • Azure Active Directory Authentication, which uses identities managed by Azure Active Directory and is supported for managed and integrated domains

 

The logical SA account or, server admin as known in the context of a PaaS server, is established when you create the logical with a username and password. When using these credentials, you can authenticate in any database on that server as the database owner, or dbo.

Creating an AD Admin

If you want to use the Azure Active Directory Authentication for your SA account, you must create another server admin called the Azure AD admin. This administrator can then be used to administer Azure AD users and groups and is also capable of performing all operations that a regular server admin can. The following script provides the Azure AD administrator group named DBA_Group as an Azure AD Admin on the KoheraServer. There is a small catch, as this commandlet does not prevent you from providing Azure AD admins for unsupported users. An unsupported user can be provided, but cannot connect to the databases.

Set-AzureRmSqlServerActiveDirectoryAdministrator –ResourceGroupName " KoheraResourceGroup "
–ServerName "KoheraServer" -DisplayName "DBA_Group

Creating an application account

It is always better to create an application account to authenticate your application, because it is a foolproof way to limit granted permissions and at the same time reduce the risks of malicious activity in case of for example a SQL injection attack. The recommended approach is to create a contained database user, which allows your app to authenticate directly to a single database. The following script is an example of how you can create a contained database user that uses SQL authentication and must be executed by a server admin in the corresponding database scope.

CREATE USER ApplicationUser WITH PASSWORD = 'strong_password'; -- SQL Authentication

If you are connected as the Azure AD admin, you can also create a contained database user that uses Azure Active Directory Authentication by executing the following T-SQL command:

CREATE USER [Azure_AD_principal_name | Azure_AD_group_display_name] FROM EXTERNAL PROVIDER; -- Azure Active Directory Authentication

You can see how simple it is to athenticate in this way, so there really is no excuse for you to connect your application using anything else than these application credentials.

Idle connections, reauthorizations and changing passwords

As the database engine will close any connections that remains idle for more than 30 minutes, you should consider a reconnecting logic into your application. However, an automated disconnect isn’t the only argument. For security reasons, continuously active connections to a SQL Server database will require reauthorization (performed by the database engine) at least every 10 hours.

The database engine will attempt reauthorization by itself using the originally submitted password and, in many cases, no extra user input will be required. This is important for performance reasons. When a password is reset in a SQL Server database, however, the connection will not be reauthenticated automatically, even if the connection is reset due to connection pooling. keep this in mind, because this is one of the points in which the behaviour of an on-premise SQL Server is different.

If you change a user password since the connection was initially authorized, the connection should be broken en reauthorized by using the new password. The best way to do this, is using an account with the KILL DATABASE CONNECTION permission and then explicitly terminate the user connections to SQL Server database by using the KILL command.

 

Resources

For more info on this topic, be sure to check out Microsoft’s advice on Azure AD authentification.

Group of computer programmers working in the office. Focus is on blond woman showing something to her colleague on PC.
Updating your Azure SQL server OAuth2 credentials in Power BI via PowerShell for automation purposes
The better way to update OAuth2 credentials in Power BI is by automating the process of updating Azure SQL Server...
2401-under-memory-pressure-featured-image
Under (memory) pressure
A few weeks ago, a client asked me if they were experiencing memory pressure and how they could monitor it...
2402-fabric-lakehouse-featured-image
Managing files from other devices in a Fabric Lakehouse using the Python Azure SDK
In this blogpost, you’ll see how to manage files in OneLake programmatically using the Python Azure SDK. Very little coding...
2319-blog-database-specific-security-featured-image
Database specific security in SQL Server
There are many different ways to secure your database. In this blog post we will give most of them a...
kohera-2312-blog-sql-server-level-security-featured-image
SQL Server security made easy on the server level
In this blog, we’re going to look at the options we have for server level security. In SQL Server we...
blog-security_1
Microsoft SQL Server history
Since its inception in 1989, Microsoft SQL Server is a critical component of many organizations' data infrastructure. As data has...