(The term “extended stored procedure” as referred to herein is shorthand for the general technique, available in many modern RDBMSes including Microsoft SQL Server, Oracly, MySQL et al., of linking in compiled code written in non-SQL languages for invocation as stored procedures, functions, and the like.)
I recently ran into a newfangled version of DLL hell (an example support post; there are many). The basic issue was that in a SQL Server CLR stored procedure of ours, the production host machines had a different version of some .NET assemblies in the GAC, or Global Assembly Cache. It was a platform assembly causing the issue, in this case resolved by running the following statement in each affected database:
ALTER ASSEMBLY [System.Runtime.Serialization] FROM 'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Runtime.Serialization.dll' WITH PERMISSION_SET = UNSAFE;
While prompted by security measures in Microsoft’s solution, this is not a very elegant or maintenance-free approach. Imagine a future patch bringing down production systems due to a newer version of a DLL suddenly included in GAC, for instance. The cloud makes things even more complex, since having to include even platform DLLs with lowered-safety permissions may make an application difficult or impossible to deploy in a cloud implementation such as Amazon’s RDS.
Unfortunately, there are some highly useful third-party libraries which may in a naive implementation require such extra problematic configuration, such as JSON.NET . Due to understandable security concerns with linking in unvetted code, most modern RDBMSes supporting extended stored procedures will make one jump through extra hoops to use any code which makes system-level calls, can perform I/O, and the like.
Problems like this can be avoided (while still reaping the benefits of stored procedures) with approaches like the following, each with pros and cons:
1. Avoid linking in general utility libraries with references to unsafe code.
2. Create a safe version of reusable utility code for inclusion in heightended-security containers and environments. Store this in a separate project or other compilable unit from the unsafe reusable code.
3. Investigate the use of data-platform features instead of potentially unsafe program code. For example, SQL Server 2016 includes certain features for working with JSON data that might make a library like JSON.NET largely unnecessary in a particular situation.
4. Find safer alternatives to popular libraries. To tack on to the JSON example, small, fast JSON parsers could avoid the need for something like JSON.NET, and even give performance enhancements to boot.
5. If all else fails, consider importing an unsafe library into your codebase and stripping out unsafe calls. This has the obvious drawback of increased code maintenance in case of any necessary patches, but could work for a stable, mature library. Freezing a version in this manner might actually improve the long-term stability of a dependent codebase in some cases.
Of course, this is all really another facet of planning for the cloud. In my current organization, we’re shifting away from using proprietary software like SQL Server in favor of open-source alternatives like MySQL, MariaDB, and MongDB partly for licensing reasons, but just as much for the benefits of broad cross-cloud support. Growing pains for companies shifting more systems to the cloud can be minimized with a shared approach, which explicitly plans a set of reusable, cloud-safe libraries from the start.