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.