I tried to create a sp on one of the databases on my lap top and got this: Pls help i need it bad
Msg 10314, Level 16, State 11, Procedure ap_Hello, Line 5
An error occurred in the Microsoft .NET Framework while trying to load
assembly id 65695. The server may be running out of resources, or the
assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or
UNSAFE. Run the query again, or check documentation to see how to solve
the assembly trust issues. For more information about this error:
System.IO.FileLoadException: Could not load file or assembly
'vbtriggers, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' or
one of its dependencies. An error relating to security occurred.
(Exception from HRESULT: 0x8013150A)
CREATE ASSEMBLY ClrSql from 'C:ClrSql.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS
gives me this . . .
Msg 10327, Level 14, State 1, Line 1
CREATE ASSEMBLY for assembly 'ClrSql' failed because assembly 'ClrSql' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission.
How do I allow EXTERNAL_ACCESS and UNSAFE permissions? The property appears grayed out, and I am the dbo.
CREATE ASSEMBLY ClrSql from 'C:ClrSql.dll' WITH PERMISSION_SET = EXTERNAL_ACCESS
After that , I found following error.
CREATE ASSEMBLY for assembly 'Cannla.SqlClr' failed because assembly 'Cannla.SqlClr' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission. If you have restored or attached this database, make sure the database owner is mapped to the correct login on this server. If not, use sp_changedbowner to fix the problem.
i really need urgently help with my deploying my assembly to my database!! can somebody tell me how can i get the authorization to deploy my assembly as permission_set = External_access or Unsafe? i allways get error messages when i try to deploy my assembly that way.
i tried it with strong key and i tried it with asymmetric key like this:
use master go
create asymmetric key WebServiceKey from executable file = '...16082007.dll'
CREATE LOGIN WebServiceLogin FROM ASYMMETRIC KEY WebServiceKey
GRANT EXTERNAL ACCESS ASSEMBLY TO WebServiceLogin GO
than i get the message:
The certificate, asymmetric key, or private key file does not exist or has invalid format.
i have written clr sproc in c#, for use in sql server 2005. the sproc takes a file path and returns file data( size, date modified etc).to do this the sproc needs the permission_set = external_access. to get this i have to sign the dll with a certificate. i created my own root certificate using this command :
when i test though using : signtool verify <my dll>
i get an error stating this : SignTool Error: A certificate chain processed, but terminated in a root certificate which is not trusted by the trust provider.
i thought i followed the instructions correctly. can anyone help me here?
my instructions came from here : http://www.sqljunkies.com/WebLog/ktegels/articles/SigningSQLCLRAssemblies.aspx
I'm trying to create an "unsafe assembly": USE master GO
CREATE ASYMMETRIC KEY StoredProcedures_dll_Key FROM EXECUTABLE FILE = 'C:Documents and SettingsAll UsersDocumentshunterStoredProcedures.dll';
CREATE LOGIN StoredProcedures_dll_Login FROM ASYMMETRIC KEY StoredProcedures_dll_Key;
GRANT EXTERNAL ACCESS ASSEMBLY TO StoredProcedures_dll_Login; GO
USE gfx_sa GO
CREATE ASSEMBLY hunter_storedProcedures FROM 'C:Documents and SettingsAll UsersDocumentshunterStoredProcedures.dll' WITH PERMISSION_SET = UNSAFE; GO
CREATE PROCEDURE [dbo].[hunter_storedProcedure1] ( @symbol_id as int ) AS EXTERNAL NAME hunter_storedProcedures.StoredProcedures.StoredProcedure1 GO
But on 'CREATE ASSEMBLY" clause I'm getting error: Msg 0, Level 11, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded. Msg 0, Level 20, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded.
I created a SQL Server Project in VS 2005, and tried to add a reference to SQLDMO Object dll. But the reference --> add reference, does not allow it. It does not work, even if I set the Assembly Permission Level to UNAFE/EXTERNAL ACCESS.
Reason I'm trying to do this:
We have a SQL 2000 stored procedure that uses SQLDMO using sp_OACreate to BCP files to database. We are converting to 2005 and because of SOX restrictions, I'm trying to replace the sp_OACreate part with external stored procedure written in C#.
According to BOL, I thought atleast UNSAFE should support this, but it seems not.
To set up a managed sproc to act as a WSE 3.0 web service client I had to register the following .Net DLLs as UNSAFE assemblies: System.Configuration.Install.dll System.Web.dll Microsoft.Web.Services3.dll
In order to register UNSAFE assemblies I had to set the database's TRUSTWORTHY property to 'true'.
Can anyone tell me what are the implications of doing this?
I think I have some kind of permission problem. But first things first:
I have code which I would like to run in SQL Server (CLR Integration). First thing is that my code uses third-party-dll. I have to deploy my code as unsafe because of
"
CREATE ASSEMBLY failed because method "add_FunctionAdd" on type "USP.Express.Pro.FunctionsCollection" in safe assembly "USP.Express.Pro.2.0" has a synchronized attribute. Explicit synchronization is not allowed in safe assemblies. "
Of course I can not create "asymmetric key" for third-party-dll (Or can I?).
So, I tried to use trustworthy DB. But I get all the time error Msg 10327: "Assembly is not authorised for PERMISSION_SET=UNSAFE"
I am using Windows Login to log on Sql Server. Login is granted "Unsafe assembly" and DB has trustworthy setting "on".
Login has server roles "sysadmin" and "securityadmin". Login is mapped with DB User who has same name ( DOMAINUserName ) and has default schema "dbo". Login has DB memberships "db_owner" and "db_securityadmin". DB user owns schemas "db_owner" and "db_securityadmin".
Am I missing something?
Interesting thing is that I can do deployment (as unsafe assembly) in master-database. But not in the other databases.
Questions are: - Is there other way to authorise third-party-dll than using trustworthy? - Why deployment can be done in master-database?
And finally: - Why deployment can not be done in other database?
I develop a database that notifies clients when data changes by sending an UDP broadcast message using an extended stored procedure. Now I want to use a CLR stored procedure to send the UDP broadcast instead:
using System;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Net.Sockets;
public partial class UserDefinedFunctions
{
[SqlProcedure]
public static void UdpSend(SqlString address, SqlInt32 port, SqlString message)
{
System.Net.Sockets.UdpClient client = new System.Net.Sockets.UdpClient();
I have found that to be allowed to send to 255.255.255.255 I must give the assembly permission set 'Unsafe'. If I change to 'External access' I get:
Msg 6522, Level 16, State 1, Procedure UdpSend, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'UdpSend':
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.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.CodeAccessPermission.Demand()
at System.Net.Sockets.Socket.CheckSetOptionPermissions(SocketOptionLevel optionLevel, SocketOptionName optionName)
at System.Net.Sockets.UdpClient.CheckForBroadcast(IPAddress ipAddress)
at System.Net.Sockets.UdpClient.Send(Byte[] dgram, Int32 bytes, String hostname, Int32 port)
at UserDefinedFunctions.UdpSend(SqlString address, SqlInt32 port, SqlString message)
I cannot use permission set 'Unsafe' in production environment, so what I want is to customize the effective permissions with higher resoloution than the three pre-defined permission sets 'Safe', 'External access' and 'Unsafe'. Except from what is allowed by 'Safe' I only want the permissions necessary to send an UDP broadcast.
I have to make a gateway to access third-party closed-source native-code COM objects. As an interface, SQL Server stored procedures are absolutely perfect for my needs. I decided to use SQL Server 2005 CLR rather than using deprecated extended stored procedures or accessing COM objects directly through SQL Server. (This decision is aided by the fact that the third party has plans to make a .NET version of their COM objects "really soon now" (i.e.: not soon enough). Backporting their new interface to my abstraction layer will be very simple.)
I'm having problems using these COM objects from my SQL Server 2005 CLR stored procedure. When I try to run my stored procedure, I get the error below. It appears that SQL Server/CLR refuses to perform the disk access necessary to load the COM object from disk so that it can instantiate it. (My message continues after the error.)
Msg 6522, Level 16, State 1, Procedure MyStoredProcedure, Line 0 A .NET Framework error occurred during execution of user defined routine or aggregate 'MyStoredProcedure': System.IO.FileLoadException: LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by the host. System.IO.FileLoadException: at System.Reflection.Assembly.nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection) at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection) at System.Reflection.Assembly.InternalLoadFrom(String assemblyFile, Evidence securityEvidence, Byte[] hashValue, AssemblyHashAlgorithm hashAlgorithm, Boolean forIntrospection, StackCrawlMark& stackMark) at System.Reflection.Assembly.LoadFrom(String assemblyFile, Evidence securityEvidence, Byte[] hashValue, AssemblyHashAlgorithm hashAlgorithm) at System.Activator.CreateComInstanceFrom(String assemblyName, String typeName, Byte[] hashValue, AssemblyHashAlgorithm hashAlgorithm) at System.Activator.CreateComInstanceFrom(String assemblyName, String typeName) at UserDefinedFunctions.MyStoredProcedure() .
I'll outline the steps I performed so far to get the assembly working: Ensured I was running at SQL Server 2005 compatibility level. (90) - Installed and configured the third-party COM objects on my devel box and my DB box. They registered themselves properly. - Enabled CLR in the DB. - Made sure the DB owner has UNSAFE ASSEMBLY permission. - Added the TRUSTWORTHY property to the DB. (Yes, this isn't the "correct" way to do it, but I didn't want to deal with code-signing voodoo. Apologies to actual believers in voodoo.) - Created an Unsafe assembly "MyAssembly" in SQL Server 2005 and Visual Studio 2005. - Created all the necessary Interop assemblies (by creating a normal (non-SQL) C# project and building my code, and raiding the bin directory). - Added these Interop assemblies to SQL Server 2005 as Unsafe assemblies. - In VS2005, built my SQL Project code and deployed successfully. - Tried running my stored procedure through a Query window, and I got the error above.
I've done many searches. All of the results I've found concern themselves with Web Services and XML serialization, and suggest using sgen.exe. I don't think they're relevant to my problem. A few said I should add my COM DLLs as assemblies to SQL Server, but that is impossible for native code DLLs. I found something that hinted at tblexp.exe, but I didn't understand how that could help me. I already figured out a way to get me the Interop libs.
Efforts at solving: - Reading webpages for hours on end. - I tried using sgen.exe to create the XML serialization assembly, but it didn't help. - I tried adding various native-code DLLs directly as SQL Server assemblies, but you can bet it didn't like that. - A few other pitiful attempts not worth mentioning. - Reading this forum. I didn't see anything that applied. - Posting here.
I really wish Microsoft had meant what they said when they stated that "Unsafe" assemblies can access anything, instead of trying to protect developers from their folly.
Any insights on getting those COM objects to work in my CLR stored procedure? Thank you.
Can someone verify that an assembly containing an interface with an event definition, such as...
public interface A { event EventHandler Foo; }
... can never be loaded under SAFE or EXTERNAL_ACCESS ?
It appears that the compiler-generated add_xxx and remove_xxx have the MethodImpl(MethodImplOptions.Synchronized) attribute defined by default, and "Explicit synchronization is not allowed". The same limitation also applies to classes by default, although technically one is able to define the implementation directly (clearly not ideal).
I have implemented User-defined Functions in SQL Server 2005 with Managed Code. Inside Vistual Studio Project for SQL Server, there are three different levels of security, which are SAFE, EXTERNAL_ACCESS and UNSAFE. When I set EXTERNAL_ACCESS permission and try to send http web request using HttpWebRequest and HttpWebResponse classes in the .NET Framework, it throws me error message, but If I set to UNSAFE, it works fine. For that I have two Questions.
1) how to run my code with EXTERNAL_ACCESS permission ? 2) If I set UNSAFE permission, is it dangerous for any security issue ?
Looking forward to some help from you guys. Thanks in advance.
