Attach database loses CLR
Hi
When I deploy a database (mdf and ldf) and attch the database I get errors when my stored procedures attempt to call CLR stored procs.
Error regards database trust and assembly safety etc. This works on my development system/database so it's setup correclty there.
Clearly I'm missing some additional setup when attaching the database to another server instance. Can't figure it out though. I'm marking the assembly UNSAFE (it requires network access) and the database as trustworthy when attaching but the error persists. Also making sure the server instance has CLR enabled.
What am I missing? Any ideas?
[644 byte] By [
Dreedle] at [2007-12-25]
hi,
AFAIK, you only need to enable CLR integration (SQL Server Area Configuration for features) and obviously enable security settings for the db as
ALTER DATABASE [theDB] SET TRUSTWORTHY ON; GO CREATE ASSEMBLY [assemblyName] AUTHORIZATION owner_name FROM 'c:\..\full_path_to_the_dll.Dll' WITH PERMISSION_SET = EXTERNAL_ACCESS; GO CREATE PROCEDURE [schema].[procedure] @param .... AS EXTERNAL NAME [...].[...].[...]; GO
as you already did... are you dealing a "traditional" scenario or User Instances?
regards
Andrea,
Traditional, user instances don't suit the application.
When attaching the DB to another server instance I set the database trust (as per your example) but I don't recreate the assembly or the CLR stored procs - aren't they already in the DB?
I'm thinking there must be something I have to set up in master DB because the server instance I'm attaching to has no previous knowledge of the CLR requirements of my DB? Shot in the dark.
Regards
Allan
Mat,
The error is:
"An error occurred in the Microsoft .NET Framework while trying to load assembly id 66533. 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: "
followed by a stack trace. The stack trace just shows assembly load failure. Exception hresult is 0x80FC0B59 - which does not seem to be a standard hresult.
The database has 3 assemblies. One is EXTERNAL_ACCESS, one is UNSAFE and the third is SAFE.
Assembly 66533 is the external access assembly, which uses the unsafe assembly. The unsafe assembly is never called directly. The safe assembly is fine in all cases.
The error occurs when the MDF & LDF files of the database are installed on the the server and attached to the local SQL Server instance and the stored procs exposed by the assembly are called.
When attaching the database I set it trustworthy on. Makes no difference. Granting external access and unsafe to the login doesn't make any difference either.
Seems that attaching the database to another instance really screws up external access and unsafe assemblies. Detaching and attaching within the same server instance is fine. Which leads me to believe that there is an additional step required when attaching to another instance. I'm not enough of an sql server admin to figure out what though.
