Using SqlFileStream in CLR

Question

Using SqlFileStream in CLR

ifilak 38669

Old Hand

Points: 396
More actions
December 23, 2009 at 6:51 am

#215054

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

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

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

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

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)

ifilak 38669 Old Hand Points: 396 More actions · Answer 2

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?

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 3

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.

ifilak 38669 Old Hand Points: 396 More actions · Answer 4

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.

sqlvogel SSCrazy Eights Points: 9526 More actions · Answer 5

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.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 6

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.

ifilak 38669 Old Hand Points: 396 More actions · Answer 7

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.

Using SqlFileStream in CLR

Cookies on SQLServerCentral