What should I know when setting up my Azure SQL Database (PaaS)?

  • Article

This article is a consolidation of useful tips while setting up your own Azure SQL Database and covers covers single-instance Azure SQL DBs (Elastic pools require separate design considerations beyond the scope of this article).

Creating your new Database

Spinning a new SQL DB is essentially a 4 step process and can be done via Azure portal, PowerShell and Visual Studio (C#), step by step description available on Azure documentation (SQL DB get started, see all 3 modes at the top).

Tip: Read Design Considerations section below before you procure your database and prevent that infinite loop of troubles.

Connecting to the Database

Azure documentation (Connect to Sql database) covers how to connect through SSMS, C# and Excel. Will recommend a quick read on best practices related to connection pooling, timeouts, retry logic et al. Download the required connectivity libraries and get started with programming language of your choice, browse connections samples for .NET, Java, Node.js and many more here.

Most common error (once the firewall policy is added to allow access): A rule is added at the DB level to allow access for a given IP and you do not have access to server. This occurs when you have not specified the actual DB in the connection string so it defaults to master database that you do not have access. Changing your connection properties to include DB name with resolve this error.

Tip: If policy is added to database level, you will only see databases that you have access to (other DBs will be hidden from you)

Design Considerations

Firewall

By default, (when you create a new DB), Azure blocks ALL traffic to your database. The foundation is similar to Windows firewall and hence you must allow access through a “specific” port (Azure SQL Db always listens on port 1433). There are several ways to configure the firewall (Portal, TSQL, PowerShell, REST API) setup process described here. Access can be granted both at the server-level or at the db-level and it follows the hierarchy server ->database hence if you need to add a common range of IPs that can access all DBs you need to add at server level. Both the levels can be managed through Portal, TSQL, PowerShell and Rest API. For ports beyond 1433 , ADO.NET 4.5 or later (TDS 7.4) SQL database V11 always connect using proxy route (ADO .NET -> Middleware -> SQL) however with V12 there is now clear separation between Outside and Inside of Azure boundary (Azure doc). For outside, port 1433 is the only port that must be open at the application end. For all inside calls, Azure uses direct route (ADO.NET -> SQL) using the port ranges 11000-11999 and 14000-14999 so please ensure these are left available.

Tip: Block TCP Port 1433 for inbound only since Azure uses TCP port 1433 for outbound connections (for the TDS protocol) hence need to be opened however, make sure it is blocked for inbound connections and used only for outbound.

Access within Azure: This can be toggled by “Allow access to Azure services” Yes/No button on the portal (Firewall settings page). Please note, enabling this feature would allow any traffic from resources/services hosted in Azure (not just your Azure subscription) to access the database. 

Access at the Server-level (On-Premise or anywhere outside Azure) : On the Azure portal -> Server -> Firewall settings page, add a policy (give a name) and list the starting and ending range for the IP address (will not suggest using single IP address as these change often and you may need to repair often). All these rules are “server-level” rules and can also be easily created through TSQL by using sp_set_firewall_rule (use sys.firewall_rules  to find what rules exist your DB server and sp_set_database_firewall_rule to create policy at database level). You will see a specific entry for “Access within Azure” if turned ON.

Tip: These firewall settings are only to open ports, there is no setting to deny an IP address.

Encryption

All connections to Azure SQL Database use SSL/TLS to protect your data while its “in transit”. Azure SQL Database can help protect your data by encrypting your data when "at rest," or stored in database files and backups, using Transparent Data Encryption. Additionally, you may consider using Cell-level encryption (specific columns or even specific cells of data).

Authentication

Authentication refers to how you prove your identity when connecting to the database and ensures you must have a valid login to SQL Server. Azure SQL Database supports two types of authentication (more details here..)

· SQL Authentication, which uses a username and password

· Azure Active Directory Authentication, which uses identities managed by AAD (Azure documentation on set-up)

Authorization

Authorization ensures you have permissions to access DB objects and is similar to the on premise SQL Server. Authorization/manage database access and login policies – excellent Azure documentation comparing differences between on-premise and Azure SQL DB access management (more details here..).

Connecting Azure DB from an on premise firewall protected application

Local firewalls typically restrict port 1433/1434 outbound and could be an uphill task in organizations to get changed (these are restricted due to SQL slammer and Snake viruses) however, SQL Azure uses port 1433 for communication. If the port is blocked, the connection attempt times out hence there could be a few solutions possible:

  1. Create VPN connection to another network that has firewall rules configured to allow access to 1433. This workaround does add latency to the SQL Azure communication hence other approaches are generally considered.
  2. Use Port Bridge (Service Bus implementation): is an application that provides point to point tunneling between client and a server by wrapping the protocol and port used within a SOAP message (taken from Clemens Vaster’s post) . This message is further relayed using Azure AppFabric Service Bus and can be unwrapped at the destination. Service Bus provides the ability to connect on premise applications across multiple premises or to connect on premise application to the cloud. Service Bus ensures your endpoints do not have to be published directly to the Internet but just have Internet access thereby addressing the firewall, Dynamic IP and NAT challenges.
  3. Use Reverse proxy tools like Hexatier (formerly GreenSQL): Available in Marketplace (https://www.hexatier.com/hexatier-for-sql-azure/)
  4. Create specific VNET between On-premise application and Azure SQL DB – VNET support for Azure SQL DB is a feature under implementation (and will be available as future release).

Word of Caution

  1. While Azure SQL database is automatically updated by Microsoft, it is your responsibility to ensure all security updates and patches are applied to applications accessing the database (in case they are built and managed on-premise).
  2. Ensure all communications between Azure and your apps is SSL encrypted at all times. Please note that your apps need to explicitly request a secure connection to prevent any injection based attacks. If not using SSL certificate, ensure your apps do not accept other server certificates since they may not be secure
  3. DB user in the master database corresponding to the server-level principal login cannot be altered or dropped. To access the master database, every login must be mapped to a user account in the master database.
  4. If you do not specify a database in the connection string, you will be connected to the master database by default.

SQL Injection prevention and Advanced firewall definitions

SQLi is arguably one of the top threats to a database and basic firewall protection cannot always prevent such attacks (given the nature that the attack comes from compromised yet legitimate IP addresses). While implementing best coding practices goes a long way, I would recommend using Hexatier (formerly GreenSQL) like apps that ensures great degree of firewall protection (includes automated SQLi injection detection). Hexatier also provides advanced firewall definitions to assign granular-level definitions specifically to the individual (DBAs, Testers, Developers), IP addresses and Apps that can access specific areas of the database. ((https://www.hexatier.com/hexatier-for-sql-azure/)

Parting thoughts…

All said above, there is great documentation available on Azure documentation website and it continues to be upgraded as newer features are rolled out so please use that judiciously over all other sources https://azure.microsoft.com/en-us/documentation/articles/sql-database-get-started/