Nov 172015

UPDATED – May 16, 2017 – Please review the updated code here.

In my previous post, Configuring the Azure SQL Database Firewall, I discussed how you can configure the firewall rules to manage access to the SQL Server. Today, I wanted to demonstrate how you can accomplish the same thing with your on-premise SQL Servers by creating a whitelist.

So let’s start off by defining “whitelist”. According to the American Heritage Dictionary, it is “a list of people or organizations that have been approved to receive special considerations”. In our example, that “special consideration” is access to the SQL Server only if your workstation IP address is on the whitelist.

So why would we care about this? Why not just manage access to SQL Server the normal way with logins and passwords. Here is one example. Let’s say you have a company policy that prohibits anyone from using a common login to connect to a SQL Server. But your application uses a single SQL login to make its connection to SQL Server, and EVERY developer in the company knows the password. Even though there is a written policy in place, what would prevent one of those developers form connecting to SQL Server to fix a bug, or worse, change data to circumvent the application logic.

A whitelist will define which logins are allowed to connect to SQL Server from a specific IP address. Using our scenario from above, we can walk through an example. First we need to create the whitelist table to enforce this policy: the application login (WebSiteLogin) should only be allowed to connect to SQL Server if it originates from the web server’s hostname (webserver1) and IP address:

USE master;
DROP TABLE dbo.WhiteList;

CREATE TABLE dbo.WhiteList(
  ,LoginName VARCHAR(255)
  ,HostName VARCHAR(255)
  ,HostIpAddress VARCHAR(15)

Next, we need to add an entry to allow access.

INSERT dbo.WhiteList(LoginName,HostName,HostIpAddress)
SELECT 'WebSiteLogin','webserver1','';

Just creating the whitelist does nothing. That’s why we need to use a logon trigger to enforce the whiltelist rules.

         @LoginName varchar(255) = ORIGINAL_LOGIN()
        ,@HostName varchar(255) = HOST_NAME()
        ,@HostIpAddress varchar(15);
SELECT @HostIpAddress = client_net_address
FROM sys.dm_exec_connections
WHERE session_id = @@SPID;

  SELECT COUNT(*) FROM dbo.WhiteList
  WHERE((LoginName = @LoginName) OR (LoginName = '*'))
  ((HostName = @HostName) OR (HostName = '*'))
  AND((HostIpAddress = @HostIpAddress) OR (HostIpAddress = '*'))
  ) = 0

The logon trigger simply compares the user’s login name, hostname, and IP address to what’s in the WhiteList table. If there is a match, then access is allowed. If not, then the connection is terminated via a rollback. To test this, we can attempt to login using WebSiteLogin from an IP address other than

Immediately, we are greeted with a logon error message.

In addition to comparing the logins, the code also adds a wildcard option using the asterisk (*).

SELECT * FROM dbo.WhiteList;


Line 1 has an asterisk for LoginName and HostIpAddress. This means that any login from any IP address is allowed to connect as long as the HostName is dbserver. Line 2 has an asterisk for HostName and HostIpAddress. This means the sa login is allowed to connect from any hostname or IP address. Line 3 is the example we used above. An asktrisk in all three columns would allow any login from any hostname or IP address to connect.

In the event you lock yourself (or everyone) out of the SQL Server, there is a quick way to restore access. You’ll need to connect to SQL Server using the Dedicated Admin Connection, either through Management Studio or the SQLCMD command line using a login with SysAdmin permission. Once connected, you can disable the logon trigger.

From the example above, you can see how quickly you can implement your own firewall for your on-premise SQL Servers using a whitelist and a logon trigger.

Additional resources about Logon Triggers:

Nov 032015

Azure SQL Database is a Platform as a Service (PaaS) that provides a relational database for use over the internet. That sounds super cool and easy to use. But wait, there’s one word I’d like to highlight in that first sentence: “internet”. Anyone with an internet connection could access your database. Now that’s no cool. So how does Microsoft keep your database safe? The answer is a multipronged approach of using encryption, authentication, authorization, and firewalls.

All connections to Azure SQL Database use SSL/TLS to protect your data while “in transit”, and you can use Transparent Data Encryption (TDE) to protect your data “at rest”. Authentication and authorization are no different from the on premise version of SQL Server. Authentication just means you must have a valid login to SQL Server, and authorization means you must have permissions on an object; for example, SELECT permission on a TABLE.

The other way Azure protects your data is by use of a firewall. It works like any other firewall; it blocks unauthorized traffic from passing through. By default, Azure blocks ALL traffic to your database. This may sound a bit crazy but it’s no different from the way the Windows firewall works; you must allow access through a port. In Azure SQL Database, it always listens on port 1433, so Azure uses the client’s IP address to authorize access. For example, if your workstation IP address is, then you would need to explicitly allow access to your database from that IP.

Azure SQL Database has several ways to configure the firewall. Access can be granted at the server-level or at the database-level. Each level can be managed through the Azure Portal, TSQL, PowerShell, or Rest API. Let’s take a closer look at the Azure Portal and TSQL options.

Configuring the firewall through the Azure Portal offers you two options; one will allow access from any Azure service, and the other will define server-level entries. The first is the “Allow access to Azure services” button. When this option is turned on, it allows any traffic from services within your Azure subscription to pass through. This is usually on by default when your SQL Server is first created.


The second way to manage the firewall through the Azure Portal is by defining server-level entries. On the Firewall settings page, you just need to give the setting a name, and then list the starting and ending range for the IP address. If it’s just a single IP address, then you would use the same value for both. The picture below shows a single entry and a range of IP addresses that are allowed to connect.


When defining the rules through the Azure Portal, you are just creating server-level rules under the hood. If we run a TSQL query against sys.firewall_rules on our SQL Server, we should see 3 entries.


There are the two entries we created: IP Range and Single IP. But there is also a third. That’s the entry for having “Allow access to azure services” set to ON. If we turn that option off and then rerun the TSQL query, we can see that entry for is removed.


If we wanted to create a server-level firewall from TSQL, then we would use the system stored procedure sp_set_firewall_rule.

EXEC sp_set_firewall_rule
     @name = N'TSQL Server Level'
    ,@start_ip_address = ''
    ,@end_ip_address = '';


To delete a server-level firewall rule, you can use the Azure Portal or the system stored procedure sp_delete_firewall_rule.

EXEC sp_delete_firewall_rule
     @name = N'TSQL Server Level';

To create the rule, it’s as easy as running a query using the system stored procedure, sp_set_database_firewall_rule, in the context of the user database.

EXEC sp_set_database_firewall_rule
     @name = N'TSQL Database Level'
    ,@start_ip_address = ''
    ,@end_ip_address = ''

We can query the sys.database_firewall_rules DMV to verify we have successfully created the rule.


Notice, I have added this rule to the DEMO database, which will give the IP address access to only that database. Now let’s try to connect to the database.

What’s this error? My IP address does not have access to the server? But I just added it as a database-level rule.


This error is because I’m attempting to connect to the default database, master, instead of the database, Demo, that I have explicit access. If I change my connection properties, then I will be allowed to connect to the Demo database.


After connecting, the first thing you will notice is there is only one database listed. Because I only have explicit firewall access to the DEMO database, I can’t even see DEMO2 on the list of databases.


To delete a database-level firewall rule, you can use the system stored procedure sp_delete_database_firewall_rule.

EXEC sp_delete_database_firewall_rule
     @name = N'TSQL Database Level';

In the event you lose all access to your database, either by entering the wrong IP address or removing all of them, there is an easy way to get it back through the Azure Portal. On the Firewall settings page, click the “Add client ip” button and click Save. This will add the IP address of your current client to the server-level rules list. Once you have server-level access, you can then connect to the server and correct the database-level rules.



The one thing to remember from all of the firewall settings, is these settings only open ports. There is no setting to deny an IP address.

As you can see, Azure provides several options for configuring the firewall settings for a SQL Database. I also think this is great example of how the Azure Portal can be viewed as nothing more than a different version of Management Studio. It’s just a graphical interface for managing SQL Server databases.

Additional resources: