Solomon Rutzky

My feedback

  1. 2 votes
    Sign in
    Check!
    (thinking…)
    Reset
    or sign in with
      Password icon
      Signed in as (Sign out)

      We’ll send you updates on this idea

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

      I did some research and found that this is actually a little worse than I had originally thought. It seems that the version 100 collations added 438 uppercase and lowercase mappings that were missing in the version 80 and 90 collations. BUT, then the version 140 collations added another 211 mappings that were missing from the version 100 collations, for a total of 649 mappings that are missing from the version 80 and 90 collations.

      Hence, we really do need a 140_UTF8_BIN2 collation (or "Invariant_140_UTF8_BIN2", or whatever).

      To see how I came up with that list of missing mappings, please see the "Different Versions" sections of the following post:

      Differences Between the Various Binary Collations (Cultures, Versions, and BIN vs BIN2) ( https://sqlquantumleap.wordpress.com/2019/03/13/differences-between-the-various-binary-collations-cultures-versions-and-bin-vs-bin2/ )

      Solomon Rutzky commented  · 

      Ooops, almost forgot to include the UTF8_BIN2 behavior when using the actual UTF-8 encoding. The test query should be:

      -------------------------------------------------------------------------------------
      SELECT LOWER(NCHAR(504) COLLATE Latin1_General_BIN2) AS [Version80],
      LOWER(NCHAR(504) COLLATE Chinese_Taiwan_Stroke_90_BIN2) AS [Version90],
      LOWER(NCHAR(504) COLLATE Latin1_General_100_BIN2) AS [Version100],
      LOWER(NCHAR(504) COLLATE Japanese_XJIS_140_BIN2) AS [Version140],
      LOWER(NCHAR(504) COLLATE UTF8_BIN2) AS [UTF8 is Version 80 😿],
      CONVERT(VARCHAR(2), LOWER(NCHAR(504) COLLATE UTF8_BIN2)) AS [UTF8 is Version 80 😿];
      /*
      Version80 Version90 Version100 Version140 UTF8 is Version 80 😿 UTF8 is Version 80 😿
      Ǹ Ǹ ǹ ǹ Ǹ Ǹ
      */
      -------------------------------------------------------------------------------------

      So, if there is to be only one UTF8_BIN2 collation, then it needs to be at least version 100. It can be any of the following:

      Invariant_100_UTF8_BIN2
      General_100_UTF8_BIN2
      Latin1_General_100_UTF8_BIN2 (since it is currently using LCID 1033)

      Using Invariant or General works better for future deprecation of the redundant _BIN and _BIN2 variations of the Unicode-only collations since those also only truly need a single binary collation (the Unicode-only collations already have both _BIN and _BIN2, so I assume we will also need the _BIN variations):

      Invariant_90_BIN
      Invariant_90_BIN2
      Invariant_100_BIN
      Invariant_100_BIN2

      Please note: there are no Unicode-only collations at version 80 or 140, only 90 and 100.

      Solomon Rutzky commented  · 

      Actually, it seems that point #2 in my review of the new UTF8_BIN2 collation (previous comment) has far greater impact than I had originally thought. By going with a non-versioned / version 80 (or version 0 if going by the values returned by COLLATIONPROPERTY) collation, while on the surface there is no difference because the sorting and comparison doesn't change, there are still properties of the code points that do change, such as lowercase / uppercase mappings. And the collations of versions prior to 100 (or 2 going by COLLATIONPROPERTY) are missing A LOT of mappings, version 80 / 0 missing the most. For example:

      --------------------------------------------------------------
      SELECT LOWER(NCHAR(504) COLLATE Latin1_General_BIN2) AS [Version80],
      LOWER(NCHAR(504) COLLATE Chinese_Taiwan_Stroke_90_BIN2) AS [Version90],
      LOWER(NCHAR(504) COLLATE Latin1_General_100_BIN2) AS [Version100],
      LOWER(NCHAR(504) COLLATE Japanese_XJIS_140_BIN2) AS [Version140],
      LOWER(NCHAR(504) COLLATE UTF8_BIN2) AS [UTF8 is Version 80 😿];
      /*
      Version80 Version90 Version100 Version140 UTF8 is Version 80 😿
      Ǹ Ǹ ǹ ǹ Ǹ
      */
      --------------------------------------------------------------

      This is something that MUST be addressed before this goes RTM! Thanks, and thanks again for including a single, BIN2 collation 😺.

      Solomon Rutzky commented  · 

      I see that a "UTF8_BIN2" collation was added in CTP 2.3. Thanks for doing that.

      However, there are a few concerns with it:

      1) The name, being just UTF8_BIN2, is very much outside of the standard format that all other collation names fit into. I'm not entirely sure if this is truly a problem or not, but it just seems like something that lends itself well to the "law of unintended consequences", and that there might be some down-stream negative impacts, especially for any code that parses the collation name.

      2) Related to #1, version # is not part of the name. But, looking at the version number, it is reported as being version 0, which should be the collations that came with SQL Server 2000. Given the complexities of updating the TDS specification, and that there are really no other new collations since the UTF8 collations have thus far been attached to previously existing collations, it makes sense to not create / waste a whole new version number for just one single collation. But I think there are better choices than version 0, as no other UTF8 collation is at version 0. I think it would be fine to use version 3 (i.e. _140 in the name) since collations of that version were the first to inherently support supplementary characters and no longer need a set of _SC collations. I realize that a binary collation does not deal with supplementary characters, but so far the rule has been stated as UTF8 collations apply to collations that support supplementary characters, which means those ending in _SC or those with _140 in their names. Using _140 (i.e. version 3) would be consistent with that, and would still make sense as that is the most recent version.

      3) Related to #1, culture name / collation designator is not part of the name. I think "Invariant" would be fine here. It's terminology used in .NET so it will already make sense to some folks. Or, it could again be "General". It can even be "Latin1_General" seeing as the LCID is 1033. It just needs to be something.

      4) Speaking of LCID, I assume that 1033 is being used because it's the common / popular / widely-used one? Is it possible to use 0, or whatever is used for the invariant culture? Or is 1033 really the best choice?

      Solomon Rutzky commented  · 

      I just checked CTP 2.2 and there are still no BIN2 UTF-8 Collations.

      Solomon Rutzky commented  · 

      Hi Pedro. Yes, I will email you. But, to have it stated for other readers, the use cases would be (generally):

      1) whatever the current use cases are for anyone using a binary Collation for anything (getting string functions to work with characters that don't equate to anything, like CHAR(0) or some others, columns of alphanumeric codes that don't need linguistic rules and would benefit from the better performance of a binary Collation, etc)

      2) Now that CTP 2.1 adds the ability to select UTF-8 Collations as the Instance-level Collation, having the ability to continue using a binary Collation while also making use of the new UTF-8 encoding (for whatever questionable benefit it might provide). Point being, folks do use binary Collations at the Instance-level. If any of those folks want to continue doing so but also want to use UTF-8 for VARCHAR data, then they cannot do both at the same time, and for no stated (let alone "good") reason.

      Solomon Rutzky shared this idea  · 
    • 2 votes
      Sign in
      Check!
      (thinking…)
      Reset
      or sign in with
        Password icon
        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  · 
      • 1 vote
        Sign in
        Check!
        (thinking…)
        Reset
        or sign in with
          Password icon
          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  · 
        • 1 vote
          Sign in
          Check!
          (thinking…)
          Reset
          or sign in with
            Password icon
            Signed in as (Sign out)

            We’ll send you updates on this idea

            0 comments  ·  SQL Server » Bugs  ·  Flag idea as inappropriate…  ·  Admin →

            Upvotes: 3

            <=-=May 8 2015 11:31AM=-=>

            Hi Xor88,

            Thank you for your inquiry. Currently, SQL Server does not allow changing collations on scalars that have a different collation explicitly specified for them. In your example, the code that assigns the value to nameBIN2 inside CROSS APPLY, explicitly specifies the collation for nameBIN2 to be BIN2. An attempt to change that collation later in the projection portion of the query leads to SQL Server erroring out with the error 445 (COLLATE clause cannot be used on expressions containing a COLLATE clause).

            Since your original logic involves inserting data into a temporary table #t, it would make sense to use the desired collation once you start querying #t later on. That way, you can specify the projection collation in the SELECT FROM #t.

            Thanks,
            Sergey Ten.

            <=-=May 8 2015 1:46PM=-=>

            Sergey,

            thanks for the response. You are suggesting to materialize…

            Solomon Rutzky supported this idea  · 
          • 121 votes
            Sign in
            Check!
            (thinking…)
            Reset
            or sign in with
              Password icon
              Signed in as (Sign out)

              We’ll send you updates on this idea

              8 comments  ·  SQL Server » Suggestions  ·  Flag idea as inappropriate…  ·  Admin →

              Upvotes: 72

              <=-=Mar 10 2016 11:26AM=-=>

              It’s a shame that this was submitted as just a “suggestion”. It should actually be listed as a “bug” because there’s only a comparatively small set of use cases where enumeration of the result set of elements is not important.

              <=-=Mar 11 2016 12:47PM=-=>

              I agree that an order column is required; one example use case is where two lists are passed in, and ordinal positions in one list correspond to positions in the other.

              <=-=Mar 11 2016 3:12PM=-=>

              Please see the related suggestion: STRING_SPLIT needs “RemoveEmptyEntries” option, like String.Split in .NET ( https://connect.microsoft.com/SQLServer/feedback/details/2462002/ ).

              <=-=Mar 12 2016 12:02PM=-=>

              This kind of function is primarily needed for de-serializing previously serialized arrays of values of any type format-able as text.
              I therefore recommend to have the result set of this function work excellent with this use-case.

              With de-serialized arrays there is a need to…

              Solomon Rutzky supported this idea  · 
            • 1 vote
              Sign in
              Check!
              (thinking…)
              Reset
              or sign in with
                Password icon
                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  · 

                Related bug report for "sp_validname":

                sp_validname incorrectly returns 1 / true if name being checked contains code point U+FFFF which is invalid, even for delimited identifiers ( https://feedback.azure.com/forums/908035-sql-server/suggestions/37049194-sp-validname-incorrectly-returns-1-true-if-name ).

                Solomon Rutzky shared this idea  · 
              • 1 vote
                Sign in
                Check!
                (thinking…)
                Reset
                or sign in with
                  Password icon
                  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  · 
                • 1 vote
                  Sign in
                  Check!
                  (thinking…)
                  Reset
                  or sign in with
                    Password icon
                    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  · 
                  • 2 votes
                    Sign in
                    Check!
                    (thinking…)
                    Reset
                    or sign in with
                      Password icon
                      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  · 

                      I just ran these tests on CTP 2.3 and the problem still exists.

                      Solomon Rutzky commented  · 

                      I just ran these tests on CTP 2.2 and the problem still exists.

                      Solomon Rutzky commented  · 

                      To be clear, the truncation doesn't affect only Extended ASCII characters, it chops off whatever is on the right-side of the string (i.e. the end of the string). It's just that it is the Extended ASCII characters which cause there to be truncation in the first place. For example:

                      -------------------------------------------------------------------------------
                      -- Try this in any DB with a non-UTF8 [SQL_]Latin1_General default Collation:

                      -- Convert to UTF-8:
                      SELECT '§x' COLLATE Latin1_General_100_CI_AS_SC_UTF8;
                      -- § (2-byte character fits in the two allocated bytes, leaving no room for char #2)

                      SELECT 'x§' COLLATE Latin1_General_100_CI_AS_SC_UTF8;
                      -- x (1-byte character fits in the two allocated bytes, leaving only 1 byte left;
                      -- not enough room for 2-byte character so 2nd byte is left empty)
                      -------------------------------------------------------------------------------

                      Solomon Rutzky shared this idea  · 
                    • 2 votes
                      Sign in
                      Check!
                      (thinking…)
                      Reset
                      or sign in with
                        Password icon
                        Signed in as (Sign out)

                        We’ll send you updates on this idea

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

                        I just ran these tests on CTP 2.3 and the problem still exists.

                        Solomon Rutzky commented  · 

                        I just ran these tests on CTP 2.2 and the problem still exists.

                        Solomon Rutzky commented  · 

                        I just ran these tests on CTP 2.1 and the problem still exists.

                        Solomon Rutzky commented  · 

                        Hello @pmasl : Please take a closer look at my description and example. It is clear from the "??" returned that both A) the value was converted into UTF-16, hence the double question mark instead of a single question mark as it is a Supplementary Character, and B) the value was then converted back to Code Page 1252, thanks to Collation Precedence, hence the question marks instead of the original character.

                        However, if you prefer to see it differently, as an actual comparison, then execute the following (the results are included in comments). There is no way that the same UTF-8 character should match both "??" in Code Page 1252, AND "𨕷" in UTF-8.

                        -------------------------
                        USE [UTF8];

                        DECLARE @Test2 TABLE
                        (
                        [CP1252] VARCHAR(50) COLLATE Latin1_General_100_CI_AS,
                        [UTF8] VARCHAR(50) COLLATE Latin1_General_100_CI_AS_SC_UTF8
                        );

                        INSERT INTO @Test2 ([CP1252], [UTF8]) VALUES ('??', '𨕷');
                        SELECT * FROM @Test2;
                        -- ?? 𨕷

                        SELECT * FROM @Test2 WHERE [CP1252] = '𨕷';
                        -- ?? 𨕷

                        SELECT * FROM @Test2 WHERE [UTF8] = '𨕷';
                        -- ?? 𨕷
                        -------------------------

                        Solomon Rutzky shared this idea  · 
                      • 2 votes
                        Sign in
                        Check!
                        (thinking…)
                        Reset
                        or sign in with
                          Password icon
                          Signed in as (Sign out)

                          We’ll send you updates on this idea

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

                          This appears to be fixed in CTP 2.3.

                          P.S. Please see "Native UTF-8 Support in SQL Server 2019: Savior or False Prophet?" ( https://sqlquantumleap.com/2018/09/28/native-utf-8-support-in-sql-server-2019-savior-false-prophet-or-both/ ) for a detailed analysis of the new UTF-8 feature.

                          Solomon Rutzky supported this idea  · 
                        • 1 vote
                          Sign in
                          Check!
                          (thinking…)
                          Reset
                          or sign in with
                            Password icon
                            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  · 

                            This is still a problem in CTP 2.3, though not as much of a problem. The installer still fails, but the installation itself completes, leaving the instance running but in single user mode. If the instance is stopped and started again, you can actually log in, and the instance-level collation is reported as being Maltese_100_CI_AI_SC_UTF8. So at least 95% of the way fixed.

                            The new installation error is:

                            ------------------------------------------------------------------------
                            Action required:
                            Use the following information to resolve the error, uninstall this feature, and then run the setup process again.

                            Feature failure reason:
                            An error occurred during the setup process of the feature.

                            Error details:
                            § Error installing SQL Server Database Engine Services Instance Features
                            Usage: EXECUTE xp_sqlagent_notify <operation type>, <job id>, <schedule id>, <alert id>, <action type> [, <login name>] [, <error flag>]Usage: EXECUTE xp_sqlagent_notify <operation type>, <job id>, <schedule id>, <alert id>, <action type> [, <login name>] [, <error flag>]
                            Error code: 0x80131904
                            Visithttps://go.microsoft.com/fwlink?LinkId=20476&ProdName=Microsoft+SQL+Server&EvtSrc=setup.rll&EvtID=50000&ProdVer=15.0.1300.359&EvtType=0x7590AFD6%400x9A41B222&EvtType=0x7590AFD6%400x9A41B222 to get help on troubleshooting.
                            ------------------------------------------------------------------------

                            Solomon Rutzky commented  · 

                            This is still an issue in CTP 2.2. Well, the installer still lets you select a Unicode-only UTF-8 Collation. And, attempting to change the instance's Collation to "Maltese_100_CI_AS_SC_UTF8" using the "'sqlservr.exe -q" method still reports the same error.

                            Solomon Rutzky shared this idea  · 
                          • 2 votes
                            Sign in
                            Check!
                            (thinking…)
                            Reset
                            or sign in with
                              Password icon
                              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  · 

                              This is still an issue for SSMS v18.0 Preview 7 (SQL Server Management Studio 15.0.18092.0).

                              I have not tested in the newest SQLCMD that should come with SQL Server 2019 CTP 2.3, but I assume it uses the same parsing logic and would thus have the same issue.

                              Solomon Rutzky supported this idea  · 
                            • 1 vote
                              Sign in
                              Check!
                              (thinking…)
                              Reset
                              or sign in with
                                Password icon
                                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  · 

                                To be clearer about the parameters that have default values, the following query filters out parameters that do not have defaults (else it is more difficult to see those that have NULL for the value simply because no default value was ever set):

                                SELECT [default_value], *
                                FROM sys.parameters
                                WHERE [object_id] = OBJECT_ID(N'SQL#.DB_BulkCopy')
                                AND [has_default_value] = 1
                                ORDER BY [parameter_id];

                                ALSO, since the DB_BulkCopy stored procedure currently has non-NULL defaults for the INT parameters, I just changed one to NULL to see if it was only NVARCHAR parameters that are affected by this bug. After changing the @NotifyAfterRows parameter to have a default value of NULL, it was scripted out as follows:

                                @NotifyAfterRows [int] = ,

                                which produces a syntax error, unlike using N'' in place of NULL for the NVARCHAR parameters. So yes, this bug affects more than just NVARCHAR parameters.

                                Solomon Rutzky shared this idea  · 
                              • 1 vote
                                Sign in
                                Check!
                                (thinking…)
                                Reset
                                or sign in with
                                  Password icon
                                  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  · 

                                  This is still an issue for SSMS v18.0 Preview 7 (SQL Server Management Studio 15.0.18092.0).

                                  Solomon Rutzky commented  · 

                                  This bug still exists in SSMS 18.0 Preview 6. Here is a simple test:

                                  ------------------------------------------------------------------
                                  PRINT 'A';
                                  PRINT 'B' + CHAR(0x0D) + CHAR(0x0A); -- NO NEWLINE in SSMS
                                  PRINT 'C';
                                  PRINT 'D' + CHAR(0x0D); -- newline is there
                                  PRINT 'E';
                                  PRINT 'F' + CHAR(0x0A); -- newline is there
                                  PRINT 'G';
                                  PRINT 'H' + CHAR(0x0D) + CHAR(0x0A) + CHAR(0x0D) + CHAR(0x0A); -- 1 newline is there, not 2
                                  PRINT 'I';
                                  ------------------------------------------------------------------

                                  Solomon Rutzky supported this idea  · 
                                • 2 votes
                                  Sign in
                                  Check!
                                  (thinking…)
                                  Reset
                                  or sign in with
                                    Password icon
                                    Signed in as (Sign out)

                                    We’ll send you updates on this idea

                                    6 comments  ·  SQL Server » Bugs  ·  Flag idea as inappropriate…  ·  Admin →
                                    planned  ·  Matteo Taveggia responded

                                    Thanks for the reporting the issue. We’ll take a look at it and prioritize accordingly.

                                    Solomon Rutzky commented  · 

                                    This is still an issue for SSMS v18.0 Preview 7 (SQL Server Management Studio 15.0.18092.0).

                                    Solomon Rutzky commented  · 

                                    This is still an issue for SSMS v18.0 Preview 6 (SQL Server Management Studio 15.0.18075.0).

                                    Solomon Rutzky commented  · 

                                    This is still an issue for SQL Server Management Studio v17.9.1 (14.0.17289.0) and SSMS v18.0 Preview 5 (SQL Server Management Studio 15.0.18068.0).

                                    Solomon Rutzky supported this idea  · 
                                    Solomon Rutzky commented  · 

                                    This is still an issue in SSMS 17.5:

                                    SELECT 'a' COLLATE CATALOG_DEFAULT;

                                    That statement works just fine, but IntelliSense indicates that it is an invalid Collation.

                                  • 4 votes
                                    Sign in
                                    Check!
                                    (thinking…)
                                    Reset
                                    or sign in with
                                      Password icon
                                      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  · 

                                      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  · 
                                    • 7 votes
                                      Vote
                                      Sign in
                                      Check!
                                      (thinking…)
                                      Reset
                                      or sign in with
                                        Password icon
                                        Signed in as (Sign out)
                                        You have left! (?) (thinking…)
                                        0 comments  ·  SQL Database  ·  Flag idea as inappropriate…  ·  Admin →
                                        Solomon Rutzky supported this idea  · 
                                      • 4 votes
                                        Sign in
                                        Check!
                                        (thinking…)
                                        Reset
                                        or sign in with
                                          Password icon
                                          Signed in as (Sign out)

                                          We’ll send you updates on this idea

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

                                          Upvotes: 24

                                          <=-=Jan 4 2011 4:40PM=-=>

                                          Hi Bob,

                                          Thanks for the feedback. We’ll consider fixing this in a future release. Can you tell me more about the scenario? What UDAgg were you implementing and why? Feel free to contact me by email if you want.

                                          Best regards,
                                          Eric Hanson
                                          Program Manager, SQL Server Query Processing
                                          eric.n.hanson@microsoft.com

                                          <=-=Jan 4 2011 5:15PM=-=>

                                          Sure Eric, there are a few that I can think of. This actually came about because of the following forum question:
                                          http://social.technet.microsoft.com/Forums/en-US/sqlnetfx/thread/957a5b94-c7d0-49d8-928d-7cccff14b0c6. I realized that the sort was required because of choice of stream aggregate. And he can’t put on every index possible to get rid of the sort.

                                          Second is that the spatial aggregates in Denali would need this funtionality. Related to that is that there’s a vendor product that consists of a library of UDAs, Fuzzy Logix (http://www.fuzzyl.com/in-database_analytics.php#) that could benefit from this flexibility as…

                                          Solomon Rutzky commented  · 

                                          This is still an issue in SQL Server 2017 CU12 and SQL Server 2019 CTP 2.2 😿.

                                          Solomon Rutzky supported this idea  · 
                                        • 30 votes
                                          Sign in
                                          Check!
                                          (thinking…)
                                          Reset
                                          or sign in with
                                            Password icon
                                            Signed in as (Sign out)

                                            We’ll send you updates on this idea

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

                                            Can someone please take a look at this? I do not believe that it requires that much work (though I do realize that there might be technical factors that I have no knowledge of that complicate this request), but the gains would be HUGE. This is something that really should have rolled out with SQL Server 2005. Being able to create an Asymmetric Key from binary hex bytes (i.e. FROM 0x...) would not only negate the need for trusted assemblies, but it would FINALLY allow folks to deploy from Visual Studio without resorting to setting "TRUSTWORTHY ON", which currently is really the only way to deploy SQLCLR from Visual Studio without jumping through lots of hoops ( https://sqlquantumleap.com/2017/08/09/sqlclr-vs-sql-server-2017-part-2-clr-strict-security-solution-1/ and https://sqlquantumleap.com/2017/08/16/sqlclr-vs-sql-server-2017-part-3-clr-strict-security-solution-2/ ).

                                            This would not only greatly assist in people being more successful with SQLCLR, but it would greatly reduce the number of databases that are currently set to "TRUSTWORTHY ON". And doesn't Microsoft recommend keeping TRUSTWORTHY OFF? I certainly do ( https://sqlquantumleap.com/2017/12/30/please-please-please-stop-using-impersonation-execute-as/ ). By not allowing Asymmetric Keys to be created inline via a varbinary literal or variable, Microsoft is pushing users to decrease the security of their systems (by setting TRUSTWORTHY ON) while at the same time recommending that they not do that.

                                            All that is _required_ to make life much, MUCH better for thousands (easily) of your customers is to allow creation of _just_ the public key, similar to creating the Asymmetric Key from an assembly or file. Allowing for the private key to be imported is a nice-to-have for consistency with CREATE CERTIFICATE, but it is absolutely not required as it would not affect these scenarios. Importing the private key would also require more work updating CREATE ASYMMETRIC KEY, and then require updates to ALTER ASYMMETRIC KEY. The effort required for dealing with the private key might not be worth it, hence it can be a separate project. But simply creating the basic Asymmetric Key (with just the public key, just like it currently works when creating from an assembly or file) will be a huge win for what should be not a lot of work. Please?

                                            PLEASE ? ? ? ? ? ? ? ?

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

                                          Feedback and Knowledge Base