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.
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.
– which will show currently configured restrictions with the details over the feature, object_class & object_name.
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
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 :).
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.
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.
Hi, Niko. Great thanks for very nice article, but what you think about Solomon Rutzky opinion: Feature Restrictions in SQL Server 2019 are Worse Than Useless: a False Sense of Security And Wasted Opportunity?
With all the due respect – I tend to disagree with him.
He has some really great points on the permissions and some of the restrictions, where some of them are bugs and should be fixed before RTM (DMV access, DBCC FREESYSTEMCACHE, Multiple WaitFor’s).
I see the usefulness of this feature in pure production systems and for me error logging should be implemented as a part of the system.
The custom errors are totally untraceable, because system can never know what is really a sensitive part of the operation and what’s not.
Execute as DBO is naturally handled as unlimited as the SysAdmin role.
The importance of the security and data access in the enterprise environments is on the rise and giving this feature a chance is a good thing.
Hello Niko and thanks for that feedback. I’m still not sure that I understand the actual purpose of this feature. You say that you see the usefulness of this feature, but also agree that custom errors and `EXECUTE AS ‘dbo’` (an unfortunately common use case when dealing with Dynamic SQL) are probably not restrictable. You also said, “they are starting to limit the attacking surface and very simple cases”. So then why put in this effort? Are they actually limiting anything, given how easy it is to work around? Or is the phrase “very simple cases” an understatement?
I completely agree with “The importance of the security and data access in the enterprise environments is on the rise” (from your comment above), and I have been working quite hard at increasing the community’s awareness / level of knowledge regarding Module Signing ( https://ModuleSigning.Info/ and giving presentations on it). But, I don’t see this feature as anything but a distraction. The cases it solves right now are so trivial that they will only stop people who were not really a threat in the first place.
I agree with the need for “more knobs / more control”, but why create a whole new infrastructure instead of adding granularity to the existing permissions structure (meaning: create actual named permissions for things like WAITFOR, TRUNCATE TABLE, RAISERROR, etc), such that any command could be REVOKEd, and it would fit cleanly into the existing system. OR, maybe find ways to scan user input to restrict / filter out certain phrases or patterns. This new feature just smacks of being one big code smell / technical debt; it’s a very real problem that deserves serious attention, but instead of getting valuable time / energy put into solving it, we are getting (at best) a band-aid.
Take care, Solomon..
thank you for the comment.
First of all – I think you are presenting really great points.
My point of view is that the current (as in new) Microsoft works very much 6-month project and big paying customer oriented. This means that by far the most improvements will be incremental (as in no-complete). Especially since the partial debacle of In-Memory (huge investment & very few ) – totally an unfinished product, which I still love, by the way. :)
There are a couple of noticeable exceptions such as Sql Server on Linux, Big Data Clusters, etc – but even with them (SQL on Linux is still getting up to the full feature set with CDC making CTP 3.2 of Sql Server 2019 as a practical example) and Big Data Clusters is not yet released so the judgement should be delayed after post-RTM.
What I am trying to say is that limiting some of the most simple cases by increasing the defense mechanism externally (with a configuration instead of code modification) is a great way to increase the security of some of the system.
It is virtually impossible to prevent total hacking of the system, but increasing the difficulty and raising the bar a little bit is a positive thing.
I guess that there is some customer (maybe even internal in MS) that by some laws or auditory was required to have this feature and here we go – it is in Sql Server 2019. Even though
So many people are talking about the security, but almost no-one is implementing it in the real world, at least to my experience.
All together I see this feature as a positive step forward.
But hey, since most of the time I am wrong – maybe it is all useless and maybe Microsoft will remove it before RTM…
By the way, I am totally agreeing with you on the need of more granular permissions … well … starting with the SQL Agent :) hahaha
Hi there, Niko.
> “limiting some of the most simple cases by increasing the defense mechanism externally (with a configuration instead of code modification) is a great way to increase the security of some of the system.”
Sure, no code changes does make things easier and more likely to happen. I am just saying that _this_ particular method of doing that is _not_ a good way to accomplish that goal.
> “increasing the difficulty and raising the bar a little bit is a positive thing.”
Yes, it is. I am just saying that “Feature Restrictions” does not do this.
I think a lot more could have been done with an entirely different approach. “Feature Restrictions” is attempting to fix the symptoms and instead of focusing on the cause. Finding a way (or ways) to prevent external manipulation of the query is a much narrower scope to account for (only so many ways to break out of a query, but too many things can be done once you break out), and hence much more likely to be successful (i.e. effective).
Just my $0.02 ðŸ˜º
Take care, Solomon..
thank you for the discussion!
I feel we agree that a much bigger improvement can and should be done.
The thing I am basing my satisfaction on my current interpretation of the current development & implementation politics of Microsoft for Sql Server that will prevent the most of the major work to be done and because of that mostly incremental & incomplete updates are making the list of What’s New.
There are so many even more important things to implement and to fix that are out there, and some of them are totally not getting even a bit of attention.
I feel positive about this feature because looking forward I can imagine further improvements, and knowing that their development will depend on our (user) support I tend to give at least minem in order not to have abandoned features. Given that I can see their positive impact, I tend to agree with their existence more than to wish their removal.
Hi Niko. I think I understand your point. I might not agree with your optimism, but I do appreciate the discussion :-). I have incorporated some of my thoughts from these comments into the conclusion of my post. I also just added a link to a suggestion I made a while back for something that might not only be better for this case, but also handles other cases where application code cannot be changed:
“Intercept query batch from client to rewrite it or cancel it” ( https://feedback.azure.com/forums/908035-sql-server/suggestions/32896183-intercept-query-batch-from-client-to-rewrite-it-or )
Take care, Solomon…
Nice feature, especially if the number of features will increase. Didn’t test it, but I know there’s also traceflag 3625 which “Limits the amount of information returned to users who are not members of the sysadmin fixed server role, by masking the parameters of some error messages using ‘******’. This can help prevent disclosure of sensitive information.” Curious if this is giving the same results
did not knew that – thank you for sharing!
I did a test with this traceflag and the behaviour of this flag is the same as enabling the restriction on Errormessages. Note: you can only activate this flag at startup
Great info – thank you for sharing!
Makes sense that the Feature Restrictions is a further development/customisation of an already existing feature.
Hi Niko. Just to follow up on this, the “Feature Restrictions” feature has been disabled as of the RTM release of SQL Server 2019. The system procs and view are still there, but executing the procs returns an error. I have this documented at the end of my post:
Take care, Solomon…
This option is disabled by microsoft due so a sql injection vulnerability, see this article: https://sqlquantumleap.com/2019/08/05/feature-restrictions-in-sql-server-2019-are-worse-than-useless-a-false-sense-of-security-and-wasted-opportunity/
yeap, thank you – Solomon has already posted it here in November of 2019.