New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Make FSharp.Core safe for SQL CLR with IL initonly (somehow) on static field 'empty' in Map #3535
Comments
SQL Server long, long ago (~2003) decided to implement a (I believe crazy) set of additional checks on .NET IL above and beyond what is done by peverify.exe. I presume the people who decided to do this aren't even at Microsoft any more, and the decision has been lost in history. I've never seen these additional checks documented anywhere, nor any explanation of what soundness result is being established by the checks, and I don't know how to put it under automated test. This makes it really, really hard for other languages and code-generating tools to make sure that code can be run in SQL Server. We did try pretty hard to generate SQLServer-friendly constructs in 2005-2007 back when ".NET code running in SQL Server" was the big new thing. It is still a useful scenario, and if we had PRs with some user validation that we were following the rules - and some documentation of the rules with automated checking in the F# test suite - then we might be able to accept it, if it doesn't change codegen too much. |
They have an SQLCLR that is - as far as I know - implemented on top of SQLOS, in fact the plan is to support SQLCLR on Linux (please see the comments):
Pleae note, the newer version will have even more restriction in place, eg.: the 2017 version will only allow SAFE assemblies. My guess is that they implemented something like Joe-E which is a subset of the Java programming language intended to support programming according to object-capability discipline for SQLCLR: https://en.wikipedia.org/wiki/Joe-E
There are lot's of restriction what's available in SQLCLR: https://docs.microsoft.com/en-us/sql/relational-databases/clr-integration/database-objects/clr-integration-programming-model-restrictions http://www.sqlservercentral.com/articles/Stairway+Series/104406/ |
@zpodlovics Thanks for the links and the context! I still find it kind of extraordinary that there are these adhoc extra limitations for this one particular execution environment, and that there isn't a set of tools to help library writers and compiler implementers stick within those limitations. I mean, it's not like SQL is the only environment that wants to host CLR code, nor that these rules somehow create a sound sandbox environment. Anyway... The main problem is that FSharp.Core uses some of these constructs, e.g. non-readonly static fields to store amortized values such as the empty list, or the empty map and so on. It might be that we can adjust the compilation of FSHarp.Core. Anyway, if you or someone else can get the compilation of FSharp.Core to the state where its compiled form passes these tests, and we can keep it that way through automated testing, then I think we would accept PRs in this area. |
Hi there, @dsyme (and others). Just to clarify a few things here:
Hope this info helps :-). Take care, Solomon.. |
@srutzky Thanks for the info, it's the first explanation I've seen of the SQL checks since 2003. The restrictions feel adhoc since they were invented by SQL server in 2004-2005 without, AFAIK, documenting it until now. Good to have the docs.
FSharp.Core doesn't reference anything that's not on that list. It basically references nothing but There is one true global mutable I think, accessed via Async.DefaultCancellationToken. AFAIK FSharp.Core is about the safest component around, it's just a functional programming library. Perhaps SQL Server could add it to its safe list. It's Microsoft-signed so the strong name should be enough.
The way F# initializes values is slightly different to C#. Initializers for F# are run for an entire file, not class-by-class, so all static data declared in a file is initialized at once. A static constructor is used under the hood but it may initialize fields from multiple classes. This is entirely sound and valid according to ECMA 335, but means we can't mark fields as "readonly" and still be verifiable. The fields are, however, private. From the perspective of F# the added checks seem pointless, since the library is safe. FWIW as a compiler writer and language developer it's extremely hard to predict from ECMA 335 that some tool will impose the restriction that "static data must all be marked readonly, even private data". It's basically impossible to shape a language/library design and implementation if tools are free to impose extra adhoc rules like this... |
@dsyme Sorry for the delay in responding, just been so incredibly busy... I still think it would be awesome to get FSharp.Core working in SQL Server / SQLCLR as a
Yer quite welcome 😺
I think the documentation was actually there, at least from the release of SQL Server 2005, but it was likely not well advertised and not easy to find unless you knew to look for it there.
This definitely helps.
I'm not sure what, if any, impact this has. I think we will find out if / when we get passed the static variable issue.
It's probably not as simple as that to get a library added to the "supported" list. The libraries on that list are guaranteed:
I could be misunderstanding something here, but this does not sound like a conflict. Currently SQL Server appears to be initializing static class variables when each class is first referenced (I just tested this in SQL Server 2017 CU14). However, I don't see the practical harm in FSharp.Code initializing static class variables for all classes at the same time.
I don't understand the "we can't mark fields as readonly and still be verifiable" part, but don't know that I need to either 😉 . However, the fields being private is irrelevant to the " That being said, if you simply cannot mark the static class variables as " |
They are never changed after initialization, with the exception of
So it does! Yes, I think that may work |
Awesome! So we are at least a little closer 😄 Two questions:
|
Yes it does - see here, it is not protected via lock. We could protect it
@KevinRansom @brettfo Can you confirm? thanks! |
I don't know how the dotnet/arcade SDK handles signing; I only know that it uses the @tmat might know about signing/certs, though. |
Yes, our assemblies are signed with SHA2 AuthentiCode certificate. |
@tmat Thanks for that info! It actually reminded me that I can verify this myself: since FSharp.Core is available via NuGet, I just need to add it to a project and check FSharp.Core.dll's properties. So that's what I did. It appears to be signed with the same Certificate(s) used for the .NET Framework v4.x libraries. And that's very good news as it makes the deployment script much, much easier. In fact, I have already tested this by loading the MS certificate (the SHA1 digest cert with thumbprint = Assuming that:
then I can take care of the deployment aspect of this, and will assist on the automated testing aspect. At this point I am not sure how to proceed, but here is a general overview of what will be involved (as far as I can tell, of course): BUILDUltimately we need a completely self-contained deployment script that can be added to a project as a pre-release step, or handed to a DBA to execute once wherever needed, etc. Providing only the DLL and instructions on how to deploy it will only cause stress (trying to reinvent this wheel), lead to bad security practices (trying to get this done the quickest way possible), or giving up on F# for SQLCLR.
I have used this pattern before, so this script is already partially complete. TESTI'm not sure if you want to include the final deployment script in the automated testing. If you do, here is what's needed:
At this point, as long as the FSharp.Core assembly has been created, then success! At least to a degree. The goal is to get FSharp.Core loaded as a Sorry for the lengthy post, but this stuff needed to be documented somewhere to get a sense of scope. If there is a more appropriate place to put the above info, just let me know. At this point, the only major hurdle (that I can see) is resolving the concurrency issue related to |
Is there something specific me and the boys, e.g., @August-Alm, can help out with to get this mess sorted out? Since the issue has been moving in and out of F# Compiler and Tooling there might be more to the story than I’m made aware of from this thread, any pointers? My company works on a boutique compiler for the CLR and we also have the main parts of our core banking system at current being dependent on both SQLCLR and F#, so I we have the incentives sort this out smoothly. |
I want to use F# to create CLR functions for SQL Server but I cannot publish them automatically because FSharp.Core is not "safe" as far as SQL Server is concerned.
Specifically, I get the error message:
Of course, it may be that some other resolution to 'empty' is needed other than the one suggested above, but it's the first message I've seen stating why FSharp.Core isn't safe, but I don't have the chops to address it without the worry of mucking up something else.
Details
A Visual Studio (2017) solution with two projects: 1) an F# library, 2) a SQL Server Database Project.
A simple library could be:
The database project can be empty, just add a reference the library. Building/Publishing the DB project will do the work of creating the deploy SQL for an assembly binary and making the function(s) available in T-SQL.
Try to publish the DB project to a SQL Server; fails with the above message.
Contrariwise, the same DB project with a C# library auto-published works fine.
Note
Strictly speaking, the 'direct translation' of a working C# library would look like this:
but doesn't make a difference.
The text was updated successfully, but these errors were encountered: