When planning for deployment of Azure SQL Database, it is important to consider all of the security measures that can be implemented as part of the defense in depth strategy. We have already covered in detail one facet dealing with firewall-based protection in one of our earlier articles. We have also briefly described authentication, authorization, and encryption characteristics inherent to this Platform-as-a-Service (PaaS) offering. Now it is time to take a closer look at some of the authorization options that allow you to control access to your data in a granular manner, down to the level of individual database objects and statement types.
Authentication and authorization mechanisms incorporated into Azure SQL Database are based, for the most part, on the same principles that are employed in full-fledged instances of SQL Server, which you can deploy on your internal network or in Azure Infrastructure-as-a-Service virtual machines. However, there are some important differences between them that you need take into account. The most fundamental one from the authentication standpoint is a lack of support for Windows integrated authentication. Effectively, users must explicitly specify their login credentials whenever they establish connectivity to the SQL Database. While this is equivalent to the traditional SQL Server authentication, even in this case, there is a minor but important caveat. More specifically, password resets, which trigger automatic re-authentication in the on-premises scenarios result in session disconnects, requiring terminating the existing session and reconnecting when working with Azure SQL Database. (This behavior was chosen intentionally to eliminate potentially negative performance impact of automatic reconnects, which would be more pronounced when dealing with cloud-based services).
From the authorization perspective, the methodology implemented in the Azure SQL Database is considerably more distinct on the server level. For obvious reasons, the omnipotent sa login and the corresponding sysadmin SQL server role (or for that matter, any other fixed server role) are not available. Instead, when dealing with management of a logical SQL Server (which constitutes the Azure platform-based construct representing a database management unit) hosting individual SQL Databases, you have to rely on two roles pre-defined in the master database, including:
- loginmanager – granting permissions sufficient to create and manage logins (rather than the securityadmin fixed server role)
- dbmanager – granting permissions to create and manage databases (rather than the dbcreator fixed server role)
This difference is reflected in the way server-level security is managed, preventing you from relying on the graphical interface of the Security folder in the Object Explorer of the SQL Server Management Studio, forcing you instead to run corresponding T-SQL statements while connected to the master database. (Although SQL Server Management Studio makes the process conveniently transparent by automatically redirecting you to the master database and auto-generating the SQL Login template in the query window).
User database roles match those existing in traditional SQL Server implementations and include the following:
- db_accessadmin granting the rights required to create and manage database users.
- db_backupoperator granting the rights required to back up the database.
- db_datareader granting the rights required to read data from all tables and views in the database.
- db_datawriter granting the rights required to write data into all tables and views in the database.
- db_ddladmin granting the rights required to create and manage objects in the database.
- db_denydatareader denying the rights to read data from every table and view in the database.
- db_denydatawriter denying the rights to write data into every table and view in the database.
- db_owner granting the rights required to perform all configuration and management tasks in the database.
- db_securityadmin granting the rights required to manage role membership and permissions in the database.
If your intention is to further granularize access rights and permissions, you have the option to take advantage of schema and object level security, which just as the database roles, is identical to the one database administrators are familiar from managing their on-premises workloads. In particular, you can use the GRANT, REVOKE, and DENY T-SQL statements to control permissions on per schema, object instance, or the statement level. Keep in mind that DENY takes priority over explicitly granted or role-based permissions.
To create logins, you need to establish a session to the master database, while creation of user accounts requires direct connectivity to the target database. This is relevant since it is not possible to switch the database context within the same session (as you would typically do by invoking the USE database T-SQL statement) – instead, you have to initiate a separate session for each database you want to manage. The initial login, also referred to as the server-level principal login is automatically provisioned (along with the user with the matching name in the master database) when you initialize the SQL Server instance, either from the Azure Management Portal, by using Azure PowerShell module (with the New-AzureSqlDatabaseServer cmldet), or by invoking the corresponding REST API. From the authorization standpoint, this login is unique since it gets implicitly assigned the rights associated with the loginmanager and dbmanager roles (although it is not actually their member). You can also use it to connect to any of the databases within the same logical server.
Subsequently, you can create additional logins by using the CREATE LOGIN T-SQL statement (to enumerate all logins, you can query the sys.sql_logins view in the master database). In order to connect to any database (including master) with their credentials, you need to create a corresponding user account in that database. Effectively, if your intention is to designate a new member of the loginmanager or dbmanager role, you would need to start by connecting to the master database by using the server-level principal login, creating a new login, then creating a user associated with this login (by using CREATE USER (…) FROM LOGIN T-SQL statement), and finally executing the sp_addrolemember stored procedure. Similarly, if you want to allow these logins to be used to connect or manage any user database, you would need to first connect to it, create an associated user account in that database (again, leveraging the CREATE USER (…) FROM LOGIN T-SQL statement) and, if desired, executing the sp_addrolemember stored procedure to assign the newly provisioned user to one or more database-level roles. However, this requirement does not apply to scenarios where members of the dbmanager need to establish connectivity to databases they created, since in such cases they are implicitly mapped to the dbo user account (which also implies the membership in the db_owner role).
This concludes our overview of managing data protection in Azure SQL Database in which we focused on permissions as an additional line of defense against unauthorized access. In our upcoming articles, we will continue our coverage of topics related to running SQL Server in the cloud-based and hybrid scenarios.
