none
Accessing IO resources from CLR stored procedure RRS feed

  • Question

  • Hi There

    I am trying to write a CLR stored procedure that will return all the data about the fixed drives on a SQL server.

    Here is my code:

    using

     

    System;

    using

     

    System.Data;

    using

     

    System.Data.SqlClient;

    using

     

    System.Data.SqlTypes;

    using

     

    Microsoft.SqlServer.Server;

    using

     

    System.IO;

     

    public

     

    partial class StoredProcedures

    {

    [Microsoft.SqlServer.Server.

    SqlProcedure]

     

    public static void Test()

    {

     

    SqlPipe sp = SqlContext.Pipe;

     

    String strCurrentTime = DiskSpace();

    sp.Send(strCurrentTime);

    }

     

    //[Microsoft.SqlServer.Server.SqlFunction(SystemDataAccess =SystemDataAccessKind.Read)]

     

    //[System.Security.Permissions.PermissionSet(System.Security.Permissions.SecurityAction.Assert, Unrestricted =true)]

     

    public static string DiskSpace()

    {

     

    new System.Security.Permissions.SecurityPermission(System.Security.Permissions.PermissionState.Unrestricted).Assert();

     

    String Drives = "";

     

    DriveInfo[] allDrives = DriveInfo.GetDrives();

     

    foreach (DriveInfo d in allDrives)

    {

    Drives = (

    "Drive " + d.Name);

     

    //Console.WriteLine(" File type: {0}", d.DriveType);

     

    if (d.IsReady == true)

    {

    Drives +=

    " Volume label: " + d.VolumeLabel;

     

    //Console.WriteLine(" File system: {0}", d.DriveFormat);

     

    //Console.WriteLine(

     

    // " Available space to current user:{0, 15} bytes",

     

    //d.AvailableFreeSpace);

    Drives +=

    " Total available space: " + d.TotalFreeSpace + " bytes";

    Drives +=

    " Total size of drive: " + d.TotalSize + "bytes ";

    }

    }

     

     

    return Drives;

    }

    I am getting a code access security error like this:

    Msg 6522, Level 16, State 1, Procedure Test, Line 0

    A .NET Framework error occurred during execution of user-defined routine or aggregate "Test":

    System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

    System.Security.SecurityException:

    at System.Security.CodeAccessSecurityEngine.CheckNReturnSO(PermissionToken permToken, CodeAccessPermission demand, StackCrawlMark& stackMark, Int32 unrestrictedOverride, Int32 create)

    at System.Security.CodeAccessSecurityEngine.Assert(CodeAccessPermission cap, StackCrawlMark& stackMark)

    at System.Security.CodeAccessPermission.Assert()

    at StoredProcedures.DiskSpace()

    at StoredProcedures.Test()

    when I try to run the proc,,

     

    Can anyone tell me what I am doing wrong?

     

     

    Do I need to sign the code before I deploy maybe?

     

    Monday, August 16, 2010 4:17 AM

Answers

All replies

  • Have you cataloged the assembly as "permission_set = external_access"? If you're using Visual Studio to do the deployment that setting is in the project settings in the data tab.
     
    Cheers,
    Bob Beauchemin
    SQLskills
    "AndyMay" wrote in message news:58b01172-1487-46a9-88bc-bd0970782d1d...

    Hi There

    I am trying to write a CLR stored procedure that will return all the data about the fixed drives on a SQL server.

    Here is my code:

    using

     

    System;

    using

     

    System.Data;

    using

     

    System.Data.SqlClient;

    using

     

    System.Data.SqlTypes;

    using

     

    Microsoft.SqlServer.Server;

    using

     

    System.IO;

     

    public

     

    partial class StoredProcedures

    {

    [Microsoft.SqlServer.Server.

    SqlProcedure]

     

    public static void Test()

    {

     

    SqlPipe sp = SqlContext.Pipe;

     

    String strCurrentTime = DiskSpace();

    sp.Send(strCurrentTime);

    }

     

    //[Microsoft.SqlServer.Server.SqlFunction(SystemDataAccess =SystemDataAccessKind.Read)]

     

    //[System.Security.Permissions.PermissionSet(System.Security.Permissions.SecurityAction.Assert, Unrestricted =true)]

     

    public static string DiskSpace()

    {

     

    new System.Security.Permissions.SecurityPermission(System.Security.Permissions.PermissionState.Unrestricted).Assert();

     

    String Drives = "";

     

    DriveInfo[] allDrives = DriveInfo.GetDrives();

     

    foreach (DriveInfo d in allDrives)

    {

    Drives = (

    "Drive " + d.Name);

     

    //Console.WriteLine(" File type: {0}", d.DriveType);

     

    if (d.IsReady == true)

    {

    Drives +=

    " Volume label: " + d.VolumeLabel;

     

    //Console.WriteLine(" File system: {0}", d.DriveFormat);

     

    //Console.WriteLine(

     

    // " Available space to current user:{0, 15} bytes",

     

    //d.AvailableFreeSpace);

    Drives +=

    " Total available space: " + d.TotalFreeSpace + " bytes";

    Drives +=

    " Total size of drive: " + d.TotalSize + "bytes ";

    }

    }

     

     

    return Drives;

    }

    I am getting a code access security error like this:

    Msg 6522, Level 16, State 1, Procedure Test, Line 0

    A .NET Framework error occurred during execution of user-defined routine or aggregate "Test":

    System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.SecurityPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

    System.Security.SecurityException:

    at System.Security.CodeAccessSecurityEngine.CheckNReturnSO(PermissionToken permToken, CodeAccessPermission demand, StackCrawlMark& stackMark, Int32 unrestrictedOverride, Int32 create)

    at System.Security.CodeAccessSecurityEngine.Assert(CodeAccessPermission cap, StackCrawlMark& stackMark)

    at System.Security.CodeAccessPermission.Assert()

    at StoredProcedures.DiskSpace()

    at StoredProcedures.Test()

    when I try to run the proc,,

     

    Can anyone tell me what I am doing wrong?

     

     

    Do I need to sign the code before I deploy maybe?

     

    Monday, August 16, 2010 4:54 AM
    Moderator
  • Hi,

    Adding to that, for setting permission_set = external_access and create an assemble, your database option TRUSTWORTHY must be set to on if you do noy want to sign an assemble with certificate.

     


    -Chintak
    Monday, August 16, 2010 5:07 AM
  • Hi Chintak

    I have made the database trustworthy and change the code permission_set to external_access I am also the dbo of the database, but it still throws a security exception.

    -Andy

    Monday, August 16, 2010 9:45 PM
  • Hi Andy,

    As your CLR stored procedure references UNSAFE assemblies, we need to set the TRUSTWORTHY database property to ON and explicitly register the project assembly with UNSAFE permissions (PERMISSION_SET = UNSAFE).

    If you deploy the CLR stored procedure using Visual Studio, please right-click the project name in Solution Explorer, select Properties and change the Permission Level to Unsafe in Database tab.

    Also, I think we need change “Drives = ("Drive " + d.Name)” to “Drives += ("Drive " + d.Name)”. Otherwise, the CLR stored procedure only returns the information of the last drive.


    References:

    CREATE ASSEMBLY (Transact-SQL)
    http://msdn.microsoft.com/en-us/library/ms189524.aspx


    ALTER DATABASE SET Options (Transact-SQL)
    http://technet.microsoft.com/en-us/library/bb522682.aspx


    Supported .NET Framework Libraries
    http://msdn.microsoft.com/en-us/library/ms403279.aspx


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    • Marked as answer by AndyMay Tuesday, August 17, 2010 8:11 PM
    Tuesday, August 17, 2010 9:23 AM
  • Thanks for that Jian

    I have got the code running and thanks for picking up my mistake with code.

    I am a little bit puzzled about the unsafe option, the code for driveinfo is part of the System.IO namespace which is in the mscorlib.dll, which is in the list of supported assemblies. Why then does anything that uses it have to be registered as "Unsafe"?

    Regards

    Andy

    Tuesday, August 17, 2010 8:24 PM
  • If we grant SAFE permission to an assembly, the assembly cannot access resources outside SQL Server such as files, the network or the registry. In your case, we need to use DriveInfo to retrieve drive information, so that we cannot grant SAFE permission to the assembly.

    If we grant the UNSAFE permission to an assembly, the assembly has unrestricted access to resources, can call unmanaged code and is free to perform operations in the SQL Server process space. Usually, it’s recommended to grant this permission only to those highly trusted assemblies.

    The supported libraries are tested to ensure that they meet reliability and security standards for interaction with SQL Server, if they need to access external resources, we have to specify EXTERNAL_ACCESS or UNSAFE.

    Please see:

    CLR Integration Code Access Security
    http://msdn.microsoft.com/en-us/library/ms345101.aspx


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Thursday, August 19, 2010 6:35 AM
  • The accepted answer, while it does work, is misleading and is advocating opening up a security hole, something which is entirely unnecessary!! There is absolutely no reason to enable TRUSTWORTHY on this database and readers should not follow that bad advice (ideally that answer would be unmarked as accepted). All that is needed to mark an Assembly as EXTERNAL_ACCESS or UNSAFE while keeping TRUSTWORTHY set to OFF is:

    • sign the Assembly (in Visual Studio or using sn.exe)
    • create an Asymmetric Key in [master] from the Assembly (or from the .snk file)
    • Create a Login from that Asymmetric Key
    • Grant that Key-based Login either EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY

    There are very, very few cases where TRUSTWORTHY needs to be set to ON. Please do not do so unless absolutely necessary.

    I am a little bit puzzled about the unsafe option, the code for driveinfo is part of the System.IO namespace which is in the mscorlib.dll, which is in the list of supported assemblies. Why then does anything that uses it have to be registered as "Unsafe"?

    Regards

    Andy

    Hi Andy. While you are correct that the library you are using is in the supported list, that just means that the code has been tested and verified to work correctly, and just as important, a library being in that list means that it is guaranteed to work across future .NET Framework updates. Libraries that are not in that list not only need to be loaded manually, and usually as UNSAFE, but they are not guaranteed to work across future .NET Framework updates. Sometimes a library is changed from pure MSIL to mixed-mode (i.e. it contains both managed and unmanaged code), and SQL only allows for pure MSIL libraries. The ServiceModel library (an unsupported library), prior to .NET 4.0, was pure MSIL and so people imported it into SQL Server 2005, 2008, and 2008 R2 to do Web Service calls. Starting in .NET 4.0 it was changed to mixed-mode and could no longer be imported. Any project using it successfully on 2005, 2008, or 2008 R2 that wanted to upgrade to 2012 (or newer) had to be rewritten to not use ServiceModel.

    Some of the actions that can be performed via supported libraries require EXTERNAL_ACCESS so that they can go outside of SQL Server to the file system, or to the network, etc. Some actions require UNSAFE due to the type of operations (updating static variables, etc) or being marked with a Host Protection Attribute (there are many different attributes that are for different reasons for requiring UNSAFE).

    The specific operation that you are doing -- DriveInfo.GetDrives(); -- is what is requiring UNSAFE due to it scanning the hardware for available drives. If you removed that and instead specified specific drive letters to get the info for, that would only require EXTERNAL_ACCESS.


    Friday, July 14, 2017 3:52 PM
  • Hi Bob. "external_access" won't work due to the O.P. calling "GetDrives()". Please see my answer below (or possibly above ;-) for details.

    Wednesday, August 23, 2017 9:56 PM