C# CLR throws security exception unless marked as UNSAFE

Question

I have a C# CLR (.NET 4.5) stored procedure in SQL Server 2012 that is called from within a transaction. The CLR calls a WebService that requires TLS 1.2.

Everything works fine if I use create the assembly with permission_set = UNSAFE, but I would really like to avoid this and instead use EXTERNAL_ACCESS. However, this is proving a real challenge.

There are two problems when using EXTERNAL_ACCESS:

1) I write to a log file from the CLR (for maintenance & debugging purposes), which does not work since locks are not allowed:

private static readonly object Locker = new object();
public static string LogMessage(string message)
{
    message = String.Format("{0}: {1}" + Environment.NewLine, DateTime.Now, message);

    lock (Locker)
    {
        var file = new FileStream(GetConfigValue("LogFile"), FileMode.Append, FileAccess.Write);
        var sw = new StreamWriter(file);
        sw.Write(message);
        sw.Flush();
        sw.Close();
    }
    return message;
}

The error message:

System.Security.HostProtectionException: Attempted to perform an operation that was forbidden by the CLR host.

The protected resources (only available with full trust) were: All the demanded resources were: Synchronization, ExternalThreading

I could log to a database table instead, but since the call to the CLR is from within a transaction, errors would cause a rollback, which would also rollback the log record. I could use the event log, but I really do not want to do this if at all possible.

2) The web service that I call requires TLS 1.2, but setting the ServerCertificateValidationCallback is not allowed:

ServicePointManager.ServerCertificateValidationCallback = AcceptAllCertifications;

The error message:

System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.SecurityPermission, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
System.Security.SecurityException:
at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet) at System.Security.CodeAccessPermission.Demand() at System.Net.ServicePointManager.set_ServerCertificateValidationCallback(RemoteCertificateValidationCallback value) at AcmeClr.StoredProcedures.ProcessPayment(SqlMoney amount, SqlString ticketNo, SqlString& resultCode, SqlString& resultText)

Is there anyway around this issue - is there a way to make this work without using UNSAFE?

Solomon Rutzky Solomon Rutzky · Accepted Answer · 2015-11-16T06:09:31

For Issue #1 (using locks to manage multiple threads / sessions writing to the same log file at the same time), there is no way to use locks in anything but UNSAFE mode. However, the good news is that you probably don't need to use locks in the first place. All the lock is really accomplishing is to ensure that two LogMessage() calls happening at the same time don't conflict, with one of them getting an error. That should be solvable by using another overload of the FileStream constructor that allows for passing in the FileShare option / enum. You should be able to pass in FileShare.ReadWrite to prevent the error. You are already concatenating DateTime.Now into message so that should help resolve sequence issues if an earlier message gets written after a later one.

But outside of that particular contention, even with the lock you still have the issue of two sessions executing the SQLCLR WebService stored procedure at roughly the same time interleaving their messages. How do you distinguish the messages? I would suggest creating a new Guid at the started of the Stored Procedure and concatenating that into each message so that you can correlate messages of a particular call to the Stored Procedure (I assume your code has more than one place where it calls LogMessage()) and distinguish those messages from other calls (whether concurrent or not).

If for any reason the two pieces above (FileShare.ReadWrite and concatenating a Guid into message) don't prevent the error, then you can forget about the FileShare option (though I still prefer to set it to Read so I can easily check the file as it is being written) and instead of concatenating the Guid into message, append it to the end of the GetConfigValue("LogFile") value, just prior to the file extension. Then the messages of a particular call are automatically correlated with each other and distinguished from other concurrent calls. It just means you have a bunch of log files.

Three other thoughts on this:

By having the lock in the LogMessage() method, you are actually increasing blocking because concurrent calls to this method across different sessions will have to wait for the lock owner to release the lock. After all, that is kinda the point of the lock in the first place, right? Except when dealing with transactions, you really don't want to prolong them any more than absolutely necessary, and the very nature of a Web Service call via SQLCLR is that you are already making the transaction dependent upon network latency and the responsiveness of that external system (even if it is in-house).
You mentioned:

I could log to a database table instead, but since the call to the CLR is from within a transaction, errors would cause a rollback, which would also rollback the log record.

Not necessarily. If you use the in-process "Context Connection", then yes, this is true. And it is also true if you use a regular / external connection IF you either do not specify the Enlist keyword in the Connection String or if you do specify Enlist = true;. However, if you specify Enlist = false; then it should be separate / disconnected transactions that would not get rolled-back if the transaction that called the Stored Procedure gets rolled-back.
And while this might not help in the situation of using SQLCLR code, I will at least mention that for pure T-SQL code, when wanting to get around this issue of losing log records when a roll-back occurs, you have the option of writing those records initially to a table variable. Table variables are not transaction-bound and so rollbacks do not affect them. The downside is that if the process dies in such a way that it terminates fully prior to getting to the part where after the roll-back it inserts the Table Variable's records into a real table, then you do lose those records. That is where it is sometimes nicer to write to a log file or use SQLCLR to make a regular / external connection using Enlist = false;.

For Issue #2 (setting ServicePointManager.ServerCertificateValidationCallback), unfortunately there isn't much you can do about this. I have run into the same issue when trying to set the UseSSL property to true with FtpWebRequest in order to support FTPS. I have not yet found a way around needing to mark the assembly as PERMISSION_SET = UNSAFE when doing this.

Side notes:

I agree with the desire to keep the Assembly as EXTERNAL_ACCESS instead of UNSAFE, but given that this is your code and you are not using UNSAFE to pull in any 3rd party libraries or unsupported .NET Framework libraries, the actual risk level is pretty low.
Also, hopefully you are using an Asymmetric Key- or Certificate- based Login to allow the Assembly to have a non-SAFE permission and not setting the database to TRUSTWORTHY ON.
Unless the code shown in the Question was simplified for the purpose of posting only the code that is "necessary" in order to communicate the immediate issue, you are missing error handling around the external resources. If the process crashes, the log file will be locked until the App Domain is unloaded, if you fail to properly dispose of the StreamWriter and the FileStream. You can either add the try / finally structure around those 5 lines, or you can have the compiler do that for you by using the using() construct / macro.

C# CLR throws security exception unless marked as UNSAFE

1 Answers