- Microsoft Azure SQL database is based on Microsoft SQL Server 2016. Here are some restrictions and limitations that you will want to know about when using Azure SQL.
- Azure SQL only implements a subset of T-SQL
- Microsoft Azure SQL Database does not support SQL Server Agent
- Not having data hosted locally so to get data to an Azure instance is to upload it or sync across the network
- Different server-level roles, hashing algorithms, and permissions
- Contained databases
- Consider adding enhanced security management tools to cloud-based databases
- Server auditing is not supported
- Domain Authentication requires exposing the Active Directory to the cloud
- USE command is not allowed, therefore, you must specify which server it’s connected to for every app. Although it improves security but you create multiple requests to the databaseFor more information about Microsoft’s limitations, here:
As a part of AWS and Microsoft’s Partner Network, HexaTier has better knowledge for standing up against any threat. Helping companies secure databases with a comprehensive solution that ensures security, discovery of sensitive data, dynamic data masking, and database activity monitoring. Third party solutions like HexaTier provide a comprehensive solution that enables organizations to overcome security limitations while using Azure SQL database.
Figure 1: HexaTier’s patented database reverse proxy technology, sits between applications/users and azure, hides and secures the database, analyzing both queries and responses.
Azure SQL Database Security Best Practices
When implementing the database, database administrators need to appropriately configure the Azure database.
- Block TCP Port 1433 for inbound only. Azure uses TCP Port 1433 for outbound connections for the TDS protocol. Therefore, this port will need to be open. However, you need to make sure it is blocked from inbound connections and used only for outbound
- Ensure that any app that is trying to access the database is up-to-date. Cloud-based Azure database is automatically updated by Microsoft, but it is the customer’s responsibility to make sure all security updates and patches are applied to apps accessing the database
- Configure Azure SQL DB Firewall correctly according to the needs for your database
- Ensure that all communications between Azure and your apps are SSL encrypted at all times. Note that your apps need to explicitly request a secure connection. This must be implemented in order to avoid potential intermediary attacks
- If you have not used SSL certificate communication, make sure your apps do not accept other server certificates, as they may not be secure
Note that all users, including apps, must provide credentials whenever they connect to the Azure SQL database and use best practices accordingly. Also, the password reset is highly secured, and when resetting a password it may take as much as 60 minutes between re-authentications. If the password has been changed, the request will fail and the session will disconnect.
Additional Login Restrictions
- The database 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.
- If you do not specify a database in the connection string, you will be connected to the master database by default.
- You must be connected to the master database when executing the CREATE/ALTER/DROP LOGIN and CREATE/ALTER/DROP DATABASE statements.
- CREATE USER statement with the FOR/FROM LOGIN option or the ALTER USER statement with the WITH LOGIN option, it must be the only statement in a batch.
- For more information on the Azure User Management Console, see the AUMC open source project onCodePlex.
The Azure SQL is a fully contained database. It employs a multi-tenant environment. Currently, many of the SQL server capabilities are not available, so that users can use database-level assets only. It is expected that these limitations will be addressed in a future version of the Azure SQL Database with a contained implementation. Built-in Azure SQL Database Firewall.
Built in Azure-SQL Database Firewall
Microsoft Azure SQL includes a basic database firewall with the product. The server-level firewall allows the definition of permissions based on originating IP address. When set, all access from other IP addresses is prevented. While this provides some protection, it’s a fairly basic level of the firewall.
For example, this type of restriction is an all-or-nothing limitation. It is unable to define particular tables, parts of the database, or applications for accessing the database. Once an IP address is allowed, access to anywhere in the entire database is allowed from that address.
The rules are configured via the Microsoft Azure Platform management portal. The relevant commands are in the SQL Database Management REST API or System SPs&views (sys.firewall_rules, sp_set_firewall_rule and sp_delete_firewall_rule).
The Azure DB Firewall also allows database-level firewall rules, restricting access to specific databases within the server. This set of rules is an extension to the server-level rules. Again, these are IP-based and do not allow the definition of groups or specific apps or tables. These are configured via system SPs&views sys.database_firewall_rules, sp_set_database_firewall_rule and sp_delete_database_firewall_rule12
Performing penetration testing is a precaution you should take, to ensure that you have covered all the basics of database security. Microsoft offers a document that gives instructions and allows you to easily record the results of your penetration testing.
At the time of writing of this article, many of the compliance requirements were not yet fully supported by Azure SQL Database. It is possible to boost the compliance using additional security products, as discussed later in this article.
If you are concerned about compliance, make sure to stay updated on the current status of the Microsoft Azure compliance at https://www.windowsazure.com/en-us/support/trust-center/compliance/.
Auditing and Monitoring
Currently, the Azure SQL platform does not provide monitoring of all actions. While attacks from illegal IP addresses are blocked, you have no record of the attempts. This can be important information both for compliance and for improving your security on the database.
Monitoring can be implemented using a monitoring and auditing tool that supports cloud databases. Because Azure is cloud-based, the monitoring tool also needs to be cloud-based. A reverse- proxy based database firewall such as HexaTier is able to provide full monitoring of all breach attempts or illegal attempts to access your database.
General best practices
Using a cloud-based database such as Azure is subject to the same types of basic database security best practices you would implement in any database. The general idea is to stay up-to-date and:
- Monitor database breach attempts (described above)
- Conduct regular penetration testing (described above)
- Prevent SQL injections
- Set advanced firewall definitions
- Ensure separation of duties
- Mask the database
SQL Injection Prevention
SQL injection (SQLi) continues to be one of the top threats to the database. Basic firewall protection cannot always prevent such attacks because the nature of SQLi is that it comes from IP addresses that are legitimate. The IP origin may be another database or app that has been compromised or a legitimate user whose credentials have been compromised.
Best practices for coding of all applications will go a long way towards preventing SQLi, however, it is impossible to block all of the gaps. Therefore, it is recommended to use a professional cloud-based firewall such as HexaTier that includes automated SQLi injection detection, and can also detect deviations from normal behavior to identify new SQLi injections, to prevent zero-day attacks.
Advanced Firewall Definitions
Advanced firewall definitions provide the capability to define access to databases specifically. Granular-level definitions allow you to define the individuals, IP addresses, and apps that can access specific areas of the database. Solutions such as HexaTier, let you define access down to the table level, so you can make sure that DBAs, testers, apps, and others have access only to the specific tables they need.
Separation of Duties
Recently, separation of duties has garnered much attention due to new regulations in the EU. Regulatory authorities have recognized that different individuals with access to a database should have different permissions. Often, organizations don’t even know who has access to a database, much less the precise permissions of each group or individual. Separation of duties allows you to define different levels of use for different database administrators, even down to the command level. For example, testers and developers may be blocked from deleting data or from accessing specific tables in the database.
Database masking enables companies to hide specific data fields from individuals or applications accessing a database. Typically, developers and testers need access to the database in order to perform their jobs. However, they do not need to see the actual data, and in many cases may be prohibited from viewing sensitive data, such as PII or financial data. For such cases, it’s necessary to use data masking to allow developers and testers to use data without viewing the actual values.
Typically, using a cloud-based solution such as Azure will require a dynamic database masking solution, unless you are creating a separate testing or non-production database. Dynamic versus static database masking is covered in a separate article, here.
Database Authentication Proxy
HexaTier enables enterprises to use DBaaS and/or external databases while maintaining organizational policies. The Database Authentication Proxy feature allows configuring the database instance (DBaaS or out of your Active Directory network) with a static and secure username and password while connecting HexaTier to the organization’s Active Directory/LDAP.
With HexaTier you can deliver a transparent Database Authentication Proxy layer, which means that the HexaTier’s Database Reverse Proxy system, will authenticate the domain users with the local Active Directory/LDAP, and transparently create a connection with the external database using the provided username and password.
Microsoft Azure SQL has made tremendous progress in becoming a secure database. However, there are still a number of limitations that require attention if you are interested in making sure your database is both secure and compliant with regulations.
As a unified database security and compliance solution that works with a reverse-proxy technology, HexaTier offers multiple key elements needed to compliment what’s missing in Azure, including Database Security, Discovery of Sensitive Data, Data Activity Monitoring, and Dynamic Data Masking. As shown in the diagram below, HexaTier can be installed on Azure’s server. Every query or command to the Azure SQL server is required to go through HexaTier’s reverse proxy technology. The solution provides:
- SQLi prevention
- Complete monitoring of all attempts to access the database
- Real-time alerts and blocking
- Sophisticated firewall rules
- Separation of duties
- Database dynamic data masking
- Auditing for automatically locating and classifying sensitive data
- Compliance for a wide variety of regulatory requirements
- Authentication Proxy