[dba-VB] Deploying MSSQL2008 C#/VB.NET UDF's Assembly with PermissionlLevel = External or Unsafe

Hi Shamil

I don't work with SQL Server at this level but - stupid question perhaps - did you try this option:

<quote>
.. the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission.
</quote>

Yes I know, handling and implementing certificates is boring and can be troublesome (and expensive if public certificates are needed) and - don't forget - may require renewal, but once set up it works reliably.

/gustav


>>> shamil at smsconsulting.spb.ru 24-06-2010 09:52 >>>
Hi All --

I have to develop a set of MSSQL2008 C#/VB.NET UDFs using windows DLLs calls
(pInvoke). And when pInvoke is used in C#/VB.NET UDFs then UDFs' assembly
should be marked as External or Unsafe. But when I'm marking UDFs' assembly
as External or Unsafe I'm getting the following error messages on their
deployment using VB2008:

Permission Level = External

CREATE ASSEMBLY for assembly 'TestUDF.SqlServerProject' failed 
because assembly 'TestUDF.SqlServerProject' 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.	

Permission Level = Unsafe

CREATE ASSEMBLY for assembly 'Racing.SqlServerProject' failed 
because assembly 'Racing.SqlServerProject' is not authorized 
for PERMISSION_SET = UNSAFE.  The assembly is authorized 
when either of the following is true: the database owner (DBO) 
has UNSAFE 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 UNSAFE ASSEMBLY permission.

My question is: Do you have experience in developing C#/VB.NET UDFs with
pInvoke calls? How did you then set EXTERNAL ACCESS ASSEMBLY permission or
UNSAFE ASSEMBLY permission for DBO, and TRUSTWORTHY database property to
'On'? I can't find how to do that "straightforward way" using T-SQL
scripts...

And I see TRUSTWORTHY database property is disabled when I'm looking at
database properties window.

Thank you.

-- Shamil