Solomon Rutzky

My feedback

  1. 2 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
    Solomon Rutzky shared this idea  · 
  2. 1 vote
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  SQL Server » Bugs  ·  Flag idea as inappropriate…  ·  Admin →
    Solomon Rutzky shared this idea  · 
  3. 2 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  SQL Server » Bugs  ·  Flag idea as inappropriate…  ·  Admin →
    Solomon Rutzky supported this idea  · 
  4. 1 vote
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  SQL Server » Setup + Deployment  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 23

    <=-=Aug 6 2008 10:00AM=-=>

    Hello,

    Thank you for sharing your idea with us. As I understand from your description the problem is database collation rather than SQL Server instance collation. E.g. would having new databases collate to system local by default solve your problem as well?

    Thanks,
    Krzysztof Kozielczyk

    <=-=Aug 6 2008 12:04PM=-=>

    I’m afraid that I don’t really understand your comment. While it’s great to have the ability to have multiple collations in the same server instance, for very many users this of little interest. They are best served by having the same collation in the entire server, or else they will have to fight battles with collation conflict.

    If I understand you correctly, you suggest that rather than inhering the server collation as the default, the default for the database collation would be derived from the system locale. I am afraid that this would only aggrevate…

    Solomon Rutzky supported this idea  · 
  5. 2 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  0 comments  ·  SQL Server » Bugs  ·  Flag idea as inappropriate…  ·  Admin →
    Solomon Rutzky supported this idea  · 
  6. 7 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    9 comments  ·  SQL Server » Setup + Deployment  ·  Flag idea as inappropriate…  ·  Admin →
    Solomon Rutzky supported this idea  · 
  7. 2 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
    Solomon Rutzky supported this idea  · 
  8. 4 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    3 comments  ·  SQL Server » Bugs  ·  Flag idea as inappropriate…  ·  Admin →
    Solomon Rutzky commented  · 

    As far as I can tell, "Feature Restrictions" has been disabled in the official, RTM release of SQL Server 2019.

    Thank you, Microsoft, for preventing customers from using this feature until it can be improved, or maybe even replaced with new, named permissions that can be managed via the existing GRANT / REVOKE / DENY framework 😺 .

    I have updated my post with this info, including a link to a live demo showing that it reports being disabled if you attempt to add a new restriction:

    https://sqlquantumleap.com/2019/08/05/feature-restrictions-in-sql-server-2019-are-worse-than-useless-a-false-sense-of-security-and-wasted-opportunity/#update_20191028

    Take care,
    Solomon...
    https://SQLsharp.com/

    Solomon Rutzky commented  · 

    I updated my post (linked in the main content above) to add the following to the "Conclusion", to provide more explanation of why I feel that spending any more time trying to fix / expand this feature is futile, and provide some suggestions that I feel would do a better job of accomplishing this goal (which I do agree with):

    _________________________________________________________________________
    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 ( https://ModuleSigning.Info/ ) 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 `DENY`ing 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).

    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" ( https://feedback.azure.com/forums/908035-sql-server/suggestions/32896183-intercept-query-batch-from-client-to-rewrite-it-or ).
    _________________________________________________________________________

    Solomon Rutzky commented  · 

    I found two more problems:

    1. If the `EXECUTE AS` clause specifies a User that is associated with Login that is “sysadmin”, _and_ the DB is set to `TRUSTWORTHY ON`, _and_ the DB is owned by a Login that has a certain level of permissions, that will prevent the restrictions from working as expected.

    2. No permissions are required to `SELECT` from `sys.sql_feature_restrictions` system catalog view, even though it should require at least having the `CONTROL` DB-level permission.

    I have updated my blog post (linked at the bottom of the main ticket body above) to include these new issues.

    Solomon Rutzky shared this idea  · 
  9. 5 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    6 comments  ·  SQL Server » Bugs  ·  Flag idea as inappropriate…  ·  Admin →
    Solomon Rutzky commented  · 

    Just FYI: this is still broken in SSMS 18.4 (15.0.18206.0).

    Solomon Rutzky commented  · 

    Just FYI: this is still broken in SSMS 18.3.1 (15.0.18183.0).

    Solomon Rutzky commented  · 

    Just FYI: this is still broken in SSMS 18.2 (15.0.18142.0).

    Solomon Rutzky commented  · 

    I realize that the release notes for the RTM / GA release of SSMS 18.0 states that this bug was fixed, but technically speaking, it is not 100% fixed. It takes a few additional steps now, but I am still able to inject my own T-SQL, thanks to another bug that I found while testing this :-)

    Solomon Rutzky commented  · 

    This feature, er, bug ;-) still exists in SSMS 18.0 Preview 7.

    Solomon Rutzky commented  · 

    Please see related suggestion:

    SSMS: Allow forcing case-insensitive matching in Object Explorer filters ( https://feedback.azure.com/forums/908035-sql-server/suggestions/36679522-ssms-allow-forcing-case-insensitive-matching-in-o )

    Solomon Rutzky shared this idea  · 
  10. 1 vote
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    1 comment  ·  SQL Server » Bugs  ·  Flag idea as inappropriate…  ·  Admin →
    Solomon Rutzky commented  · 

    Just FYI: this is still broken in SSMS 18.4 (15.0.18206.0).

    Solomon Rutzky shared this idea  · 
  11. 2 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  SQL Server » Setup + Deployment  ·  Flag idea as inappropriate…  ·  Admin →
    Solomon Rutzky shared this idea  · 
  12. 3 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    1 comment  ·  SQL Server » Bugs  ·  Flag idea as inappropriate…  ·  Admin →
    Solomon Rutzky commented  · 

    I was able to reproduce this using the following T-SQL, which also shows that DBCC INPUTBUFFER does work correctly in the same context:

    ---------------------------------------------------
    USE [tempdb];
    CREATE LOGIN [InpBufTest] WITH PASSWORD = 'Show Me The Buffer!';
    CREATE USER [InpBufTest] FOR LOGIN [InpBufTest];

    EXECUTE AS LOGIN = N'InpBufTest';
    SELECT USER, SYSTEM_USER;

    DBCC INPUTBUFFER(@@SPID); -- works
    SELECT * FROM sys.dm_exec_input_buffer(@@SPID, CURRENT_REQUEST_ID()); -- error:
    /*
    Msg 300, Level 14, State 1, Line XXXXX
    VIEW SERVER STATE permission was denied on object 'server', database 'master'.
    Msg 297, Level 16, State 1, Line XXXXX
    The user does not have permission to perform this action.
    */
    REVERT;

    DROP USER [InpBufTest];
    DROP LOGIN [InpBufTest];
    ---------------------------------------------------

    Please note that there is a related documentation issue for this bug:

    Errors occur when the user does not have VIEW SERVER STATE permission. #2846 ( https://github.com/MicrosoftDocs/sql-docs/issues/2846 ).

    Take care,
    Solomon....
    https://SqlQuantumLeap.com/

    Solomon Rutzky supported this idea  · 
  13. 2 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    2 comments  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
    Solomon Rutzky commented  · 

    A new use for this feature has come up: intercepting queries from application users / Dynamic SQL to filter on patterns that indicate SQL Injection.

    Perhaps this feature could:

    1) be configurable per each Login and/or User.
    2) filter on total number of statements in the batch (additional statements would indicate end-user manipulation)
    3) allow for filtering on patterns via Regular Expressions (RegEx)
    4) might need some ability to add conditional logic if the goal is to work with absolutely NO code changes (is it possible to pass in a "filter_profile" or some new connection string keyword?)

    I think this would be a much better approach to preventing SQL Injection as opposed to the new "Feature Restrictions" feature that was introduced in SQL Server 2019 CTP 3.2:

    "URGENT: "Feature Restrictions" actually increases chances of SQL Injection and must be removed immediately (before RTM)" ( https://feedback.azure.com/forums/908035-sql-server/suggestions/38319796-urgent-feature-restrictions-actually-increases )

    Solomon Rutzky supported this idea  · 
    Solomon Rutzky commented  · 
  14. 1 vote
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    0 comments  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
    Solomon Rutzky shared this idea  · 
  15. 0 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    1 comment  ·  SQL Server » Setup + Deployment  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 1

    <=-=Jan 18 2010 4:09PM=-=>

    Hi,
    Thanks for reporting the issue. These type of errors are however specific to your environment / database. It will be good if you can provide us with a sample database & script that demonstrates the issue. We will also look at the code based on the error message to see what the pontential problem might be. So if you can provide us with a repro that will be great.


    Umachandar, SQL Programmability Team

    <=-=Nov 12 2010 2:08PM=-=>

    Closed the feedback item.


    Umachandar

    Solomon Rutzky commented  · 

    I assume that this was reported for SQL Server 2005 or 2008, which I have not tested. But on SQL Server 2017, the following works just fine:

    ------------------------------------------------
    DECLARE @IssueHistory TABLE ([EventText] NVARCHAR(MAX));
    INSERT INTO @IssueHistory ([EventText]) VALUES (N'dfdfgdfg' + NCHAR(0x0D) + NCHAR(0x0A) + N'sdfs');

    declare @LFCR char(2)
    declare @CRLF char(2)
    declare @CR char(1)
    declare @LF char(1)
    set @LFCR=char(10)+char(13)
    set @CRLF=char(13)+char(10)
    set @CR=char(13)
    set @LF=char(10)

    UPDATE hist
    SET hist.EventText = replace(replace(replace(replace(hist.[EventText], @CRLF, '<br>'), @LFCR, '<br>'), @LF, '<br>'), @CR, '<br>')
    FROM @IssueHistory hist

    SELECT * FROM @IssueHistory;
    ------------------------------------------------

  16. 0 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    1 comment  ·  SQL Server » Bugs  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 1

    <=-=Jun 24 2014 4:29PM=-=>

    Hello,
    After carefully evaluating all of the bugs in our pipeline, we are closing bugs that we will not fix in the current or future versions of SQL Server. �The reasons for closing these bugs are following:
    1.������ The fix is risky to implement in the current version of the product (service packs)
    2.������ Scenarios reported in the bug are not common enough
    3.������ A viable workaround is available

    Thanks again for reporting the product issue and continued support in improving our product

    Jos de Bruijn – SQL Server PM

    Solomon Rutzky commented  · 

    There is an easy workaround / fix for this: use a binary collation in the REPLACE() function. For example, the following query:

    DECLARE @NullTerminated NVARCHAR(500);
    SET @NullTerminated = N'one' + NCHAR(0) + N'two';

    SELECT @NullTerminated,
    REPLACE(@NullTerminated COLLATE Latin1_General_100_CI_AS, NCHAR(0), N''),
    REPLACE(@NullTerminated COLLATE Latin1_General_100_BIN2, NCHAR(0), N'');

    returns:

    one ... one ... onetwo

  17. 2 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    1 comment  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →

    Upvotes: 1

    <=-=Nov 17 2016 6:36AM=-=>

    Solomon, such function already exist, NEXT VALUE FOR , see https://msdn.microsoft.com/en-CA/library/ff878370.aspx?f=255&MSPPError=-2147217396, and gets value before insert, not after. Knowing it before is a lot more advantageous than after as with Scope_Identity IMHO

    <=-=Nov 18 2016 10:50AM=-=>

    Hi Solomon,

    Thank you for reaching out to Microsoft.

    The current value of a sequence is exposed by the sys.sequences catalog view (https://technet.microsoft.com/en-us/library/ff877934(v=sql.110).aspx)
    Would this work in your scenario?

    Thanks,
    Panagiotis Antonopoulos

    <=-=Dec 11 2016 3:00PM=-=>

    Hi @Panagiotis. Thanks for the reply. To be honest, I don’t have a current scenario that I need this for. I was just doing some testing with Sequences and thought that it might be nice to have. It would have made what I was doing at the time easier to validate, so I figured others might have run into this as well, especially since SCOPE_IDENTITY() is used so frequently.

    Solomon Rutzky commented  · 
    Solomon Rutzky supported this idea  · 
  18. 10 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    4 comments  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
    Solomon Rutzky commented  · 
    Solomon Rutzky supported this idea  · 
    Solomon Rutzky commented  · 

    One needs to also be careful not to remove square brackets from non-regular identifiers. That requires verifying both that a) all of the characters used are valid for use in regular identifiers, and b) the identifier being un-delimited is not a reserved word. I came up with an SSMS RegEx expression that is nearly 100% accurate (minor issue is that the Unicode classifications might have changed slightly between version 3.2--which is what SQL Server used to determine the list of valid characters years ago--and the version currently used by .NET). Please see DBA.StackExchange for the RegEx expression for use with Find/Replace in SSMS:

    Does this regex reliably remove square brackets from T-SQL? ( https://dba.stackexchange.com/a/198391/30859 )

  19. 4 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    under review  ·  1 comment  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →
    Solomon Rutzky supported this idea  · 
    Solomon Rutzky commented  · 

    1) there is no such thing as "reserved characters" that require being delimited. But, there is a list of characters that do _not_ require being delimited (names using only these characters are known as "regular" identifiers, while names using characters not in the list are delimited identifiers since they require being delimited). The only known list of such characters (that I am aware of) is the list that I published here: Completely Complete List of Valid T-SQL Identifier Characters ( https://sqlquantumleap.com/reference/completely-complete-list-of-valid-t-sql-identifier-characters )

    2) Please see related item: "Remove Brackets from SSMS script generation" ( https://feedback.azure.com/forums/908035-sql-server/suggestions/32907346-remove-brackets-from-ssms-script-generation )

  20. 3 votes
    Sign in
    (thinking…)
    Sign in with: Microsoft
    Signed in as (Sign out)

    We’ll send you updates on this idea

    2 comments  ·  SQL Server » Bugs  ·  Flag idea as inappropriate…  ·  Admin →
    Solomon Rutzky commented  · 

    So, I made the mistake of clicking on the "edit" button, and this pathetic, sad excuse of a website decided it would rather delete my comment. Nice. I guess it serves me right for thinking that progress was being made on a site that is clearly only being used by Microsoft (one of the largest, if not _the_ largest, software company on the planet) because someone at UserVoice knows someone at Microsoft.
    ________________________________________________________________________

    My comment was something along the lines of: I don't think this is a bug since it works for me on SQL Server 2017 CU15 using the following test script:
    ------------------------------------
    DECLARE @PythonScript NVARCHAR(MAX);

    SET @PythonScript = N'
    fileToSearch = "\\\\serverName\\shareName\\pathName\\fileName.txt"
    if os.path.exists(fileToSearch):
    print("File exists")
    else:
    print("File does not exist.")
    ';

    EXECUTE sp_execute_external_script
    @language = N'Python',
    @script = @PythonScript;
    ------------------------------------

    It could be a permissions issue (check the "\\server\share" permissions not the "F:\folder" permissions) as the "LaunchPad" service, by default, runs as the "NT SERVICE\MSSQLLaunchPad" account. And that account, even if it can access a local share, might not be able to access a networked share: you might need to add permissions for "computerName$" (or something like that).

    P.S. I only voted for this item because this horrible UserVoice system doesn't provide any other mechanism for notifications.

    Solomon Rutzky supported this idea  · 
← Previous 1 3 4 5 6

Feedback and Knowledge Base