Security error connecting to SQL Server Express LocalDB in SQLCLR using EXTERNAL_ACCESS and "(localdb)\Instance" connection string
I am using:
- Windows 8.0 (though that shouldn't matter)
- SQL Server 2012 Developer Edition (64 bit) SP2 (11.0.5058.0)
- SQL Server 2014 Express Edition (64 bit) SP1 (12.0.4100.1)
- .NET Framework 4.5.2 installed
When connecting to SQL Server Express LocalDB via the "trusted_connection=true;server=(localdb)\InstanceName" syntax that became available in either the .NET Framework 4.0.2 Update or .NET Framework 4.5 (depending on what blog or documentation you go by), I can connect successfully to:
- an automatic instance: (localdb)\v11.0
- a named instance: (localdb)\Projects
- a shared named instance: (localdb).\SharedProjects
However, when doing that same simple test via SQLCLR in either SQL Server 2012 SP2 or SQL Server 2014 SP1, with an assembly marked as PERMISSIONSET = EXTERNALACCESS, I get the following error:
Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "ConnectTest":
System.Security.SecurityException: Request failed.
at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
However, I can successfully connect to both the automatic and named instances when using the Named Pipes syntax: "trusted_connection=true;server=np:\.\pipe\LOCALDB#xxxxxxxx\tsql\query". But this is not an acceptable work-around since the "xxxxxxxx" portion (after the "LOCALDB#" part) changes every time that the instance of LocalDB starts, so that connection string isn't terribly useful.
To be clear, this problem has nothing to do with the functionality being added to a .NET Framework version that is newer than what is shipped with SQL Server or that shows up when executing:
SELECT * FROM sys.dmclrproperties
The "version" field does report "v4.0.30319", but that is the CLR version, which covers .NET Framework versions 4.0 through the current 4.6. I am stating this mostly for the benefit of readers who are not aware of the distinction.
The "(localdb)\InstanceName" syntax should work. Since the security exception was for "PermissionSet.Demand", I figured I would try updating the assembly to PERMISSION_SET = UNSAFE. Trying those three connections again, the automatic instance and shared named instance worked as expected. The non-shared named instance did not connect, but that is also expected behavior since the named instance belonged to one user while the SQL Server service was logging on as another user. Since LocalDB is user-specific, the "SQL Server" login that was running the SQL Server process could only see the shared named instance. But again, needing to set the assembly to UNSAFE just to connect to LocalDB without using the always-changing Named Pipes provider is not an acceptable work-around.
Fortunately I did find something. The security exception was being caused by "System.Data.LocalDBAPI.DemandLocalDBPermissions". Taking a look at that class, "LocalDBAPI", we can see that it should be possible to connect via the "(localdb)\InstanceName" syntax with an EXTERNAL_ACCESS assembly. In that particular method, "DemandLocalDBPermissions" ( http://referencesource.microsoft.com/#System.Data/System/Data/SqlClient/LocalDBAPI.cs,262 ), the code is calling:
The value of "const_partialTrustFlagKey" is defined on line 27 as:
If I call:
BUT, if I create a separate assembly that is loaded into the same database with the same owner (so it is in the same AppDomain as the assembly with the database access code), and set this new assembly to UNSAFE while the assembly with the database access code is set to EXTERNAL_ACCESS, then if I call:
and THEN call the connection test using either "(localdb)\DefaultInstance" or "(localdb).\SharedInstance" as the "server" in the ConnectionString, then it DOES connect as expected. BUT, that still leaves me with an assembly that needs to be set to UNSAFE, just so that I can set this AppDomain property such that my other assembly can remain as EXTERNAL_ACCESS. That seems kinda silly. And I do see that there is an "AssertLocalDBPermissions()" method on line 287, which is only referenced in "SqlDependencyListener" ( http://referencesource.microsoft.com/#System.Data/System/Data/SqlClient/SqlDependencyListener.cs,123 ), but I am not sure if that helps any.
So, is it possible to add "ALLOWLOCALDBINPARTIALTRUST" set to "true" as a predefined system entry for all AppDomains created in SQL Server's CLR host? There is a list in the MSDN page for AppDomain.GetData ( https://msdn.microsoft.com/en-us/library/system.appdomain.getdata.aspx ) that shows quite a few predefined entries, so it seems like the intention from the beginning was to add that entry and it was just forgotten about or too low on the priority list. But, adding it would really, really help. Especially as LocalDB gets more popular.
Upvotes: 2<=-=Jul 15 2015 3:39PM=-=>
Not sure what happened (maybe my session timed out?), but this was supposed to be logged under my account. Is it possible to transfer it to my account?
Solomon Rutzky commented
Hi there. Can someone _please_ take a look at this? It has been 3.5 years since it was reported, and while there is clearly not much (or any) resources being devoted to improving SQLCLR, this fix should be _very_ low risk, if not no risk. It is merely adding a dictionary entry (i.e. "ALLOW_LOCALDB_IN_PARTIAL_TRUST") to the App Domain setup for all App Domains, or at least all user App Domains. This change doesn't _do_ anything. It is just a simple entry that will not even been seen by 99.99% of functionality. This isn't code, just configuration. I can't think of a single reason to not add this entry. It clearly was something that was intended to be added but was forgotten about. This is a very simple change that would greatly improve the usability of SQL Server Express LocalDB, and that's a good thing, right? 8-D, Please???? With a new version (2019) coming out soon, this is the perfect time to do this. Please??????
Solomon Rutzky commented