Using SqlFileStream in CLR

  • Hi,

    I'm a newbe in SQL Server 2008.

    I created a stored procedure within a signed assembly created by VS 2008.

    This stored procedure load a varchar(MAX) field via SqlFileStream.

    I used code access security imperatively:

    PermissionSet perms = new PermissionSet(PermissionState.None);

    SqlClientPermission sqlPerm = new SqlClientPermission(PermissionState.None);

    sqlPerm.Add("context connection=true", "", KeyRestrictionBehavior.AllowOnly);

    perms.AddPermission(sqlPerm);

    FileIOPermission filePerm = new FileIOPermission(PermissionState.None);

    filePerm.AllLocalFiles = FileIOPermissionAccess.Read;

    perms.AddPermission(filePerm);

    perms.PermitOnly();

    With mscorcfg.msc I set FullTrust permission to the specific strong name (at machine policy level).

    I registered the DLL with SQL Server: ... WITH PERMISSION_SET = EXTERNAL_ACCESS and created a SQL Server procedure to access

    the .NET procedure.

    When I run the stored procedure I get this System.Security.SecurityException:

    „System.Security.Permissions.FileIOPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089”

    Then, I tired this:

    I created an asymmetric key for the assembly and grant external access to the assembly.

    I created a login for the asymmetric key.

    But the result is same:

    „System.Security.Permissions.FileIOPermission, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089”

    What permission settings must have using to a SqlFileStream in CLR

    Can anyone help me please?

    Thanks,

    Philip

  • Post the TSQL that you used to do this. Its probably just a single missing step in the setup you did. It should follow the instructions on the following article:

    Signing an Assembly with a Certificate (Visual Studio)

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • Thanks Jonathan for the reply.

    When I remove SqlFileStream specific code from the .NET procedure it works fine. But when it's enabled I get this error:

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

    System.ComponentModel.Win32Exception: Access is denied

    System.ComponentModel.Win32Exception:

    at System.Data.SqlTypes.SqlFileStream.OpenSqlFileStream(String path, Byte[] transactionContext, FileAccess access, FileOptions options, Int64 allocationSize)

    at System.Data.SqlTypes.SqlFileStream..ctor(String path, Byte[] transactionContext, FileAccess access, FileOptions options, Int64 allocationSize) at StoredProcedures.SqlFileStreamTest(String path, Byte[] ctx)

    I tried it with EXTERNAL and UNSAFE permission level.

    SqlFileStream must have special permission settings?

  • Your connection string should look something like this:

    connectionString="server=SERVERNAME;database=DATABASE;Integrated Security = true";

    FILESTREAM requires that you use Integrated Security to work properly. Whatever Windows User your application is running under must have proper access to the database including the ability to do reads and writes.

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • Hi Jonathan.

    I use Integrated Security.

    All other IO operations are working well in my SQLCLR procedure.

    However, the SqlFileStream part is always throw an exception.

    I have no idea what to do.

  • I think it's not possible to use the filestream API from SQL CLR code running in the same instance as the target filestream column. You can only use it to access filestream columns on a different instance. At least I'm sure that was the case prior to 2008 RTM, but I haven't been able to find any written confirmation about whether that's changed or not.

    I suggest you try to get filestream access using SQL CLR from a different instance. If that works but local access doesn't then perhaps that isn't a supported scenario.

  • David Portas (12/28/2009)


    I think it's not possible to use the filestream API from SQL CLR code running in the same instance as the target filestream column. You can only use it to access filestream columns on a different instance. At least I'm sure that was the case prior to 2008 RTM, but I haven't been able to find any written confirmation about whether that's changed or not.

    I suggest you try to get filestream access using SQL CLR from a different instance. If that works but local access doesn't then perhaps that isn't a supported scenario.

    It won't work through a context connection but a non-context connection using a standard connection string back onto the SQL Server should work, but it requires more than SAFE Assembly.

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • David, Jonathan thans for your feedback!

    I use Integrated Security through a non-context connection. Assembly permission set is EXTERNAL_ACCESS.

    When my SQL CLR stored procedure read/write sql server filestream using SqlParameter it's all right. When it using SqlFileStream:

    SqlFileStream sqlStream = new SqlFileStream(path, tranContext, FileAccess.Write, FileOptions.SequentialScan, 0);]

    I get same exception.

    I tried to use sql server filestream through the interop (native API) too. But I get an invalid SafeFileHandle.

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply