Microsoft Azure SQL Database Security – Authentication, Authorization, and Encryption

In our most recent article published on this forum, we started an overview of securing Microsoft Azure-based SQL Database deployments, focusing on SQL Server- and database-level firewall access control and methods that can be employed to implement it. While the approach presented so far constitutes the first line of defense against unauthorized use, it is important to realize that additional protection can be provided by leveraging features available in a number of other security-related areas, such as authentication and authorization, as well as data integrity and confidentiality. In this article, we will explore each of them in more detail.

From the standpoint of authentication, your options when connecting to SQL Database are limited exclusively to native SQL functionality, since Windows-based logons are not supported in this scenario. This underscores the importance of proper management of SQL security principals – including both logins defined in the master database as well as users of individual SQL databases (along with their associated roles, which affect the scope of authorization). Your diligence in this regard should start with assigning a non-standard name and a complex password (Azure does enforce its strength, ensuring that it contains at least eight characters in three out of four categories consisting of mixed case letters, numbers, and symbols; however, you might want to strengthen it even further) to the login provisioned automatically when creating Microsoft Azure-resident SQL Server instance. As part of the provisioning process, the login is also associated with the identicaly named, autogenerated master database user (which you can easily verify by enumerating content of sys.sql_logins and sys.sysusers system views). From the authorization perspective, this user (by default) has a privilege level equivalent to the dbmanager and loginmanager database roles in the master database, effectively granting it sufficient permissions to create additional databases and defining new SQL logins as well as associated users (it is also automatically added as a user with the dbo-level permissions in every new database). It is worthwhile noting that this administrative mechanism is different from the one incorporated in on-premise instances of SQL Server that relies on dbcreator and securityadmin fixed server roles for this purpose, which reflects architectural differences between the two platforms. In the context of Microsoft Azure, the concept of SQL Server is somewhat artificial, providing an abstraction layer that conceals the fact that its databases can reside on separate systems (one of the more apparent side effects of this design is a lack of support of the USE command for switching between databases).

To create additional logins, use CREATE LOGIN login_name WITH PASSWORD=’password T-SQL statement (while connected to the master database), where login_name and password represent the new credentials. The statement must be executed in the security context of the original, aforementioned user (generated during provisioning of the SQL Server instance) or another one (created afterwards) that has been assigned the loginmanager master database role. Note that if you rely on sqlcmd to run T-SQL code against a cloud-based SQL Server instance, you will need to specify your credentials in the format [email protected]_name, where server_name represents the name of your SQL Server instance (as it appears in the Azure Management Portal) when authenticating your connection.

To allow a newly created login (stored in the master database) to log on to an arbitrary database (including master) or have any privileges assigned, you need to also define a new database user associated with that login. This is accomplished by running CREATE USER user_name FROM LOGIN login_name T-SQL statement, where user_name and login_namerepresent the names of the new user and an existing login, respectively. At this point, you also have an option of granting privileges to the newly created user by executing the sp_addrolemember stored procedure (conversely, you would use the sp_droprolemember stored procedure to revoke existing privileges). For example, if your intention is to provision an account with the ability to manage logins and other databases, you would run the following code:

CREATE LOGIN 0th3rl0g1n WITH PASSWORD='[email protected]$y2Gu3$$';
CREATE USER 0th3ru$3r FROM LOGIN 0th3rl0g1n;
EXEC sp_addrolemember 'dbmanager', '0th3ru$3r';
EXEC sp_addrolemember 'loginmanager', '0th3ru$3r';

Keep in mind that each CREATE LOGIN and CREATE USER statement must be executed separately, since neither of them can be combined with any other within the same batch. The same rule applies to ALTER and DROP statements within the context of login and user management as well as to creating, altering, and dropping SQL Databases. You can verify the successful outcome of this procedure by enumerating users and their assigned roles with the following T-SQL query:

SELECT AS member, AS role
FROM sys.database_role_members
INNER JOIN sys.database_principals m ON sys.database_role_members.member_principal_id = m.principal_id
INNER JOIN sys.database_principals r ON sys.database_role_members.role_principal_id  = r.principal_id

In general, you should limit permissions granted to database users to a minimum, in compliance with the principle of least privilege. While your options in this regard are restricted in the master database allowing you to assign only server-wide roles (namely dbmanager and loginmanager described earlier), any non-system database offers more flexibility, including in the list of choices those available in on-premise SQL Server installations, such as db_accessadmin, db_securityadmin, db_ddladmin, db_backupoperator, db_denydatareader, db_denydatawriter, db_owner, db_datareader, and db_datawriter (with the last three of these being likely most commonly used). For example, in order to allow our user named 0th3ru$3r to read content of a database (other than master, where this privilege cannot be granted or revoked by leveraging built-in roles), you would assign it the role of db_datareader by executing EXEC sp_addrolemember 'db_datareader', '0th3ru$3r'while connected to that database (note that, as we mentioned earlier, due to the lack of support for the USE T-SQL command, you will first need to generate a new login while connected to the master database, then establish a separate connection to the target database where the new user will be defined, create that user, and finally associate it with a desired role). Similarly, you would use the db_datawriter role if you wanted to allow for writing (but not reading or updating). You should also keep in mind that you can leverage more granular and flexible authorization techniques, which rely on schema separation or individual object and statement based permissions.

Another potential concern that should be taken into account when evaluating security of interaction with SQL Database is data integrity and confidentiality. The primary approach to addressing this concern leverages SSL-based encryption that is enforced by Microsoft Azure. The efficiency of this approach is contingent on ensuring that a certificate used for tabular data stream (TDS) over SSL communication has been properly validated. Specifics of such validation depend on the method used to connect to SQL Database, which include the following:

  • SQL Server Management Studio – starting with SQL Server 2008 R2-based version of SQL Server Management Studio, you have the option of enforcing encryption and certificate validation by enabling the “Encrypt connection” check box on the Connection Properties tab of the Connect to Server dialog box.
  • sqlcmd – starting with SQL Server 2008 R2, you can enforce validation by including -N switch (which enables encryption) but excluding -C switch (which otherwise would facilitate implicit certificate trust).
  • ADO.NET – relies on database connection string to control certificate validation behavior, requiring the presence of Encrypt=True and TrustServerCertificate=False parameters.

Unfortunately in-place encryption-specific features incorporated into on-premise SQL Server installations (such as transparent data encryption or symmetric and asymmetric keys – along with corresponding T-SQL statements) are not supported in their Azure Platform as a Service (PaaS) counterparts. Effectively, if you want to provide an additional level of protection for data stored in SQL Databases you will need to resort to implementing a custom encryption mechanism within your cloud-based applications.

See all articles by Marcin Policht

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Latest Articles