May 162017
 

A while back, I posted an article about creating a WhiteList for access to SQL Server. Since then I have received a bit of feedback that it was not working as designed. My apologies for taking so long, but I believe I have come up with a fix.

The main issue is the trigger will block some or even all access to the server after it’s created. As it turns out, the issues were really permission being denied. To see it in action, let’s create everything using the original code from here.

We’ll add 1 row to the WhiteList table should allow all users from the workstation, ECHOBASE1, access, regardless of its IP address.

USE master;
GO
INSERT dbo.WhiteList(LoginName,HostName,HostIpAddress)
VALUES ('*','ECHOBASE1','*');
GO

Next, we’ll create a SQL login with only connect permission to the server and nothing else.

CREATE LOGIN LogonTriggerTest WITH PASSWORD = 'Password1';
GO

Finally, we’ll open a new query window using that login.

As you can see, we are denied access to the server because of the logon trigger. If we look in the Errorlog, we can see that we lack the VIEW SERVER STATE permission.

This was my first mistake. I did my initial testing using an administrative login. Most users are not admins; therefore, they will not have the permission required to view sys.dm_exec_connections. I was using this DMV to get the IP address of the client connection, and it requires the VIEW SERVER STATE permission. To get around this, I can use the CONNECTIONPROPERTY function, as it does not require any additional permissions.

SELECT CONNECTIONPROPERTY(‘client_net_address’);
GO

Now let’s try to connect again.

Again, we failed. This would be my second mistake. I failed to grant SELECT access to the WhiteList table. By default, a user is will have public permission to the master database, but no permission to the table. To solve this, we can grant permission to the public database role. This will allow any authenticated user to read from the WhiteList table.

USE master;
GO
GRANT SELECT ON dbo.WhiteList TO public;
GO

Finally, our connection to SQL Server is successful. Using the same code from the trigger, we can compare it what’s in the WhiteList table.

USE master;
GO
SELECT
   ORIGINAL_LOGIN() AS 'LoginName'
  ,HOST_NAME() AS 'HostName'
  ,CONNECTIONPROPERTY('client_net_address') AS 'HostIpAddress';
GO
SELECT * FROM dbo.WhiteList;
GO

 

If I had followed my own rules, I could have discovered most of these issues before posting the original article.

The fully updated code is below. Please let me know if you run into any other issues with this new version. I also added another column to the WhiteList table that can be used for hold comments. The idea is to provide some documentation about what the white-listed item is attempting to do.

USE master;
GO
IF OBJECT_ID('dbo.WhiteList') IS NOT NULL
  DROP TABLE dbo.WhiteList;
GO
CREATE TABLE dbo.WhiteList(
   Id INT IDENTITY(1,1) PRIMARY KEY
  ,LoginName VARCHAR(255)
  ,HostName VARCHAR(255)
  ,HostIpAddress VARCHAR(50)
  ,Comments VARCHAR(2000)
);
GO

GRANT SELECT ON dbo.WhiteList TO PUBLIC;
GO

INSERT dbo.WhiteList(LoginName,HostName,HostIpAddress,Comments)
VALUES ('*','ECHOBASE1','*','Any user from the workstation "ECHOBASE1" is allowed to connect, regardless of IP address.')
,('WebSiteLogin','webserver1','192.168.100.55','Only the WebSiteLogin from webserver1 with an IP of 192.168.100.55 is allowed access.');
GO

CREATE TRIGGER WhiteListTrigger
ON ALL SERVER FOR LOGON
AS
BEGIN
  DECLARE
   @LoginName VARCHAR(255) = ORIGINAL_LOGIN()
  ,@HostName VARCHAR(255) = HOST_NAME()
  ,@HostIpAddress VARCHAR(50) = CONVERT(VARCHAR(50),CONNECTIONPROPERTY('client_net_address'));

  IF(
    SELECT COUNT(*) FROM dbo.WhiteList
    WHERE ((LoginName = @LoginName) OR (LoginName = '*'))
    AND((HostName = @HostName) OR (HostName = '*'))
    AND((HostIpAddress = @HostIpAddress) OR (HostIpAddress = '*'))
  ) = 0
  ROLLBACK;
END;
GO
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: 192.168.100.55.

USE master;
GO
IF OBJECT_ID('dbo.WhiteList') IS NOT NULL
DROP TABLE dbo.WhiteList;
GO

CREATE TABLE dbo.WhiteList(
   Id INT IDENTITY(1,1) PRIMARY KEY
  ,LoginName VARCHAR(255)
  ,HostName VARCHAR(255)
  ,HostIpAddress VARCHAR(15)
);
GO

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

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

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

CREATE TRIGGER WhiteListTrigger
ON ALL SERVER FOR LOGON
AS
BEGIN
    DECLARE 
         @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;

IF(
  SELECT COUNT(*) FROM dbo.WhiteList
  WHERE((LoginName = @LoginName) OR (LoginName = '*'))
  AND
  ((HostName = @HostName) OR (HostName = '*'))
  AND((HostIpAddress = @HostIpAddress) OR (HostIpAddress = '*'))
  ) = 0
ROLLBACK;
END;
GO

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 192.168.100.55.

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;
GO

 

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:
https://msdn.microsoft.com/en-us/library/bb326598.aspx

Oct 102012
 

Right out of the box, SQL Server makes it pretty easy to grant SELECT, INSERT, UPDATE, and DELETE to all user tables.  That’s accomplished by using the built-in db_datareader (SELECT) and db_datawriter (INSERT, UPDATE, and DELETE) database roles in every user database.  Any user you add to those database roles will be granted those permissions.


But what if you want to grant EXECUTE permission to all of the user stored procedures.  Where’s the built-in database role for that?  Nowhere to be found. 

Oh you can create a database role, add users to that role, and grant EXECUTE permission to that role for each stored procedure that’s created.  But that’s just not as efficient as the built-in roles, mainly because you must always remember to grant the permission to the role every time you create a new stored procedure.

Here’s an easier way to create a database role that’s more like the built-in roles.

Create a database role and add a user to that role.

CREATE ROLE db_execproc;
GO
EXEC sp_addrolemember N’db_execproc’, N’User1′;
GO

Grant EXECUTE permission at the schema level.

GRANT EXECUTE ON SCHEMA::dbo TO db_execproc;
GO

Create a new stored procedure.

CREATE PROCEDURE dbo.p_test
AS
SELECT * FROM dbo.Table1;
GO

Login as the test user and execute the stored procedure.

EXEC dbo.p_test;
GO


This example granted EXECUTE permission to the dbo schema.  Any stored procedures that are created in the dbo schema can be executed by users who are members of the db_execproc database role. You can grant the same permission to other schemas if needed.  

This concept is not going to be for everyone and every database, but if you’re already using the built-in database roles to manage permissions within a database then this will be a good option for you.


For more details on database roles and grant schema permissions, check out Books Online.