C# Stored procedure

When I execute the stored procedure (a C# dll) from Management Studio, I get
the following error:
===================================================== Msg 6522, Level 16, State 1, Procedure LoadImageToDB, Line 0
A .NET Framework error occurred during execution of user defined routine or
aggregate 'LoadImageToDB':
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.LoadImageToDB()
.
====================================================
Can anyone point me into the right direction? Thanks.Hi,
Error 6522 is the wrapped .NET error code. you are calling a function
or assembly which isn=B4t *secure* for SQL Server, so step through your
code and try to see what is happening.
HTH, Jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||This is the message I get when I debug the code. Any ideas?
====================================================
A first chance exception of type 'System.Security.SecurityException'
occurred in mscorlib.dll
====================================================
"Jens" wrote:
> Hi,
> Error 6522 is the wrapped .NET error code. you are calling a function
> or assembly which isn´t *secure* for SQL Server, so step through your
> code and try to see what is happening.
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>|||How did you register the code as SAFE, UNSAFE or EXTERNAL_ACCESS ?
Depending on your settings the code isn=B4t allowed to execute several
things. There are only a few classes which are allowed to execute with
*only* safe permissions.
HTH; Jens Suessmeyer.
--
http://www.sqlserver2005.de
--|||It is registered as SAFE. What do I need to change to make it work? Any help
is appreciated. Thanks.
"Jens" wrote:
> How did you register the code as SAFE, UNSAFE or EXTERNAL_ACCESS ?
> Depending on your settings the code isn´t allowed to execute several
> things. There are only a few classes which are allowed to execute with
> *only* safe permissions.
> HTH; Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>|||Guessing from the name LoadImagetoDB you'll need EXTERNAL_ACCESS in order to
access the image (file?) you want to load into the database.
--
HTH,
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
"KMP" <KMP@.discussions.microsoft.com> wrote in message
news:0BC394CB-2E61-40F1-BC01-0439622CE994@.microsoft.com...
> It is registered as SAFE. What do I need to change to make it work? Any
> help
> is appreciated. Thanks.
> "Jens" wrote:
>> How did you register the code as SAFE, UNSAFE or EXTERNAL_ACCESS ?
>> Depending on your settings the code isn´t allowed to execute several
>> things. There are only a few classes which are allowed to execute with
>> *only* safe permissions.
>> HTH; Jens Suessmeyer.
>> --
>> http://www.sqlserver2005.de
>> --
>>|||When I do that, I get an error message saying "LoadImage failed because
assembly is not authorized for PERMISSION_SET = EXTERNAL_ACCESS". This is
just part of the error message.
"Jasper Smith" wrote:
> Guessing from the name LoadImagetoDB you'll need EXTERNAL_ACCESS in order to
> access the image (file?) you want to load into the database.
> --
> HTH,
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
>
> "KMP" <KMP@.discussions.microsoft.com> wrote in message
> news:0BC394CB-2E61-40F1-BC01-0439622CE994@.microsoft.com...
> >
> > It is registered as SAFE. What do I need to change to make it work? Any
> > help
> > is appreciated. Thanks.
> >
> > "Jens" wrote:
> >
> >> How did you register the code as SAFE, UNSAFE or EXTERNAL_ACCESS ?
> >> Depending on your settings the code isn´t allowed to execute several
> >> things. There are only a few classes which are allowed to execute with
> >> *only* safe permissions.
> >>
> >> HTH; Jens Suessmeyer.
> >>
> >> --
> >> http://www.sqlserver2005.de
> >> --
> >>
> >>
>
>|||Thanks for the link/solution. Is this the only way to do, is there no other
"simpler "option? Thanks.
"Kent Tegels" wrote:
> Hello KMP,
> http://www.sqljunkies.com/WebLog/ktegels/articles/SigningSQLCLRAssemblies.aspx
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>|||I still can't get it work by turning on that option. I get the same error
message that I had listed in my very first posting. Any ideas?
"Kent Tegels" wrote:
> Hello KMP,
> ALTER DATABASE name SET TRUSTWORTHY ON
> But then anything gets to run. Ick.
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>|||Hello KMP,
Oh, that's ugly. Can you post the code throwing the error? I wonder what
you're doing...
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels|||Ok, here is my C# stored procedure. I am trying to read/write an image file
to the the database from the local hard drive. Please let me know what is
wrong. Thanks.
=====================================================using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.IO;
using System.Security.Cryptography;
using System.Security.Permissions;
using System.Globalization;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void LoadImageToDB()
{
// Put your code here
SqlConnection con = new SqlConnection("server=localhost;integrated
security=true;database=RegPerfectDb");
//con.ConnectionString = "Context Connection=true";
SqlClientPermission perm = new
SqlClientPermission(System.Security.Permissions.PermissionState.Unrestricted);
perm.Assert();
SqlDataAdapter da = new SqlDataAdapter("SELECT * FROM DAHS", con);
SqlCommandBuilder commandBuilder = new SqlCommandBuilder(da);
DataSet ds = new DataSet("DAHS");
SqlCommand SqlCmd = new SqlCommand("SELECT * FROM DAHS", con);
int DAHS_ID = 0;
SqlDataReader SqlImgReader;
SqlCmd.CommandType = CommandType.Text;
FileStream fs;
byte[] MyData = null;
con.Open();
SqlImgReader = SqlCmd.ExecuteReader();
if (File.Exists(@."D:\RegPerfect\Bin\Reports\ReportsLogo.bmp"))
{
FileIOPermission filePerm = new
FileIOPermission(FileIOPermissionAccess.Read,
@."D:\RegPerfect\Bin\Reports\ReportsLogo.bmp");
filePerm.Assert();
fs = new
FileStream(@."D:\RegPerfect\Bin\Reports\ReportsLogo.bmp", FileMode.Open,
FileAccess.Read);
MyData = new byte[fs.Length];
fs.Read(MyData, 0, System.Convert.ToInt32(fs.Length));
da.UpdateCommand = new SqlCommand("UPDATE DAHS SET Logo = @.iLOGO
", con);
// Add the Parameters for the UPDATE Command
da.UpdateCommand.Parameters.Add("@.iLOGO", SqlDbType.Image,
(int)fs.Length, "Logo");
fs.Close();
}
else if (File.Exists(@."C:\RegPerfect\Bin\Reports\ReportsLogo.bmp"))
{
fs = new
FileStream(@."C:\RegPerfect\Bin\Reports\ReportsLogo.bmp", FileMode.Open,
FileAccess.Read);
MyData = new byte[fs.Length];
fs.Read(MyData, 0, System.Convert.ToInt32(fs.Length));
da.UpdateCommand = new SqlCommand("UPDATE DAHS SET Logo = @.iLOGO
", con);
// Add the Parameters for the UPDATE Command
da.UpdateCommand.Parameters.Add("@.iLOGO", SqlDbType.Image,
(int)fs.Length, "Logo");
fs.Close();
}
int DAHSIDOrd = SqlImgReader.GetOrdinal("DAHS_Id");
while (SqlImgReader.Read())
{
DAHS_ID = (int)SqlImgReader.GetValue(DAHSIDOrd);
}
da.MissingSchemaAction = MissingSchemaAction.AddWithKey;
da.FillSchema(ds, SchemaType.Source, "DAHS");
da.Fill(ds, "DAHS");
DataTable DAHSTable = ds.Tables["DAHS"];
DataRow myRow;
myRow = DAHSTable.Rows.Find(DAHS_ID);
myRow.BeginEdit();
myRow["Logo"] = MyData;
myRow.EndEdit();
da.Update(ds, "DAHS");
SqlImgReader.Close();
con.Close();
}
};
=====================================================
"Kent Tegels" wrote:
> Hello KMP,
> Oh, that's ugly. Can you post the code throwing the error? I wonder what
> you're doing...
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>|||Thanks a lot Kent. I guess I was trying to do something that was achievable
through a single UPDATE statment. I really really appreciate you help.
"Kent Tegels" wrote:
> Hello KMP,
> I'm not a CAS expert by any stretch of the imagination, but when I reduce
> your code to this:
> [Microsoft.SqlServer.Server.SqlProcedure]
> public static void LoadImageToDB()
> {
> SqlConnection con = new SqlConnection("server=localhost;integrated
> security=true;database=RegPerfectDb");
> //con.ConnectionString = "Context Connection=true";
> SqlClientPermission perm = new SqlClientPermission(System.Security.Permissions.PermissionState.Unrestricted);
> perm.Assert();
> FileStream fs;
> byte[] MyData = null;
> if (File.Exists(@."c:\ssa.dll"))
> {
> FileIOPermission filePerm = new
> FileIOPermission(FileIOPermissionAccess.Read,@."c:\ssa.dll");
> filePerm.Assert();
> fs = new FileStream(@."c:\ssa.dll", FileMode.Open,FileAccess.Read);
> MyData = new byte[fs.Length];
> fs.Read(MyData, 0, System.Convert.ToInt32(fs.Length));
> }
> }
> I get this exception on the filePerm.Asset():
> System.Security.SecurityException: Stack walk modifier must be reverted before
> another modification of the same type can be performed.
> System.Security.SecurityException:
> at System.Security.CodeAccessSecurityEngine.Assert(CodeAccessPermission
> cap, StackCrawlMark& stackMark)
> at System.Security.CodeAccessPermission.Assert()
> at StoredProcedures.LoadImageToDB()
> Is there a particular reason that you are asserting these permissions?
> Also, if you're simply trying to load a image into a database, this might
> be easier:
> create table dbo.images
> (
> img varbinary(max)
> )
> go
> insert into dbo.images
> select * from
> openrowset(bulk 'C:\etc\ktegels\My Pictures\1px.gif'
> ,single_blob) as p
> go
> select * from dbo.images
> go
>
> Any help?
> Thanks,
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>|||Hi Kent,
I was using the UPDATE statement to update the database with the image
stored on the hard drive. I wrote a small sql script that checks for the
existence of a certain drive, for example (pseudocode)-
IF Exists 'D:\'
UPDATE <table> SET <colname> = (SELECT * FROM OPENROWSET(BULK
'D:\test.bmp', SINGLE_BLOB) AS P)
ELSE
UPDATE <table> SET <colname> = (SELECT * FROM OPENROWSET(BULK
'C:\test.bmp', SINGLE_BLOB) AS P)
After the first IF is executed, the else part also gets executed. I am not
sure why this is happening. Can you please help me?
Thanks.
"Kent Tegels" wrote:
> Hello KMP,
> I'm not a CAS expert by any stretch of the imagination, but when I reduce
> your code to this:
> [Microsoft.SqlServer.Server.SqlProcedure]
> public static void LoadImageToDB()
> {
> SqlConnection con = new SqlConnection("server=localhost;integrated
> security=true;database=RegPerfectDb");
> //con.ConnectionString = "Context Connection=true";
> SqlClientPermission perm = new SqlClientPermission(System.Security.Permissions.PermissionState.Unrestricted);
> perm.Assert();
> FileStream fs;
> byte[] MyData = null;
> if (File.Exists(@."c:\ssa.dll"))
> {
> FileIOPermission filePerm = new
> FileIOPermission(FileIOPermissionAccess.Read,@."c:\ssa.dll");
> filePerm.Assert();
> fs = new FileStream(@."c:\ssa.dll", FileMode.Open,FileAccess.Read);
> MyData = new byte[fs.Length];
> fs.Read(MyData, 0, System.Convert.ToInt32(fs.Length));
> }
> }
> I get this exception on the filePerm.Asset():
> System.Security.SecurityException: Stack walk modifier must be reverted before
> another modification of the same type can be performed.
> System.Security.SecurityException:
> at System.Security.CodeAccessSecurityEngine.Assert(CodeAccessPermission
> cap, StackCrawlMark& stackMark)
> at System.Security.CodeAccessPermission.Assert()
> at StoredProcedures.LoadImageToDB()
> Is there a particular reason that you are asserting these permissions?
> Also, if you're simply trying to load a image into a database, this might
> be easier:
> create table dbo.images
> (
> img varbinary(max)
> )
> go
> insert into dbo.images
> select * from
> openrowset(bulk 'C:\etc\ktegels\My Pictures\1px.gif'
> ,single_blob) as p
> go
> select * from dbo.images
> go
>
> Any help?
> Thanks,
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>|||Here you go. It is pretty straight forward piece of code, but both IF and
ELSE parts get executed. Thanks for all the help Kent.
=====================================================
DECLARE
@.OutputPath VARCHAR(100),
@.Result INT
SET NOCOUNT ON
SELECT @.OutputPath = '\Test\Logo.bmp'
SELECT @.OutputPath = '"' + 'dir ' + 'D:' + @.OutputPath + '"'
EXEC @.Result = master..xp_cmdshell @.OutputPath , NO_OUTPUT
--Check the available drives
IF @.Result = 0
UPDATE Test SET Logo = (SELECT * FROM OPENROWSET(BULK 'D:\Test\Logo.bmp',
SINGLE_BLOB) AS P)
ELSE
UPDATE Test SET Logo = (SELECT * FROM OPENROWSET(BULK 'C:\Test\Logo.bmp',
SINGLE_BLOB) AS P)
=====================================================
"Kent Tegels" wrote:
> Hello KMP,
> > IF Exists 'D:\'
> > UPDATE <table> SET <colname> = (SELECT * FROM OPENROWSET(BULK
> > 'D:\test.bmp', SINGLE_BLOB) AS P)
> > ELSE
> > UPDATE <table> SET <colname> = (SELECT * FROM OPENROWSET(BULK
> > 'C:\test.bmp', SINGLE_BLOB) AS P)
> > After the first IF is executed, the else part also gets executed. I am
> > not sure why this is happening. Can you please help me?
> Well, it depends how many times you're executing as part of some other query
> and how the "if exists" works. If nothing else, try wrapping the if body
> and else body in begin end (even though they should be just single statements,
> I know...)
> Could you post the actual code too?
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>|||I put PRINT statements and that is how I know that both IF and ELSE parts are
getting executed. Also in the results pane in Management Studio, I get an
error message saying 'C:\Test\Logo.bmp' path not found (which is correct,
because I expect the IF part to be executed ONLY and not ELSE). Thnx.
"Kent Tegels" wrote:
> Hello KMP,
> Okay, take a look at the estimated execution plan and you'll see that fully
> evaluates if the bulk load is likely to succeed in either cause. I don't
> believe it actually executes both statements, but it does seem to be trying
> to figure out if it can or not, so that's why it looks like it is.
> You can probably see which one is actually getting executed by adding 'print
> true' and 'print false' in begin-end pairs for each condition.
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>|||Sorry, but I don't understand. I ran the same query and my table does not
get updated. So what is the suggested solution to the problem? Thanks.
"Kent Tegels" wrote:
> Hello KMP,
> Right, you're getting the not found not because the bulk read is executing,
> but because the Query Optimizer seems to be heck-bent on determining ahead
> of time if it can be.
> I just ran the query and only the true or false part were actually being
> executed on my laptop...
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>|||Please help, I can't get it to work.
"KMP" wrote:
> Sorry, but I don't understand. I ran the same query and my table does not
> get updated. So what is the suggested solution to the problem? Thanks.
> "Kent Tegels" wrote:
> > Hello KMP,
> >
> > Right, you're getting the not found not because the bulk read is executing,
> > but because the Query Optimizer seems to be heck-bent on determining ahead
> > of time if it can be.
> >
> > I just ran the query and only the true or false part were actually being
> > executed on my laptop...
> >
> > Thank you,
> > Kent Tegels
> > DevelopMentor
> > http://staff.develop.com/ktegels/
> >
> >
> >|||Thanks for all your help, Kent.
"Kent Tegels" wrote:
> Hello KMP,
> > Sorry, but I don't understand. I ran the same query and my table does
> > not get updated. So what is the suggested solution to the problem?
> > Thanks.
> Okay, here's some code that seems to work for me. Note that I'm very paranoid
> about XP_CMDSHELL, so I re-did the test-for-file-existance as a CLRUDF.
> using System;
> using System.Data;
> using System.Data.SqlClient;
> using System.Data.SqlTypes;
> using System.IO;
> public partial class FileFunctions
> {
> public static SqlBoolean FileExists(SqlString FilePath) {
> try { if (File.Exists(FilePath.Value))
> return SqlBoolean.True;
> else
> return SqlBoolean.False;
> } catch (Exception ex) {
> return(SqlBoolean.Null);
> }
> }
> };
> Here's the updated script. One row in the table. :)
> drop table dbo.images
> go
> drop function dbo.FileExists
> go
> drop assembly fileFunctions
> go
> create assembly FileFunctions from 'c:\etc\ktegels\examples\FileExist\FileExist\bin\Debug\FileExist.dll'
> with permission_set=external_access
> go
> create function dbo.FileExists(@.FilePath nvarchar(4000)) returns bit with
> execute as caller as external name FileFunctions.FileFunctions.FileExists
> go
> create table dbo.images(pkid tinyint identity(1,1) primary key,[name] varchar(8),blob
> varbinary(max))
> go
> sp_configure 'clr enabled','1'
> go
> reconfigure
> go
> if dbo.FileExists('c:\one.txt')=1
> begin
> insert into images([name],blob)
> select 'one.txt',BulkColumn from openrowset(bulk 'c:\one.txt',single_blob) p
> end
> else
> begin
> insert into images([name],blob)
> select '2.txt',bulkcolumn from openrowset(bulk 'c:\2.txt',single_blob) p
> end
> go
> select cast(blob as varchar(max)) from dbo.images
> go
> sp_configure 'clr enabled','0'
> go
> reconfigure
> go
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>
>