Discussion:
CLR Stored Procedure gives "Failed to grant permission to execute"
(too old to reply)
JohnD
2007-06-14 11:41:03 UTC
Permalink
Hello All,

I'm new to all SQL so please forgive me if I've missed something obvious.

With SQL 2005.

I have a C# stored procedure which works fine.

I then modified the C# code to call into an unmanaged (C++) DLL. I've set
this up to use PInvoke to get into the unmanaged DLL (which is called
cpp2.dll). However when I try to run this new Stored Procedure I get:

An error occured: Could not load file or assembly 'cpp2, Version=0.0.0.0,
Culture=neutral, PublicKeyToken=null' or one of its dependencies. Failed to
grant permission to execute. (Exception from HRESULT: 0x80131418).

The DLL contains a single function that just returns a character string so
should have no dependencies other than the VS2005 runtime dlls, which are
installed on the machine.

What do I need to configure to allow SQL 2005 to execute this DLL?

This is a cut-down version of a real example which has the C# calling into a
C++ mixed managed/unmanaged dll, where I get the same message.

Thanks,

John.

Code follows:

C#:

using System;
using System.Data.Sql;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Runtime.InteropServices;

namespace DotNetFun.SqlServer
{
public class SProc
{

[DllImport("user32.dll", CharSet = CharSet.Auto)]
public extern static int MessageBox(IntPtr hWnd, String text, String
caption, uint type);

[DllImport("cpp2.dll", CharSet = CharSet.Auto)]
public extern static String CPPTest();

public static int PrintMessage(String Message)
{
//MessageBox((IntPtr)0,Message,Message,0);
int i = 0;
try
{
//SqlContext.Pipe.Send("c#:"+Message);
SqlContext.Pipe.Send("c#:"+CPPTest());
}
catch (Exception err)
{
i = 1;
SqlContext.Pipe.Send("An error occured: " + err.Message);
}
return i;
}
}
}
~

C++:

typedef wchar_t * wp;

extern "C" wp __declspec(dllexport) __stdcall CPPTest(void)
{
return L"CPP.DLL";
}
~
~
Erland Sommarskog
2007-06-14 22:12:54 UTC
Permalink
Post by JohnD
I then modified the C# code to call into an unmanaged (C++) DLL. I've set
this up to use PInvoke to get into the unmanaged DLL (which is called
An error occured: Could not load file or assembly 'cpp2,
Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its
dependencies. Failed to grant permission to execute. (Exception from
HRESULT: 0x80131418).
The DLL contains a single function that just returns a character string so
should have no dependencies other than the VS2005 runtime dlls, which are
installed on the machine.
What do I need to configure to allow SQL 2005 to execute this DLL?
You need to use WITH PERMISSION_SET = UNSAFE when you create the assembly.
Note that this requires you to have to sysadmin rights on the server.
--
Erland Sommarskog, SQL Server MVP, ***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
JohnD
2007-06-15 07:46:59 UTC
Permalink
Hello Erland,

Sorry, I should have said. I've already done that.

/john
Post by Erland Sommarskog
Post by JohnD
I then modified the C# code to call into an unmanaged (C++) DLL. I've set
this up to use PInvoke to get into the unmanaged DLL (which is called
An error occured: Could not load file or assembly 'cpp2,
Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its
dependencies. Failed to grant permission to execute. (Exception from
HRESULT: 0x80131418).
The DLL contains a single function that just returns a character string so
should have no dependencies other than the VS2005 runtime dlls, which are
installed on the machine.
What do I need to configure to allow SQL 2005 to execute this DLL?
You need to use WITH PERMISSION_SET = UNSAFE when you create the assembly.
Note that this requires you to have to sysadmin rights on the server.
--
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Erland Sommarskog
2007-06-15 22:20:47 UTC
Permalink
Post by JohnD
Sorry, I should have said. I've already done that.
I was able to get your repro to work. Beside giving the assembly unsafe
permissions, I also had to make the database trustworthy. (I could also
have given the assembly a strong name or signed it. But making the
database trustworthy was the easiest way out.) This I had to do, to get
CREATE ASSEMBLY to work, so I guess you've gone past that stage as well.

When I first tried to run the procedure, I got an error message (except
that it had severity 0, so it was presented as a plain message), that
CPP2.DLL was not be found. But when I copied the DLL to the BINN directory
of SQL Server, things worked. (Well, in the second attempt. A 32-bit DLL
does not run on a 64-bit server.)

Your message could indicate that SQL Server finds the DLL, but does not
have permission to read it. But the errors I got, only related to the DLL,
and your refer to the assembly. So I would rather look at the assembly as
such. If you did not set the database to be trustworthy, you could try
that.
--
Erland Sommarskog, SQL Server MVP, ***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
JohnD
2007-06-18 13:48:18 UTC
Permalink
Hello Erland,

Thanks for your help so far.
Post by Erland Sommarskog
I was able to get your repro to work. Beside giving the assembly unsafe
permissions, I also had to make the database trustworthy. (I could also
have given the assembly a strong name or signed it. But making the
database trustworthy was the easiest way out.) This I had to do, to get
CREATE ASSEMBLY to work, so I guess you've gone past that stage as well.
The database was already set to trustworthy. As you say, if it is not the
UNSAFE permission fails on the CREATE ASSEMBLY. I confirmed this by toggling
trustworth off and then back on again.
Post by Erland Sommarskog
When I first tried to run the procedure, I got an error message (except
that it had severity 0, so it was presented as a plain message), that
CPP2.DLL was not be found. But when I copied the DLL to the BINN directory
of SQL Server, things worked. (Well, in the second attempt. A 32-bit DLL
does not run on a 64-bit server.)
I do get a "plain" error message on cpp2.dll, at least its in black text,
not the red text that I get for managed errors. It definately says "Failed
to grant permission to execute" NOT that it could not find the DLL. I've
also tried copying the DLL to the Binn folder, but that made no difference.
Post by Erland Sommarskog
Your message could indicate that SQL Server finds the DLL, but does not
have permission to read it. But the errors I got, only related to the DLL,
and your refer to the assembly. So I would rather look at the assembly as
such. If you did not set the database to be trustworthy, you could try
that.
The messages I get refer to the unmanaged CPP dll, not the managed assembly.
A second assembly located in the same folder that does not use pInvoke does
run succesfully, so I still think that I have a problem with CPP2.DLL. I've
used the .Net configuration tool to give the managed DLL "full trust" on the
machine (I cannot apply "full trust" to the unmanaged CPP2.DLL), but it
still cannot get permission to execute CPP2.DLL.


/john
Post by Erland Sommarskog
Post by JohnD
Sorry, I should have said. I've already done that.
I was able to get your repro to work. Beside giving the assembly unsafe
permissions, I also had to make the database trustworthy. (I could also
have given the assembly a strong name or signed it. But making the
database trustworthy was the easiest way out.) This I had to do, to get
CREATE ASSEMBLY to work, so I guess you've gone past that stage as well.
When I first tried to run the procedure, I got an error message (except
that it had severity 0, so it was presented as a plain message), that
CPP2.DLL was not be found. But when I copied the DLL to the BINN directory
of SQL Server, things worked. (Well, in the second attempt. A 32-bit DLL
does not run on a 64-bit server.)
Your message could indicate that SQL Server finds the DLL, but does not
have permission to read it. But the errors I got, only related to the DLL,
and your refer to the assembly. So I would rather look at the assembly as
such. If you did not set the database to be trustworthy, you could try
that.
--
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Erland Sommarskog
2007-06-18 22:06:33 UTC
Permalink
Post by JohnD
I do get a "plain" error message on cpp2.dll, at least its in black
text, not the red text that I get for managed errors. It definately
says "Failed to grant permission to execute" NOT that it could not find
the DLL. I've also tried copying the DLL to the Binn folder, but that
made no difference.
Hm, isn't there something that looks funny to this message:

An error occured: Could not load file or assembly 'cpp2,
Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its
dependencies. Failed to grant permission to execute. (Exception from
HRESULT: 0x80131418).

Maybe I only show how ignorant I am about .Net and such things, but
why does the message talk about an assembly, when it's old-fashioned
native DLL?

I made an experiment where I denied the service account for SQL Server
access to the DLL. This was the result:

An error occured: Unable to load DLL 'cpp2.dll': Access is denied.
(Exception from HRESULT: 0x80070005 (E_ACCESSDENIED))

Quite different.

I assume that you compiled both the C# assembly and the C++ DLL from
Visual Studio? I compiled both from the command line. I would suggest
that you try that.
--
Erland Sommarskog, SQL Server MVP, ***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Erland Sommarskog
2007-06-19 22:34:00 UTC
Permalink
cl.exe /LD cpp2.cpp
csc /t:library cs.cs /r:"C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Binn\sqlaccess.dll"
sqlaccess.dll? That was dropped in CTP 11 or so. I'm surprised to see
that that DLL is still around. You can remove the /r option for a
nicer command-line experience. But it does not seem to have anything
to do with the issue. I recompiled the assembly, and it still worked
for me.
If you are willing and able, I've attached my two DLLS (zipped) to this
email. It would be great if you could try these binaries on your box - to
see if its a build or a deployment issue.
I got this error:

An error occured: An attempt was made to load a program with an incorrect
format. (Exception from HRESULT: 0x8007000B)

I'm running a 64-bit version of SQL Server, that's why.

But, hey, I have a virtual machine or two with 32-bit SQL Server. I
could try those, but not tonight. I need to crawl off to bed.
--
Erland Sommarskog, SQL Server MVP, ***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Erland Sommarskog
2007-06-20 20:14:38 UTC
Permalink
Post by Erland Sommarskog
But, hey, I have a virtual machine or two with 32-bit SQL Server. I
could try those, but not tonight. I need to crawl off to bed.
OK, so I tried your DLLs on a wirtual Win2000 machine with SQL 2000 SP1,
they worked out of the box. I was logged in as sysadmin when I tried it.

I'm afraid that I'm out of ideas why it does not work on your machine.
--
Erland Sommarskog, SQL Server MVP, ***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
JohnD
2007-06-21 07:29:29 UTC
Permalink
OK Erland thanks. I really appreciate all the time you've spent. If I get a
resolution I'll post it here.

/john
Post by Erland Sommarskog
Post by Erland Sommarskog
But, hey, I have a virtual machine or two with 32-bit SQL Server. I
could try those, but not tonight. I need to crawl off to bed.
OK, so I tried your DLLs on a wirtual Win2000 machine with SQL 2000 SP1,
they worked out of the box. I was logged in as sysadmin when I tried it.
I'm afraid that I'm out of ideas why it does not work on your machine.
--
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
JohnD
2007-06-21 11:48:16 UTC
Permalink
Ah Ha,

OK, good news and bad news.

It turns out that my system has a number of directories:

C:\program files\Microsoft SQL Server\MSSQL\MSSQL\Binn
C:\program files\Microsoft SQL Server\MSSQL.1\MSSQL\Binn
C:\program files\Microsoft SQL Server\MSSQL.2\MSSQL\Binn
C:\program files\Microsoft SQL Server\MSSQL.3\MSSQL\Binn

And I guess was copying cpp.dll into the wrong directory. Once I've sorted
that out I can run the UNMANAGED cpp.dll fine. Brilliant.

But,

If I add some managed code to the cpp dll:

#using <mscorlib.dll>
using namespace System;
using namespace System::Reflection;
using namespace System::Runtime::CompilerServices;

[assembly:AssemblyKeyFile("keyfile.snk")];

extern "C" wp __declspec(dllexport) __stdcall CPPTest(void)
{
return L"CPP.DLL";
}

String *dummy_call(void)
{
return new String("dummy_call");
}

and compile with the /clr option I get the error message again.

In fact if I remove the error handling from the C# code I get a more helpful
message:

Msg 6522, Level 16, State 1, Procedure csfn, Line 0
A .NET Framework error occurred during execution of user defined routine or
aggregate 'csfn':
System.IO.FileLoadException: Could not load file or assembly 'cpp,
Version=0.0.0.0, Culture=neutral, PublicKeyToken=eb5ebc232de94dcf' or one of
its dependencies. Failed to grant permission to execute. (Exception from
HRESULT: 0x80131418) ---> System.Security.Policy.PolicyException: Execution
permission cannot be acquired.
System.Security.Policy.PolicyException:
at System.Security.SecurityManager.ResolvePolicy(Evidence evidence,
PermissionSet reqdPset, PermissionSet optPset, PermissionSet denyPset,
PermissionSet& denied, Boolean checkExecutionPermission)
at System.Security.SecurityManager.ResolvePolicy(Evidence evidence,
PermissionSet reqdPset, PermissionSet optPset, PermissionSet denyPset,
PermissionSet& denied, Int32& securitySpecialFlags, Boolean
checkExecutionPermission)
System.IO.FileLoadException:
at DotNetFun.SqlServer.SProc.CPPTest()
at DotNetFun.SqlServer.SProc.PrintMessage(String Message)


Again, I've copied cpp.dll into the correct folder, added it to the GAC, use
the .Net configurator tool to give it "full trust". I dunno what else I can
do to get the thing running.

Any further ideas anyone?

/john.

~
Post by Erland Sommarskog
Post by Erland Sommarskog
But, hey, I have a virtual machine or two with 32-bit SQL Server. I
could try those, but not tonight. I need to crawl off to bed.
OK, so I tried your DLLs on a wirtual Win2000 machine with SQL 2000 SP1,
they worked out of the box. I was logged in as sysadmin when I tried it.
I'm afraid that I'm out of ideas why it does not work on your machine.
--
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Erland Sommarskog
2007-06-22 14:02:32 UTC
Permalink
Post by JohnD
Again, I've copied cpp.dll into the correct folder, added it to the GAC,
use the .Net configurator tool to give it "full trust". I dunno what
else I can do to get the thing running.
This definitely goes over my head, since I don't know how to add things
to the GAC or what the .Net configurator is. On top of that, the C++
code you posted resulted in a couple of compilation errors.

The best I can do is to alert my MVP colleagues, and see if anyone else
have the time to look at this.
--
Erland Sommarskog, SQL Server MVP, ***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
JohnD
2007-06-22 14:12:36 UTC
Permalink
Thanks again Erland.

(The code is missing "typedef wchar_t *wp;" at the top)
Post by Erland Sommarskog
Post by JohnD
Again, I've copied cpp.dll into the correct folder, added it to the GAC,
use the .Net configurator tool to give it "full trust". I dunno what
else I can do to get the thing running.
This definitely goes over my head, since I don't know how to add things
to the GAC or what the .Net configurator is. On top of that, the C++
code you posted resulted in a couple of compilation errors.
The best I can do is to alert my MVP colleagues, and see if anyone else
have the time to look at this.
--
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Erland Sommarskog
2007-06-22 22:14:28 UTC
Permalink
Post by JohnD
Thanks again Erland.
(The code is missing "typedef wchar_t *wp;" at the top)
With some more tweaks I was able to compile the code, and I got the same
error as you, although I skipped the GAC and the .Net Configurator.

But I think I know what the problem is. SQL Server does only permit a
limited set of the .Net Framework to be used. For instance, you cannot
refer to System.Drawing in an assemnbly you load into SQL Server. It
appears logical then that SQL Server would not permit a completely
unknown assembly to be loaded.

The workaround is apparent: load your assembly into SQL Server with
CREATE ASSEMBLY. (This works for System.Drawing too.)

There is some discussion about this in Books Online, in the section
CLR Integration Security, see
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/denet9/html/05d7a471-c5d5-4730-b903-
e4edc8157bb4.htm.
--
Erland Sommarskog, SQL Server MVP, ***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Continue reading on narkive:
Loading...