Feature Restrictions in SQL Server 2019 are Worse Than Useless: a False Sense of Security And Wasted Opportunity

(last updated: 2020-01-12 @ 16:00 EST / 2020-01-12 @ 21:00 UTC )

First, let me say how much I love some of the new functionality in SQL Server 2019:

  1. Using In-Memory tables for [tempdb]
  2. Adaptive inlining of T-SQL scalar functions
  3. Optimizing indexes for sequential inserts (i.e. IDENTITY)
  4. Better table variable statistics
  5. Batch mode for rowstore tables
  6. MSDTC for SQL Server on Linux
  7. sys.dm_exec_query_plan_stats DMF
  8. Memory-optimized TempDB metadata
  9. Reduced recompilations when using temp tables across multiple scopes
  10. and quite a few others…

However, nobody’s perfect and occasionally the SQL Server team makes a mistake. A new feature introduced in SQL Server 2019 CTP 3.2, “Feature Restrictions”, is an unfortunate example of such a mistake. It’s a tragically misguided attempt at improving security that not only increases the chances of SQL Injection, but it also prevented useful changes from being made. “Misguided” because it doesn’t even accomplish it’s stated goal, and “tragic” because it a) most likely increases the chances of SQL Injection, and b) used up the time that could have been spent on implementing useful changes.

What is “Feature Restrictions”?

(NOTE: Please see the 2019-10-28, 2019-11-10, 2019-12-10, and 2019-12-16 Updates regarding the disabling of, and removal of documentation for, this feature.)

(NOTE: Documentation links take you to the final revision of the page on GitHub, before that page was officially removed.)

Feature Restrictions accepts that Dynamic SQL exists and that code exists in your system that concatenates unchecked (or inadequately checked) user input into the query to be executed (as opposed to using parameters and/or thorough validations). The idea of this feature is to disable certain features used by hackers to gain knowledge of the system.

Ideally, application code is developed so it does not allow for SQL injection. However, in large code-bases that include legacy and external code, one can never be sure that all cases have been addressed, so SQL injections are a fact of life that we have to protect against.

The current two features available to restrict are: "ErrorMessages", and "WaitFor". Restricting "ErrorMessages" will mask system details in the error messages (e.g. table names, datatypes, etc), and "WaitFor" will simply disable the WAITFOR statement from causing any amount of waiting.

Unfortunately, the new “Feature Restrictions” feature is not going to protect any database against SQL Injection. Not only is “Feature Restrictions” absolutely worthless in that it doesn’t even do what it claims to do, but because it does not truly protect against these types of SQL Injection attacks, it gives a false sense of security to those who only read the official documentation and implement this feature thinking that they are then secure. That makes this feature actually quite dangerous, and the end result could actually be an increase in SQL Injection attacks.

 

Problems

  1. The feature is meant to protect against SQL Injection, which can only happen in ad hoc query batches (i.e. Dynamic SQL), yet disabled features are disabled even in pre-compiled contexts, such as stored procedures (the initial / non-dynamic context of a stored procedure ; the top @@NESTLEVEL), where this is no chance of SQL Injection. I had originally thought that this was a problem, but then I got the following to work, so perhaps it cannot simply be ad hoc batches:
    SELECT * FROM sys.objects WHERE [is_ms_shipped] = 0; EXEC(N'CREATE PROCEDURE #Temp AS SELECT DB_NAME(); WAITFOR DELAY ''00:00:05'';'); EXEC #Temp;
    

     

  2. While it is likely that this feature is enabled in Production only, sometimes it is hard to reproduce errors based on user descriptions of what happened, and so that error message might be quite useful to QA and/or support.
     
  3. The "error messages" with masked internal information are only system generated error message. The output of RAISERROR and THROW is not masked! (example below)
     
  4. Hiding error messages helps for scenarios where the error message is displayed, but isn’t it more common for the application to simply indicate that an error happened without showing any details? Either using a customer error page or even just a standard “500 Internal Server Error” page? In these scenarios, an attacker can infer from differences in behavior, between error and no error cases. And you cannot prevent all errors entirely (well, you could, I suppose, but that would be quite dangerous and foolish). Hence, the following cannot be prevented by this feature:

    IF (USER = 'dbo') SELECT 1 / 0;
    

    or this:

    IF (USER = 'dbo') SELECT * FROM dbo.[NoSuchTable];
    
  5. WAITFOR is not the only means of causing a delay. It is possible to inject a simple loop that ends after a specified number of seconds, and does not cause any error. For example:
    IF (USER = 'dbo') BEGIN DECLARE @Now DATETIME = DATEADD(SECOND, 5, GETDATE()); WHILE (GETDATE() < @Now) DECLARE @Dummy INT; END;
    

    (example below)

  6. EXEUTE AS 'dbo', OR Login is member of sysadmin Fixed Server Role, OR EXECUTE AS User associated with Login that is "sysadmin" and DB is TRUSTWORTHY ON and DB owned by Login that has been granted (at minimum) AUTHENTICATE SERVER, OR EXECUTE AS User that is not restricted nor in a role that is restricted, will prevent restriction:

    1. The WITH EXEUTE AS 'dbo' clause is often used when creating a stored procedure or function to grant rights to perform operations such as TRUNCATE TABLE. It is also sometimes used to get Dynamic SQL working since Dynamic SQL breaks ownership chaining, and we don’t want to give direct table DML access to the application account. And, just to be clear, this does not require that TRUSTWORTHY be set to ON, or even that the "dbo" User be associated with a Login that is a "sysadmin". (examples below)
    2. Hopefully the application account isn’t logging in as "sa" or anyone in the "sysadmin" fixed server role. But this does happen, and feature restrictions do not apply to any Login that is a member of the "sysadmin" fixed server role. This is important to note because the example given for the WaitFor restriction (in the documentation) is:
      IF (SYSTEM_USER = 'sa') WAITFOR DELAY '00:00:05' ...
      

      There are two significant problems with this example:

      1. "sa" is likely not the only account with “sysadmin” privileges. Anyone looking will be checking the following:
        SELECT IS_SRVROLEMEMBER(N'sysadmin', SYSTEM_USER)
        
      2. Any Login in the "sysadmin" role will not have either of these features restricted. Hence, if this condition is actually true, then the WAITFOR will behave normally, and will indicate that the current security context has "sysadmin" privileges.
    3. Similar to item # 1, sometimes the EXECUTE AS clause is used to grant Instance/Server -level permissions such as VIEW SERVER STATE. In this case, the User specified is associated with Login that is "sysadmin" and the Database is set to TRUSTWORTHY ON and the Database is owned by a Login that has the AUTHENTICATE SERVER permission (directly or implied). No features are restricted in modules using this setup, even if the User specified in the EXECUTE AS clause is in the restricted list, or a member of a Database Role that has been restricted. (no example yet, but will add one soon).
    4. A less frequent, but also less complicated, scenario is using WITH EXECUTE AS 'SomeUser' where [SomeUser] is a User that is neither restricted directly nor a member of a Database Role that is restricted. In this case, since the process is now executing as a different User, the User executing the module no longer matters.
       
      These are all even more reasons to use Module Signing instead of EXECUTE AS!
  7. There is a bug with the WaitFor restriction: security context gets cached the first time a query is executed that checks the current user. But, whatever is cached can be dropped automatically by the system (probably for a variety of reasons), or manually by executing:
    DBCC FREESYSTEMCACHE('ALL', 'default');
    

    I also tested with DBCC FREESYSTEMCACHE('ALL', 'internal'); and that had no effect.

    This is a minor bug because the scenarios that would be using this feature would almost certainly already have a query being executed before the WAITFOR is introduced. Still, a good security feature wouldn’t rely so much on luck. (example below)

  8. There is a bug in the permissions checking on the sys.sql_feature_restrictions system catalog view. According to the documentation:
    Permissions
     
    The executing principal must have the CONTROL permission on the database.

    However, as far as I can tell, there are no restrictions at all for selecting from this system catalog view. (example below)

 

Examples

Setup

General

Please note:

  1. Database is owned by a low-privileged Login.
  2. TRUSTWORTHY remains OFF (the default setting).
IF (SUSER_ID(N'_TestDbOwner') IS NULL)
BEGIN
    CREATE LOGIN [_TestDbOwner] WITH PASSWORD = 'NotVerySecure',
                                     CHECK_POLICY = OFF;
END;

IF (DB_ID(N'FeatureRestrictionTest') IS NULL)
BEGIN
    CREATE DATABASE [FeatureRestrictionTest]
        COLLATE Latin1_General_100_BIN2_UTF8;
    ALTER DATABASE [FeatureRestrictionTest] SET RECOVERY SIMPLE;
    ALTER AUTHORIZATION ON DATABASE::[FeatureRestrictionTest]
                        TO [_TestDbOwner];
END;
GO
USE [FeatureRestrictionTest];


CREATE LOGIN [_TestAppAccount] WITH PASSWORD = 'NotVerySecure',
                                    CHECK_POLICY = OFF;
CREATE USER [_TestAppAccount] FOR LOGIN [_TestAppAccount];


GO
CREATE OR ALTER PROCEDURE dbo.[ShowSecurityContext]
AS
SET NOCOUNT ON;

SELECT SYSTEM_USER AS [Login], [name], [type], [usage]
FROM   sys.login_token;

SELECT USER AS [User], [name], [type], [usage]
FROM   sys.user_token;

GO
GRANT EXECUTE ON dbo.[ShowSecurityContext] TO [public];

Add Restrictions

Please note that the capitalization of "waitfoR" and "useR" is intentional: I am testing in a DB using a binary collation to make sure that everything behaves as expected.

EXEC sp_add_feature_restriction
          N'waitfoR', N'useR', N'_TestAppAccount';
EXEC sp_add_feature_restriction
          N'ErrormessageS', N'useR', N'_TestAppAccount';


SELECT * FROM sys.sql_feature_restrictions;
/*
class    object             feature
User     _TestAppAccount    WaitFor
User     _TestAppAccount    ErrorMessages
*/

 

Test: "ErrorMessages" — RAISERROR and THROW aren’t blocked

EXECUTE AS LOGIN = N'_TestAppAccount';
EXEC dbo.[ShowSecurityContext];


SELECT * FROM sys.objects WHERE [is_ms_shipped] = 0 AND CAST(DB_NAME() AS INT) = 0;
/*
Msg 245, Level 16, State 1, Line XXXXX
Conversion failed when converting the ****** value '******' to data type ******.
*/


SELECT * FROM sys.objects WHERE [is_ms_shipped] = 0; DECLARE @Stuff NVARCHAR(4000) = DB_NAME() + NCHAR(42) + ORIGINAL_LOGIN(); RAISERROR(@Stuff, 16, 1);
/*
(2 rows affected)
Msg 50000, Level 16, State 1, Line XXXXX
FeatureRestrictionTest*ALBRIGHT\Solomon
*/


SELECT * FROM sys.objects WHERE [is_ms_shipped] = 0; DECLARE @XML NVARCHAR(4000) = (SELECT [name], [type], [usage] FROM sys.user_token FOR XML RAW('r')); RAISERROR(@XML, 16, 1);
/*
(2 rows affected)
Msg 50000, Level 16, State 1, Line XXXXX
<r name="_TestAppAccount" type="SQL USER" usage="GRANT OR DENY"/><r name="public" type="ROLE" usage="GRANT OR DENY"/>
*/


DECLARE @Err NVARCHAR(4000) = DB_NAME();
;THROW 50505, @Err, 16;
/*
Msg 50505, Level 16, State 16, Line XXXXX
FeatureRestrictionTest
*/


REVERT;
EXEC dbo.[ShowSecurityContext];

 

Test: "ErrorMessages" — EXECUTE AS ‘dbo’ prevents restriction

Create Stored Procedures

GO
CREATE OR ALTER PROCEDURE dbo.[ErrorMessages]
AS
SET NOCOUNT ON;

EXEC dbo.[ShowSecurityContext];

SELECT [name]
FROM   sys.objects
WHERE  [is_ms_shipped] = 0
AND    CAST(DB_NAME() AS INT) = 0;
GO
CREATE OR ALTER PROCEDURE dbo.[ErrorMessages_ExecAsDBO]
WITH EXECUTE AS N'dbo'
AS
SET NOCOUNT ON;

EXEC dbo.[ShowSecurityContext];

SELECT [name]
FROM   sys.objects
WHERE  [is_ms_shipped] = 0
AND    CAST(DB_NAME() AS INT) = 0;
GO


GRANT EXECUTE ON dbo.[ErrorMessages] TO [_TestAppAccount];
GRANT EXECUTE ON dbo.[ErrorMessages_ExecAsDBO] TO [_TestAppAccount];

Execute Stored Procedures

EXECUTE AS LOGIN = N'_TestAppAccount';
EXEC dbo.[ShowSecurityContext];


EXEC dbo.[ErrorMessages]; -- masked

EXEC dbo.[ErrorMessages_ExecAsDBO]; -- not masked


REVERT;
EXEC dbo.[ShowSecurityContext];

 

Test: "WaitFor" — Simple WHILE loop to force a delay

 

IF (1 = 1)
BEGIN
    DECLARE @Now DATETIME = DATEADD(SECOND, 5, GETDATE());
    WHILE (GETDATE() < @Now)
    BEGIN
        DECLARE @Dummy INT;
    END;
END;

 

Test: "WaitFor" — EXECUTE AS ‘dbo’ prevents restriction

Create Stored Procedures

GO
CREATE OR ALTER PROCEDURE dbo.[WaitFor]
AS
SET NOCOUNT ON;

EXEC dbo.[ShowSecurityContext];
WAITFOR DELAY '00:00:03.000'; -- 3 seconds
GO
CREATE OR ALTER PROCEDURE dbo.[WaitFor_ExecAsDBO]
WITH EXECUTE AS N'dbo'
AS
SET NOCOUNT ON;

EXEC dbo.[ShowSecurityContext];
WAITFOR DELAY '00:00:03.000'; -- 3 seconds
GO


GRANT EXECUTE ON dbo.[WaitFor] TO [_TestAppAccount];
GRANT EXECUTE ON dbo.[WaitFor_ExecAsDBO] TO [_TestAppAccount];

Execute Stored Procedures

EXECUTE AS LOGIN = N'_TestAppAccount';
EXEC dbo.[ShowSecurityContext];


EXEC dbo.[WaitFor]; -- no delay

EXEC dbo.[WaitFor_ExecAsDBO]; -- 3-second delay


REVERT;
EXEC dbo.[ShowSecurityContext];

 

Test: "WaitFor" — Bug allows WAITFOR to cause delay again

EXECUTE AS LOGIN = N'_TestAppAccount';
EXEC dbo.[ShowSecurityContext];


-- Execute the following in another Query tab:
-- USE [master]; DBCC FREESYSTEMCACHE('ALL', 'default') WITH NO_INFOMSGS;

WAITFOR DELAY '00:00:03'; -- 3-second delay (NOT expected)


SELECT TOP (1) [name] FROM sys.objects;

WAITFOR DELAY '00:00:03'; -- no delay (expected)
-- If the above command did cause a delay, re-run
-- the "SELECT TOP (1)" just above it and then
-- re-execute this WAITFOR.


-- Execute the following in another Query tab:
-- USE [master]; DBCC FREESYSTEMCACHE('ALL', 'default') WITH NO_INFOMSGS;

WAITFOR DELAY '00:00:03'; -- 3-second delay again (NOT expected)


REVERT;
EXEC dbo.[ShowSecurityContext];

 

Test: "sys.sql_feature_restrictions" — No permissions required to SELECT from

This test uses a Database-only User. This type of User cannot have any Instance/Server -level permissions because they do not have an SID that matches any SID in sys.server_principals. And, no Database-level permissions (including CONTROL) are granted.

CREATE USER [Mr.NoLogin] WITHOUT LOGIN;

EXECUTE AS USER = 'Mr.NoLogin';
EXEC dbo.[ShowSecurityContext];


SELECT * FROM sys.dm_db_fts_index_physical_stats;
/*
Msg 262, Level 14, State 1, Line XXXXX
VIEW DATABASE STATE permission denied in database 'FeatureRestrictionTest'.
Msg 297, Level 16, State 1, Line XXXXX
The user does not have permission to perform this action.
*/


SELECT * FROM sys.sql_feature_restrictions;
/*
class    object             feature
User     _TestAppAccount    WaitFor
User     _TestAppAccount    ErrorMessages
*/

REVERT;
EXEC dbo.[ShowSecurityContext];

 

Clean-up / Tear-down

USE [master];
DROP DATABASE [FeatureRestrictionTest];
DROP LOGIN [_TestAppAccount];
DROP LOGIN [_TestDbOwner];

 

Fun Facts That Are Not Officially Documented Yet

Here are some behaviors I found in testing that are not yet in the documentation:

  1. sp_drop_feature_restriction is not yet in IntelliSense.
     
  2. “Feature Restrictions” cannot be used in three system databases: [master], [model], and [tempdb]:
    USE [master];
    EXEC sp_add_feature_restriction N'ErrorMessages', N'User', N'MyUser';
    
    
    USE [tempdb];
    EXEC sp_add_feature_restriction N'ErrorMessages', N'User', N'MyUser';
    
    
    USE [model];
    EXEC sp_add_feature_restriction N'ErrorMessages', N'User', N'MyUser';
    
    /*
    Msg 16305, Level 16, State 0, Procedure sp_add_feature_restriction, Line XXXXX [Batch Start Line YYYYY]
    The database does not support feature restrictions.
    */
    

     

  3. “Feature Restrictions” can be used in one system database: [msdb]:

    USE [msdb];
    EXEC sp_add_feature_restriction N'ErrorMessages', N'User', N'MyUser';
    /*
    Msg 16301, Level 16, State 0, Procedure sp_add_feature_restriction, Line XXXXX [Batch Start Line YYYYY]
    User 'MyUser' not found.
    */
    

     

  4. Cannot add "dbo" User to “Feature Restrictions”:

    EXEC sp_add_feature_restriction N'waitfoR', N'useR', N'dbo';
    /*
    Msg 16309, Level 16, State 0, Procedure sp_add_feature_restriction, Line XXXXX [Batch Start Line YYYYY]
    Feature restrictions are not allowed for the dbo user.
    */
    

     

  5. Restrictions do not apply to either of the following scenarios:
    1. module is created using WITH EXECUTE AS 'dbo' clause —
    2. Login is member of sysadmin Fixed Server Role —
       
  6. "ErrorMessages" option does not mask custom error messages sent from:
    1. RAISERROR
    2. THROW

 

Can This Feature Be Fixed?

Sadly, no.

  1. You can’t prevent all error output entirely (i.e. the entire message returned by RAISERROR or THROW) as that would make debugging extremely difficult, if not impossible.
     
  2. You can’t prevent forcing an error, either a simple "divide by zero", or selecting from an object that doesn’t exist. Even if error messages are not shown to the end user (this is usually the case, right?), isn’t there at least some indication of there at least being an error (again, this is usually the case, right?)?
     
  3. You can’t prevent WHILE loops.
     
  4. Hackers are more clever than this feature gives them credit for. If I was able to find all of these holes in this feature, you better believe hackers will find even more.

Clearly this feature was neither planned out well nor thoroughly tested.

 

Conclusion

This feature is intended to improve security when using Dynamic SQL. When using Dynamic SQL, it is common to run into permissions problems resulting from broken Ownership Chaining. While it is best to use Module Signing in order to resolve the permissions errors, many people still use the WITH EXECUTE AS clause with either 'dbo' or OWNER as the User to "Execute As" (and if the Schema is owned by dbo, then that is the same as specifying 'dbo'). If “Feature Restrictions” does not work with the dbo User, and yet it is common for stored procedures using Dynamic SQL to be executing as dbo, then how often will “Feature Restrictions” actually be restricting anything?

If anything is to be restricted, why not expand the existing permissions hierarchy by making more commands their own named permissions, commands such as WAITFOR, RAISERROR, TRUNCATE TABLE, PRINT, etc. Doing this fits naturally into the GRANT / DENY / REVOKE system that people are familiar with and would allow for DENYing something such as WAITFOR, which can be a Database-level permission (along with PRINT and RAISERROR, while TRUNCATE TABLE can be Schema-level and/or object-level). Not having a permission doesn’t have to raise an error, does it?

If the desire is to make Dynamic SQL more secure without requiring code changes (or at least a lot of them), then a far better approach would be to attempt preventing end-user manipulation of the query, rather than what the query can do once it has been manipulated. There are only a few methods that someone can use to inject their own SQL, but if they can do even one of them, then there is a lot that they can do. Rather than focus on cleaning up a mess, focus on preventing the mess (i.e. “an ounce of prevention is worth a pound of cure”). If the problems all begin with User input, provide one or more mechanisms to filter one or more input parameters. For example, implementing the following suggestion would not only work for this scenario, but it also handles other scenarios, thereby increasing the ROI for the time / energy put into developing it: “Intercept query batch from client to rewrite it or cancel it”.

Regardless, from my perspective, “Feature Restrictions” is a wasted opportunity in that I am guessing at least 50 – 100 developer hours were spent on adding this feature, between planning, development, testing, etc. Unfortunately, all of that time was wasted delivering a feature that will never do anything. And, by “wasted” I mean that those hours could have been spent delivering real value to the SQL Server community. In that same amount of time, both of the following updates could have been made which would have benefited many thousands of users:

I don’t want to be harsh as, again, I do love SQL Server and it has many more positive and great features than failings. But, this is still quite frustrating knowing that development time required to implement one or more very needed and definitely beneficial features was available but squandered, leaving us with nothing (or worse: a mess of a feature that will only serve to confuse and/or give the false sense of having security) when we could have easily had something.

This feature needs to be removed before people start using it and get a false sense of security, thinking that they are being protected against certain forms of SQL Injection.

Also See

UPDATE 2019-08-06

I filed a bug report to have this feature removed:

URGENT: “Feature Restrictions” actually increases chances of SQL Injection and must be removed immediately (before RTM)

UPDATE 2019-08-15

Found two more problems:

  1. EXECUTE AS User associated with Login that is "sysadmin" and DB is TRUSTWORTHY ON and DB owned by Login that has a certain level of permissions, prevents restrictions.
  2. No permissions required to SELECT from sys.sql_feature_restrictions system catalog view.

Both of these issues have been integrated into the post.

 

UPDATE 2019-10-28

On 2019-08-16, Microsoft responded to my bug report, saying:

Thank you for the valuable feedback.
 
We are looking closely into your comments and into various ways to improve this feature going forward. We are also going to make changes to the current documentation to address some of the concerns you raised.
Please continue to follow the evolution of Feature Restriction.

But then, on 2019-08-28, all non-Azure documentation and references to that documentation were completely removed from the "MicrosoftDocs/sql-docs" repository. You can view the GitHub commit that removed these items, but there’s no comment providing any details related to reasoning or implications. The “Feature Restrictions” functionality is still in Release Candidate 1 (RC1), but according to SELECT @@VERSION;, RC1 was compiled on 2019-08-16, so the implication might be that this will be removed as of RC2 (we shall see).

 

UPDATE 2019-11-10

SQL Server 2019 was officially released on 2019-11-04 (i.e. RTM). As far as I can tell, the “Feature Restrictions” feature has been disabled, though not fully removed. You can still select from the system catalog view to get an empty result set (instead of a missing object error):

SELECT * FROM sys.sql_feature_restrictions;

However, the sys.sp_add_feature_restriction system stored procedure is not suggested via Intellisense (though if you do type it in SSMS will indicate that parameter info is not available for extended stored procedures), and attempting to execute it:

EXEC sp_add_feature_restriction N'WaitFor', N'USER', N'_TestAppAccount';

returns the following error:

Msg 16305 Level 16 State 0 Line XXXXX
The database does not support feature restrictions.

You can see for yourself via this live demo on db<>fiddle (yes, I also confirmed using RTM Developer Edition — 2019.150.2000.5 — on my computer).

This is certainly a fair and acceptable solution. Thank you, Microsoft, for restricting “Feature Restrictions” until it can be made to do what it claims to do! (or, better yet, my fingers are crossed that such functionality gets implemented as named permissions 😸 )

 

UPDATE 2019-12-10

On 2019-11-25, all Azure documentation and references to that documentation were completely removed from the "MicrosoftDocs/azure-docs" repository. You can see the removal of the documentation here, and the removal of the references to that documentation here.

 

UPDATE 2019-12-16

With regards to “Feature Restrictions” being disabled, it turns out that only the sys.sp_add_feature_restriction system stored procedure has been disabled. The feature itself still works (well, as much as it ever did). Before uninstalling RC1 so that I could install the official RTM release, I added a User and a Role to “Feature Restrictions”, knowing that at the very least the system catalog view, sys.sql_feature_restrictions;, would still be available, and I wanted to test if I would still be able to see the entries I just placed in there, and if so, if they would also be bound by those restrictions.

The answer is: YES, yes they are. System error messages are partially masked, and the WAITFOR command is ignored (most of the time). And, I had not tested this before, but the sys.sp_drop_feature_restriction system stored procedure has not been disabled (which makes sense enough: you can remove entries from the list, but not add new ones).

Also, in a comment below, John Langston mentioned that when running a “Vulnerability Assessment” in SSMS, the following entry was reported:


VA1145 – Feature restrictions should be applied to all principals that are used to access the database from web applications

This rule should only run on Azure SQL Database (“Single DB”; not “Managed Instance”). I ran the report in SSMS 18.4 against stand-alone / on-premises SQL Server 2019 and did not see this item among the 55 checks that it ran (though, I did see the item stating that “User CLR Assemblies” were a high risk and that’s just horribly, irresponsibly incorrect, so please ignore that particular check!)

 
 

13 thoughts on “Feature Restrictions in SQL Server 2019 are Worse Than Useless: a False Sense of Security And Wasted Opportunity”

  1. I liked the article but I wouldn’t say that in “stored procedures, where this is no chance of SQL Injection” because what protects from SQL injection isn’t the stored procedure but the parameter and query validation (as can happen in sp_executesql with parameters). The problem is always the concatenation of dynamic sql (in any context) with unchecked external input.

    1. Hello Andre and thanks for the feedback. I do not disagree with what you are saying, but to be fair, what I said did not begin with “stored procedures”. My statement began with, “even in compiled contexts, such as stored procedures, …”, so I was referring only to the outer-most context where there’s no Dynamic SQL / concatenation. A call to EXEC or sp_executesql would be a non-pre-compiled context, same as the app layer executing an ad hoc batch or a prepared statement. I was just saying that at first I had thought that the initial / non-dynamic context of a stored procedure (i.e. the top @@NESTLEVEL) was entirely safe, but then realized that a stored procedure can be created within Dynamic SQL, and there is no way to discern that, so that context isn’t guaranteed safe. If that initial / non-dynamic context was guaranteed safe, then it would be silly to restrict any of the features in that context, hence it would have been yet another problem for this feature. But, it seems like the restrictions need to apply there as well (if they exist in the first place, and ideally they won’t by the time 2019 goes RTM).

      Take care, Solomon…

  2. Hello Solomon and thanks for your answer.
    My gripe is that the way I read it, it seems that compiled contexts (stored procedures, prepared statements, etc) protect against SQL injection when the only defenses are parameter validation and/or not constructing dynamic SQL queries.
    It only takes a bad stored procedure (or prepared statement) constructing a dynamic query inside and not validating the parameters to open up for SQL injection on compiled contexts that call this SP.

    1. Hello again, André. Yes, I do understand that my original wording was ambiguous because because a) eventually everything gets compiled, and b) there was an assumption that I was only referring to the non-Dynamic context (because Dynamic SQL is ad hoc). I thought that I had updated that part last week to clarify based on this discussion, but it seems that I had not. However, I did just update that paragraph to clarify / narrow-down the context to being just the pre-compiled / highest @@NESTLEVEL context of a stored procedure (i.e. the context that does not allow for concatenation, hence the use of Dynamic SQL).

      Thanks again for pointing this out and helping me clarify this for other readers.

  3. Late to this party but I noticed in one of my Sunday morning (12/15/19) Vulnerability assessment scans a finding of “VA1145 – Feature restrictions should be applied to all principals that are used to access the database from web applications” . The automatically supplied remediation script for every user and role looks like:sp_add_feature_restriction N’ErrorMessages’, N’Role’, N’db_owner’. If sp_add_feature_restriction has already been removed/disabled, I am surprised to see it appear in my scan results on this particular database.

    1. Hi John. Thanks for mentioning this. I just ran it in SSMS 18.4 and did not see VA1145 in either the “Failed” or “Passed” lists. What version of SSMS are you using? Still, this did remind me to test something that I need to add as an addendum to the end of my post: I restored a DB that I created in RC1 and had added a role and a user to the “Feature Restrictions” list, and they are still there in the RTM version, and the behavior is still the same! The only thing disabled, it seems, it adding to the list.

      Take care, Solomon…

  4. Thanks for the analysis. I wanted to add my specific scenario to your investigation.

    I also get the vulnerability “VA1145 – Feature restrictions should be applied to all principals that are used to access the database from web applications”, but in my case I’m running the vulnerability tool in Azure SQL Server (using Azure Portal, not SSMS). I read your comments, and yet decided to apply the restriction as suggested anyway and monitor the changes it creates and assess it later.

    However, and perhaps adding to your list of gripes, something new comes up. In my scenario I had added an AAD user and group to the database as contained database users (CREATE USER .. FROM EXTERNAL PROVIDER). These principals get returned in the list of suggested restrictions, but the ‘sp_add_feature_restriction’ sproc doesn’t seem to support external users or at least I can’t figure out what to pass in the object type parameter. I tried a bunch of options. The suggested command is:
    exec sp_add_feature_restriction N’ErrorMessages’, N’Unknown’, N’Admin DEV’
    which returns “‘Unknown’ is not an object class than can be restricted.” (in this case “Admin DEV” is an AAD group)

    The script above was recommended by the same Azure Portal tool with this query:

    SELECT p.name AS Principal, 
    CASE 
        WHEN p.type = 'S' THEN 'User'
        WHEN p.type = 'R' THEN 'Role'
        ELSE 'Unknown'
    END AS Type
    FROM sys.database_principals p
        LEFT JOIN sys.sql_feature_restrictions r
        ON p.name = r.object
        AND p.type IN ('S', 'R')
    WHERE r.object IS NULL
        AND p.name NOT IN ('dbo', 'sys', 'INFORMATION_SCHEMA')
    

    Go figure :-) I hope it helps.

    1. Hello Esteban. Thanks for mentioning this. That is odd, both in terms of being able to execute sp_add_feature_restriction, and having SSMS suggest “Uknown” for the second parameter. I have updated the links in my post to point to the deleted documentation. The syntax shows that the second parameter can only be either User or Role. In your case, it should be:

      EXEC sp_add_feature_restriction N'ErrorMessages', N'User', N'Admin DEV';
      

      Please try that and let me know if it works. Take care, Solomon…

  5. Hi Solomon, I actually tried with ‘User’ in the second parameter. No errors, but same result. Running the Scan tool again reported the same “issue” and suggested executing the sproc with the “Unknown” reference. Just to be clear, I run the scanning tool in Azure Portal for my Azure SQL Database. Then I use SSMS only to execute the SQL commands.

    It looks like the main difference with the scenarios you described is that I’m using these database contained users associated to an AAD user / group. It looks like you cannot add the feature restriction to this kind of users. For further reference, I’m following this guide for the AAD contained db user:

    https://docs.microsoft.com/en-us/azure/sql-database/sql-database-aad-authentication-configure

    Cheers!

    1. Hello again. I figured out where the “Unknown” is coming from. It seems that “Feature Restrictions” is still a valid rule to check for Azure Single DB only. And guess what, the “check” query is:

      SELECT p.name AS Principal, 
      CASE 
          WHEN p.type = 'S' THEN 'User'
          WHEN p.type = 'R' THEN 'Role'
          ELSE 'Unknown'
      END AS Type
      FROM sys.database_principals p
          LEFT JOIN sys.sql_feature_restrictions r
          ON p.name = r.object
          AND p.type IN ('S', 'R')
      WHERE r.object IS NULL
          AND p.name NOT IN ('dbo', 'sys', 'INFORMATION_SCHEMA')
      

      I find the ELSE condition to be a strange choice given that it creates an invalid remediation step as the only two valid options for that parameter are “User” and “Role”. But, from what I saw in the rest of the definitions, at least with respect to anything dealing with SQLCLR, this is certainly not the only SQL Server feature to be misunderstood by the SQL Vulnerability Assessment tool.

      Thanks again for mentioning this behavior. Take care, Solomon…

Leave a Reply