October 24, 2018 at 8:35 pm
Comments posted to this topic are about the item CLR Strict Security
October 25, 2018 at 12:14 am
That is really good to know, thanks Steve
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
October 25, 2018 at 5:06 am
yep, good to know, cheers
---------------------------------------------------------------------------------------
The more you know, the more you know that you dont know
October 25, 2018 at 10:16 am
Shayn and Stewart:
No, not "good to know" since this information is absolutely false. The wording found in that SQL Server 2017 documentation stating that all SQLCLR code is considered "unsafe" (starting with SQL Server 2017, if "CLR strict security" is enabled, and it is by default), is misleading. It only means that all verification steps are processed, none are skipped, when loading SQLCLR assemblies, both at creation time (via CREATE ASSEMBLY / ALTER ASSEMBLY) and at runtime (when SQLCLR objects are accessed). Previously you could CREATE ASSEMBLY that contained "unsafe" code yet was marked as either SAFE or EXTERNAL_ACCESS, but it would fail at runtime if you either didn't have TRUSTWORTHY enabled (bad choice) or had not signed the assembly and had the matching signature-based Login that had been granted the UNSAFE ASSEMBLY permission. But if you mark your assembly as SAFE in SQL Server 2017 or 2019, then you will not be able to access the file system, network, etc.
I am working on a post right now that will clarify this and provide a simple example to prove that a SAFE Assembly, even with "CLR strict security" enabled, will not be able to access any external resource, let alone do something requiring the UNSAFE permissions set. I will post again once I have that blog post up.
Steve:
Please either remove this question or provide an answer for people to choose that is correct (the sooner the better given that this question is spreading misinformation on a topic that is already flooded with misinformation). Currently all answer choices are incorrect; no correct option is available to choose. The actual correct answer (for the question as it is currently worded), is:
"No change from previous versions of SQL Server: EXTERNAL_ACCESS only allows for accessing external resources, and UNSAFE allows for accessing external resources, calling unmanaged code, and a few other things."
It should also be noted that the restriction imposed by "CLR strict security" is ignored if the database has TRUSTWORTHY enabled (another reason to not enable TRUSTWORTHY). However, this doesn't change the answer since both "CLR strict security" and TRUSTWORTHY affect what PERMISSION_SETs are allowed, not what can be done within those PERMISSION_SETs.
Take care, Solomon..
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
October 25, 2018 at 11:56 pm
Solomon Rutzky - Thursday, October 25, 2018 10:16 AMShayn and Stewart:
No, not "good to know" since this information is absolutely false. The wording found in that SQL Server 2017 documentation stating that all SQLCLR code is considered "unsafe" (starting with SQL Server 2017, if "CLR strict security" is enabled, and it is by default), is misleading. It only means that all verification steps are processed, none are skipped, when loading SQLCLR assemblies, both at creation time (via CREATE ASSEMBLY / ALTER ASSEMBLY) and at runtime (when SQLCLR objects are accessed). Previously you could CREATE ASSEMBLY that contained "unsafe" code yet was marked as either SAFE or EXTERNAL_ACCESS, but it would fail at runtime if you either didn't have TRUSTWORTHY enabled (bad choice) or had not signed the assembly and had the matching signature-based Login that had been granted the UNSAFE ASSEMBLY permission. But if you mark your assembly as SAFE in SQL Server 2017 or 2019, then you will not be able to access the file system, network, etc.I am working on a post right now that will clarify this and provide a simple example to prove that a SAFE Assembly, even with "CLR strict security" enabled, will not be able to access any external resource, let alone do something requiring the UNSAFE permissions set. I will post again once I have that blog post up.
Steve:
Please either remove this question or provide an answer for people to choose that is correct (the sooner the better given that this question is spreading misinformation on a topic that is already flooded with misinformation). Currently all answer choices are incorrect; no correct option is available to choose. The actual correct answer (for the question as it is currently worded), is:"No change from previous versions of SQL Server: EXTERNAL_ACCESS only allows for accessing external resources, and UNSAFE allows for accessing external resources, calling unmanaged code, and a few other things."
It should also be noted that the restriction imposed by "CLR strict security" is ignored if the database has TRUSTWORTHY enabled (another reason to not enable TRUSTWORTHY). However, this doesn't change the answer since both "CLR strict security" and TRUSTWORTHY affect what PERMISSION_SETs are allowed, not what can be done within those PERMISSION_SETs.
Take care, Solomon..
Now that makes a lot more sense, thanks, Solomon.
i do currently not have much CLR in my current client's databases (having managed code in a database engine goes against the company policy), so am rather rusty...
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
October 26, 2018 at 8:11 am
Question and answer altered to make this slightly better.
I'm not completely sure I understand it, and the docs aren't great here. I need to sit down and better understand this and submit some PRs for better docs.
October 26, 2018 at 8:41 am
Steve Jones - SSC Editor - Friday, October 26, 2018 8:11 AMQuestion and answer altered to make this slightly better.I'm not completely sure I understand it, and the docs aren't great here. I need to sit down and better understand this and submit some PRs for better docs.
Ok, those alterations are definitely better (outside of my answer now being incorrect 😉 ). Thanks. I will try to get a post done today that will explain this fully, including examples. I will also submit a fix for the documentation, so you don't need to worry about that. Besides, there is nobody at MS that is able to fix the documentation because nobody understands (or sees) the bigger picture of how this security stuff (including module signing and certificates) actually works. This is why there are other, rather egregious mistakes in the MS documentation (that I haven't had the time to fix yet), and why the "trusted assemblies" boondoggle was introduced (nobody knew that the problem had been solved in SQL Server 2005, and hence was not a problem at all).
Take care,
Solomon..
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
October 31, 2018 at 1:27 am
Ok, I have finally finished the post describing how PERMISSION_SET is affected by having "CLR strict security" enabled:
SQLCLR vs SQL Server 2017, Part 9: Does PERMISSION_SET Still Matter, or is Everything Now UNSAFE?
Take care,
Solomon...
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
October 31, 2018 at 2:07 am
Solomon Rutzky - Wednesday, October 31, 2018 1:27 AMOk, I have finally finished the post describing how PERMISSION_SET is affected by having "CLR strict security" enabled:SQLCLR vs SQL Server 2017, Part 9: Does PERMISSION_SET Still Matter, or is Everything Now UNSAFE?
Take care,
Solomon...
Nice, thanks for this. Really makes a lot more sense
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
November 2, 2018 at 12:16 pm
Stewart "Arturius" Campbell - Wednesday, October 31, 2018 2:07 AMSolomon Rutzky - Wednesday, October 31, 2018 1:27 AMOk, I have finally finished the post describing how PERMISSION_SET is affected by having "CLR strict security" enabled:SQLCLR vs SQL Server 2017, Part 9: Does PERMISSION_SET Still Matter, or is Everything Now UNSAFE?
Take care,
Solomon...Nice, thanks for this. Really makes a lot more sense
You are quite welcome. I also added a paragraph at the end (in bold), tying it back more directly to the concept of "When clr strict security is enabled, all assemblies are treated as UNSAFE."
Take care, Solomon...
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply