(last updated: 2020-05-20 @ 03:10 ET / 2020-05-20 @ 07:10 UTC )
First, let me say how much I love some of the new functionality in SQL Server 2019:
- Using In-Memory tables for
- Adaptive inlining of T-SQL scalar functions
- Optimizing indexes for sequential inserts (i.e. IDENTITY)
- Better table variable statistics
- Batch mode for rowstore tables
- MSDTC for SQL Server on Linux
- Memory-optimized TempDB metadata
- Reduced recompilations when using temp tables across multiple scopes
- 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, 2019-12-16, and 2020-05-20 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.
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 topI 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:
@@NESTLEVEL), where this is no chance of SQL Injection.
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;
- 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.
- The "error messages" with masked internal information are only system generated error message. The output of
THROWis not masked! (example below)
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;
IF (USER = 'dbo') SELECT * FROM dbo.[NoSuchTable];
WAITFORis 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;
EXEUTE AS 'dbo', OR Login is member of
sysadminFixed Server Role, OR
EXECUTE ASUser associated with Login that is "sysadmin" and DB is
TRUSTWORTHY ONand DB owned by Login that has been granted (at minimum)
AUTHENTICATE SERVER, OR
EXECUTE ASUser that is not restricted nor in a role that is restricted, will prevent restriction:
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
TRUSTWORTHYbe set to
ON, or even that the "dbo" User be associated with a Login that is a "sysadmin". (examples below)
- 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
WaitForrestriction (in the documentation) is:
IF (SYSTEM_USER = 'sa') WAITFOR DELAY '00:00:05' ...
There are two significant problems with this example:
- "sa" is likely not the only account with “sysadmin” privileges. Anyone looking will be checking the following:
SELECT IS_SRVROLEMEMBER(N'sysadmin', SYSTEM_USER)
- Any Login in the "sysadmin" role will not have either of these features restricted. Hence, if this condition is actually true, then the
WAITFORwill behave normally, and will indicate that the current security context has "sysadmin" privileges.
- "sa" is likely not the only account with “sysadmin” privileges. Anyone looking will be checking the following:
- Similar to item # 1, sometimes the
EXECUTE ASclause 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 ONand the Database is owned by a Login that has the
AUTHENTICATE SERVERpermission (directly or implied). No features are restricted in modules using this setup, even if the User specified in the
EXECUTE ASclause is in the restricted list, or a member of a Database Role that has been restricted. (no example yet, but will add one soon).
- 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
- There is a bug with the
WaitForrestriction: 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
WAITFORis introduced. Still, a good security feature wouldn’t rely so much on luck. (example below)
There is a bug in the permissions checking on the
sys.sql_feature_restrictionssystem catalog view. According to the documentation:
The executing principal must have the
CONTROLpermission 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)
- Database is owned by a low-privileged Login.
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];
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" —
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
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:
sp_drop_feature_restrictionis not yet in IntelliSense.
- “Feature Restrictions” cannot be used in three system databases:
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. */
“Feature Restrictions” can be used in one system database:
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. */
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. */
- Restrictions do not apply to either of the following scenarios:
- module is created using
WITH EXECUTE AS 'dbo'clause —
- Login is member of
sysadminFixed Server Role —
- module is created using
- "ErrorMessages" option does not mask custom error messages sent from:
Can This Feature Be Fixed?
- You can’t prevent all error output entirely (i.e. the entire message returned by
THROW) as that would make debugging extremely difficult, if not impossible.
- 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?)?
- You can’t prevent
- 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.
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
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
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
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:
- Allow Asymmetric Key to be created from binary hex bytes string just like CREATE CERTIFICATE — this is merely a request to get the public key bytes from a
VARBINARYliteral string (or variable) instead of from a file or Assembly. There is nothing different to interpret as the bytes read from the
0x...string or variable is identical to whatever is in the file or Assembly (except that executable files and assemblies have extra bytes of program code to skip over, but the internal logic is all the same). No private key handling is necessary; we just need the Asymmetric Key created from the public key only.
- Allow SQLCLR to Connect to LocalDB instance using “(LocalDB)\InstanceName” connection string in EXTERNAL_ACCESS instead of requiring UNSAFE — this is just a few minutes of work to add a single line of code to what eventually gets compiled as the sqlaccess.dll assembly:
That’s it! Just copy and paste that in and change "appDomain" to whatever it should be.
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.
- sys.sql_feature_restrictions (NOTE: Please see the 2019-10-28 and 2019-11-10 Updates regarding the removal of documentation for this feature.)
sp_add_feature_restriction(no documentation page)
sp_drop_feature_restriction(no documentation page)
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)
Found two more problems:
EXECUTE ASUser associated with Login that is "sysadmin" and DB is
TRUSTWORTHY ONand DB owned by Login that has a certain level of permissions, prevents restrictions.
- No permissions required to
sys.sql_feature_restrictionssystem catalog view.
Both of these issues have been integrated into the post.
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).
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;
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 😸 )
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.
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!)
It seems that “Feature Restrictions” are finally completely removed, based on the following observations:
- On March 4th, 2020, Anthony Iacono posted a comment stating that the
sys.sp_add_feature_restrictionsystem stored procedure had been removed from Azure SQL Database.
- On April 7th, 2020, Microsoft released SSMS version 18.5 (15.0.18330.0), and the VA1145 rule is no longer present in the “Vulnerability Assessment” tool.
15 thoughts on “Feature Restrictions in SQL Server 2019 are Worse Than Useless: a False Sense of Security And Wasted Opportunity (Msg 16305, Msg 16309, VA1145)”
[…] Solomon Rutzky is not a fan of Feature Restrictions in SQL Server 2019 CTP 3.2: […]
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.
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
sp_executesqlwould 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…
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.
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
@@NESTLEVELcontext 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.
[…] anything that helps fix this, helps fix this. However, if you go and read this blog post by Solomon Rutzky, you’re going to find two things. First, someone who dislikes SQL Injection possibly more […]
[…] Feature Restrictions in SQL Server 2019 are Worse Than Useless: a False Sense of Security And Wasted… […]
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.
Hi John. Thanks for mentioning this. I just ran it in SSMS 18.4 and did not see
VA1145in 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…
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:
Go figure :-) I hope it helps.
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
Role. In your case, it should be:
Please try that and let me know if it works. Take care, Solomon…
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:
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:
I find the
ELSEcondition 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…
So, funny story. The stored procedure SP_add_feature_restriction no longer exists in Azure SQL Server (with absolutely no heads up from Microsoft, by the way).
Hi Anthony. Thank you very much for sharing that info! Just to confirm a few things before I update the post with this info: a) this is Azure SQL DB Single DB, not Managed Instance, correct? b) Does the
sys.sql_feature_restrictionsview still exist? and c) What does executing
EXEC sys.sp_drop_feature_restriction;do? say “not supported” or “not found”?