SQL Server 2019: Feature Restrictions

UPDATE 2020-02-28: It looks that by RTM this feature has been disabled, check out blog by Solomon Rutzky for more information.

With the release of the Sql Server 2019 CTP 3.2 we have received a new SECURITY feature, that is called “Feature Restrictions”, this one is really focused on the security aspect of the applications and more specifically the SQL Injection prevention.

For the last couple of years I have been asking and arguing that we need a knob for controlling the availability of the features for the Sql Server and Azure SQL Database, which besides the Security aspect can also allow to prevent developers from deploying deprecated or not really supported for production within the organisation features.
UNFORTUNATELY, this is not the feature that some people were hoping for, but still this “Feature Restriction” is a useful feature that I will definitely evaluate for some of my clients, once Sql Server 2019 will become available.

Feature Restriction

SQL Injection is pretty old problem that seems to have never got a serious solution over the years. Countless numbers of people recommended to avoid using easy injectable query constructions with EXECUTE without typecasting of the parameters, and at this moment I do not believe that unless there is a serious dragon with a very security-aware organisation, checking every single line of the code to be deployed – there is no way of preventing of someone doing a quick fix deployment and potentially allowing to takeover, to delete or to expose the sensitive information.
There were some hopes for the WITH RESULT SETS feature in Sql Server 2012, but as I have pointed out in Troubles in Exec @stmt With Result Sets, there is no real prevention mechanism against the SQL Injection.
If you do some homework and research about the problem you will see that there are so many creative ways, with the help of Unicode or character conversion to inject the needed command that you will understand why in the past 2 decades even major efforts of prevention within such languages as PHP were considered to be laughable.

Right now at the initial preview release there are just 2 features that can be restricted, but I believe that with the eventual success of this feature, more will follow. The currently available features to be restricted are :
– ErrorMessages, which will prevent any data within error messages to be shown to the specified user, by masking the system data details – substituting it with asterisk “*”.
– Waitfor, which will cancel any eventual delay and will finish executing the current command right away.

The following functions have been added to the T-SQL Surface in order to support adding, viewing and removing the restrictions:

sp_add_feature_restriction feature, object_class, object_name

– for restricting any of the 2 available features, where object_class will specify the either User or Role to point the class of the object that will be restricted and object_name will indicate the name of the User or Role within the database to be restricted.

sp_drop_feature_restriction feature, object_class, object_name

– for removing restriction of any of the 2 available features, where object_class will specify the either User or Role to point the class of the object that will be restricted and object_name will indicate the name of the User or Role within the database to be restricted.

DMV sys.sql_feature_restrictions

– which will show currently configured restrictions with the details over the feature, object_class & object_name.

Setup

Let us create a test sql user that will be called “appuser” with the permissions as db_datareader, db_datawriter as well as the execution permissions on our test TPC-H 10 GB Database (generate your own easily with a free tool HammerDB) as shown in the code below:

USE [master]
GO
CREATE LOGIN [appuser] WITH PASSWORD=N'n4,f^yiyE`2/og8IHR', DEFAULT_DATABASE=[master]
GO
USE [tpch_10]
GO
CREATE USER [appuser] FOR LOGIN [appuser]
GO
ALTER ROLE [db_datareader] ADD MEMBER [appuser]
GO
ALTER ROLE [db_datawriter] ADD MEMBER [appuser]
GO
GRANT EXECUTE TO appuser;

Let us also create a simple Stored Procedure that will read top 10 rows from the Lineitem table using the predicate l_shipdate that will identify the shipping date by the predicate:

CREATE OR ALTER PROCEDURE [dbo].[PleaseDoNotInjectSQL]( @param NVARCHAR(250) ) AS
BEGIN
	DECLARE @sql NVARCHAR(MAX) = (N'SELECT TOP 10 * FROM dbo.lineitem WHERE l_shipdate = ''' + @param + N'''');
	EXECUTE (@sql);
END

ERRORMESSAGE Restriction

Let’s dive into the first Restriction Feature – the ErrorMessage, which prevents sensitive user data to be shown in the error messages. This is a kind of simple filtering and prevention of data leaking mechanism that should mask the data and not expose to the user/role that is facing this restriction.
To add a new restriction for our newly created user (in the setup section of this post), we shall need to execute the sp_add_feature_restriction stored procedure:

EXEC sp_add_feature_restriction N'ErrorMessages', N'User', N'appuser';

select * from sys.sql_feature_restrictions

On the left you can see the output of the simple query that delivers information on the currently enabled Feature Restrictions and where we can see that a user [appuser] should be unable to see the full error messages, if everything goes correctly as expected.

To test it, let’s invoke the [dbo].[PleaseDoNotInjectSQL] Stored Procedure with the parameter set to the 6th of October 1992:

execute [dbo].[PleaseDoNotInjectSQL] @param = N'1992-10-06'

The successful output of the Stored Procedure is presented below:

Everything works fine as expected.

Now, we can do a different kind of test where we expand the parameter a little bit with another column l_shipmode where we without the knowledge assume that it is an integer column

execute [dbo].[PleaseDoNotInjectSQL] @param = N'1992-10-06'' AND  l_shipmode = 2--';

The default error message is very clear, which besides indicating the exact column name that provoked error will also display the value of the cell where it took place:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'SHIP      ' to data type int.

To test the restrictions on the error messages, let’s execute it as a user [appuser] to check what kind of output we shall receive:

execute as user = 'appuser';
execute [dbo].[PleaseDoNotInjectSQL] @param = N'1992-10-06'' AND l_shipmode = 2--'

This time the error message is similar though different:

Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the ****** value '******' to data type ******.

This looks significantly better with no details on the datatype, value or even datatype.

Let’s test if we can drop the restriction by executing the Stored Procedure sp_drop_feature_restriction:

EXEC sp_drop_feature_restriction N'ErrorMessages', N'User', N'appuser';

Worked like magic:

select * from sys.sql_feature_restrictions;

There are no restrictions listed and that’s exactly what one would expect :).

WAITFOR Restriction

A lot of times in order to obtain information about the data or some restrictions, the WAITFOR functionality shall be used. A good practical example would testing if the injection operation is successful would be simply by adding a one minute delay and if the webpage takes 1 minute to respond instead of 2-3 seconds as previously, then we have succeeded.

In order to prevent this vector of the attacks, we can restrict the usage of the WAITFOR function for the specific user or role by executing the following statement:

EXEC sp_add_feature_restriction N'Waitfor', N'User', N'appuser'

select * from sys.sql_feature_restrictions;

You can see that we have simply disabled right away the usage of the WAITFOR command, which will result in an instant execution of the code.

Running the following statement with our default user (SYSADMIN in my case), will take over 1 minute, because I have requested a delay of 1 minute:

Execute [dbo].[PleaseDoNotInjectSQL] @param = N'1992-10-06''; WAITFOR DELAY ''00:01''; SELECT 1--'

, but running it as an [appuser] will finish almost instantly, simply ignoring the 1 minute delay and returning 2 result sets, the regular for the 6th of October of 1992 plus a number one, that I have specified in the parameter.

 execute as user = 'appuser'
 execute [dbo].[PleaseDoNotInjectSQL] @param = N'1992-10-06''; WAITFOR DELAY ''00:01''; SELECT 1--'

Feature Restriction RESTRICTIONS :)

If you are targeting non-existing columns, the restricted information will not filter the data and specify that the column does not exist (allows potential discovery of the columns).
If you are using SYSADMIN or Database Owner (dbo) specific roles, then those can not be limited (but here I guess you should reconsider how your application is accessing the data).
No system databases are supported, besides the [MSDB].

Fix it the old way

Even if you can’t change the legacy parameter invocation, try to update your Stored Procedure code making parameter typed and casted correctly, leaving to the SQL-Injection less chances for success:

USE [tpch_10]
GO

CREATE OR ALTER PROCEDURE [dbo].[PleaseDoNotInjectSQL]( @param NVARCHAR(250) ) AS
BEGIN
	DECLARE @shipdate DATETIME = CAST( @param as DATETIME);
	DECLARE @sql NVARCHAR(MAX) = N'SELECT TOP 10 * FROM dbo.lineitem WHERE l_shipdate = @shipdate';
	EXECUTE sys.sp_executesql @sql, N'@shipdate DATETIME', @shipdate = @shipdate;
END

Trying the same trick of the injection of the l_shipmode filter will fail instantly:

execute [dbo].[PleaseDoNotInjectSQL] @param = N'1992-10-06'' AND l_shipmode = 2--'

with the following error message:

Msg 241, Level 16, State 1, Procedure dbo.PleaseDoNotInjectSQL, Line 7 [Batch Start Line 0]
Conversion failed when converting date and/or time from character string.

But once again, if you have a vendor application that you just RUN, then those new features can be wonderful friends in trying to limit the attack surface area.

Final Thoughts

I think that this is a useful feature with a very low profile start, very similar to other features that Microsoft has been launching in SQL Server in the past decade.
First of all, there are very few applicability and usability cases with just functions (and I guess there is an important internal or external customer who begged for this feature). Just think about the Row Level Security in SQL Server 2016+ :).
Secondly, do not think as those restrictions will save you from all possible vector of attacks – they are starting to limit the attacking surface and very simple cases, but as you should know – the human creativity is a complex thing and in a house with thousands of doors, closing one or two will not prevent attackers from taking control of the house.

In the end, of course you should be prepared an using Stored Procedure sys.sp_executesql with the parameters for the dynamic SQL in the production.
Using TRY CATCH logic will allow you control the narrative of what gets delivered back and what should be simply logged for later analysis.

For me, I like the feature that is start moving the SQL Server into the space of better data protection and I am definitely waiting for further developments in post – SQL Server 2019 world.

Oh, and I really wish that we could disable any feature usage, as I wrote in the opening part – but that is a totally different need and a different story.