December 23, 2009 at 6:51 am
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
December 23, 2009 at 2:33 pm
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]
December 24, 2009 at 2:37 am
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?
December 24, 2009 at 10:20 am
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]
December 27, 2009 at 8:24 am
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.
December 28, 2009 at 3:22 pm
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.
December 28, 2009 at 6:48 pm
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]
December 29, 2009 at 5:34 am
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy