Microsoft has taken SQL Server to the cloud and introduced SQL Azure Database – the as a service version of SQL Server. This isn’t new as the feature has been generally available since xxxx. It is also not quite a true software as a service offering, at least in the way that most other SaaS offerings occur. The SQL Server vm is still sitting behind the database, which handles the same functions as it might in an on-premises SQL Server. This is the part that, at least for me, makes this SaaS-ish because the SQL Server resource still gets created behind the scenes.

The purpose of this post is not to nit-pick the SQL offering from Microsoft, but to look at how it is able to connect to a VPN. To try this out in my Azure subscription, I created a local to Azure VPN from the gallery and configured a connection from my Fortinet firewall to connect to Azure. I have been testing out some of the features to see what works and what might not – the direct VPN connection to SQL is not available at this time.

Note:I am referencing Fortigate documentation in this post because my home firewall is a Fortigate 30e. Neither Fortinet nor Microsoft have had input into the content of this post.

[table id=1 /]

To connect the SQL Database to an endpoint to a virtual network. Doing this will connect this machine to a network and allow connection to any of the databases that exist on the server. Using SQL Server Management Studio and t-SQL you can configure access to individual databases. Configuring access to each database is a way to ensure that even the databases are segregated from each other.

Configure a Server Firewall for SQL Server

SQL Server in Azure is a logical entity used to manage the collective of databases assigned to it. It is similar to a SQL Server instance used in an on-premises server in that it provides a point of management for these databases. While you are required to create a server piece, you do not need to create if first. When you create the database, the Azure portal will provide required fields to get the server created as well.

Once the SQL Server and Database has been provisioned, the option to add firewall and security rules is available. Figure A shows the overview.

Figure AConfiguring Firewall Rules or VNet Access in SQL Azure

You will notice in the above image, that there are a few sections to configure:
•Azure Services Access – this configures the firewall to allow access for services within Azure.
•Client IP Address – this provides access from a source IP address to all databases connected to this SQL Server
•VNet connection/Endpoints – this section allows the specified virtual network (in Azure) to connect to and access the databases on this server.

When considering Azure Services Access, be aware that enabling this will allow any Azure service that has the DNS name or IP address of the SQL Server will be able to access the resource. For a production environment this may be a bit too open for many organizations. There are plans to limit this access, potentially using a reserved IP address on an Azure VM with a restriction to that IP address in the Azure SQL firewall – see this link for more information http://bit.ly/2BOLQNN. Will there be environments that you do wish to enable access for Azure Services, but plan those environments and consider all possible issues before enabling it.

The client IP address allows the administrator to limit access to a specific IP address. For access from an on premises network a public gateway IP address will need to be used. You cannot push traffic across a VPN to access the database. The connections and access will be limited to systems behind that IP address. The restriction helps control access to the server, and does not require enabling Azure Services Access. This is as close as you can get right now to “VPN” access to SQL Azure. Is the solution perfect? No it isn’t, but it is a start and does keep the SQL platform offering moving forward.

The last section is the VNet Connection, which configures an endpoint on the specified VNet allowing only traffic from the configured network – again helping to limit access to the SQL Server resource. VMs and other resources that exist on the configured virtual network will be able to access all databases on the SQL Server.

These are great security features and will help control access to the SQL Server; What happens if I want to restrict access at the database level?

Configure a Firewall Rule at the database level

Security at the database level is possible, but must be configured using T-SQL. The configurations are stored in the master database and can be used at a more granular level and are recommended depending on what your security goals are. If you are looking to provide access to a database for a specific group of users, but allow access to the SQL Server for an entire organization – this might just be the solution for you – or at least part of that solution.

For this, you will need to use SQL Server Management Studio or another Query Engine because the settings are configured in the database itself.

Note: SQL Database currently supports 128 firewall rules at the database level – while this seems like a large number for firewall rules, choose them wisely

To review the existing rules configured for a database, you can query the sys.database_firewall_rules in the master database:

select * from sys.database_firewall_rules

To create a database level firewall rule in SQL Azure you will execute T-SQL:

exec sp_set_database_firewall_rule @name = N’A firewall rule azure connections’, @start_ip_address = ‘0.0.0.0’, @end_ip_address = ‘0.0.0.0’

This rule allows Azure connections to this database – similar to enabling Azure connections in the portal. The example is nothing terribly fancy, just something to show the syntax of adding a firewall rule to the database.

Adding the rule calls a stored procedure and allows you to pass the following items in:
@Name – the name of the rule
@Start_ip_address – the starting IP address for the rule
@End_ip_address – the ending IP address for the rule

The same stored procedure will update existing rules when new IP address values are passed in. Controlling access to the database is done by allowing certain hosts (ip addresses) access to the resource – this access will still happen over port 1433. Remember, when adding access for an on premises server or workstation, the IP address will be the public IP address of your Internet connection because it is the source address on your network that SQL Azure database will see.

The recent release of VNet Endpoints to help in restricting access to SQL workloads in Azure is pretty significant. Sure it isn’t VPN only access from on-premises networks, but it is a start and can only mean more is coming as Microsoft uses customer feedback to improve the cloud database service. There are other rules/features and things to know about access to resources when using an Express Route connection – more on that can be found here: http://bit.ly/2shZSTP Express route connections have two public IP addresses, which are then required to be used as IP rules similar to VPN connections – because the circuit is private, the security is somewhat enhanced over a VPN.

I hope you will stand up SQL databases and tinker with the firewall configuration to understand how it will help your organization move some of it’s databases to the Azure cloud. While they do not support VPN connections right now, Azure is rapidly evolving and I would imagine more changes will be added as this evolves – I do not know if or when this will happen, but knowing how much work Microsoft puts into the features of Azure (including SQL database) I can only believe that this is being considered.