i'm writing data to xml file by creating folder and file if not exists in clr stored procedure in c#

Question

I'm trying this;

public partial class StoredProcedures
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void ExportXML_v1_2(string file_path, string file_name, string xml_data, out int status, out string error_messages)
    {
        string Folder1 = file_path;
        string xml = xml_data;
        error_messages = "";
        status = 0;
        try
        {
            if (!Directory.Exists(Folder1))
            {
                Directory.CreateDirectory(Folder1);
            }

I get this error;

System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.FileIOPermission, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed. at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet) at System.Security.CodeAccessPermission.Demand() at System.IO.Directory.InternalCreateDirectoryHelper(String path, Boolean checkHost) at StoredProcedures.ExportXML_v1_2(String file_path, String file_name, String xml_data, Int32& status, String& error_messages)

The action that failed was: Demand The type of the first permission
that failed was: System.Security.Permissions.FileIOPermission
The Zone of the assembly that failed was: MyComputer

did u check social.msdn.microsoft.com/Forums/sqlserver/en-US/… — Daniel B

Solomon Rutzky Solomon Rutzky · Accepted Answer · 2017-10-22T17:17:50

You need to mark the Assembly as WITH PERMISSION_SET = EXTERNAL_ACCESS. And, in order to do that you need to:

Sign the assembly
Create an Asymmetric Key in master
Create a login from that asymmetric key
Grant that login the EXTERNAL ACCESS ASSEMBLY permission

Of course, there are some variations on how you might go about this, depending on if you are using Visual Studio / SSDT, especially in light of recent changes introduced in SQL Server 2017. For details, please see two blog posts that I have written detailing two methods of accomplishing this, fully automated within Visual Studio, and working within the new "clr strict security" constraint of SQL Server 2017:

Also, you should not be using the string type as an input / output parameter type when working with SQLCLR. You should use the appropriate Sql* types, and in this case that would be SqlString. Then, you get the actual string value via the Value property. And, for the xml_data parameter, use the SqlXml type as you can create an XmlReader from it which should not only be slightly faster, but it will also allow for better handling of changing the encoding (to UTF-8 most likely) as well as proper handling of the <?xml ... ?> declaration at the top. For example:

public static void ExportXML_v1_2(SqlString file_path, SqlString file_name,
   SqlXml xml_data, out int status, out SqlString error_messages)
{
    if (file_path.IsNull)
    {
       error_messages = "@file_path cannot be NULL.";
       return;
    }

    string Folder1 = file_path.Value;

For more information on working with SQLCLR in General, please see the series that I am writing on this topic on SQL Server Central: Stairway to SQLCLR (especially Level 5).

And, FWIW, a function that saves XML to a file, handles formatting (e.g. indenting, etc), append vs overwrite, and encoding (including specifying it in the <?xml ... ?> declaration) is available in SQL# (which I wrote). The function is XML_SaveToFile, and while many functions are available in the Free version, this particular function is only available in the Full (i.e. paid for) version.

i'm writing data to xml file by creating folder and file if not exists in clr stored procedure in c#

1 Answers