Shamil Salakhetdinov
shamil at smsconsulting.spb.ru
Thu Jun 24 05:30:48 CDT 2010
Hi All -- Setting database trustworthy happened to be easy: ALTER DATABASE [myDb] MaxPreps_v2 SET TRUSTWORTHY ON dbo But I still can't find how to set the database owner (DBO) having EXTERNAL ACCESS ASSEMBLY permission - missing something simple here? Thank you. -- Shamil -----Original Message----- From: dba-vb-bounces at databaseadvisors.com [mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of Shamil Salakhetdinov Sent: Thursday, June 24, 2010 1:11 PM To: 'Discussion concerning Visual Basic and related programming issues.' Subject: Re: [dba-VB] Deploying MSSQL2008 C#/VB.NET UDF'sAssemblywith PermissionlLevel = External or Unsafe Hello Gustav -- No, I didn't try to use a certificate or an asymmetric key... - I just wanted to have that "UDF with pInvoke" task done ASAP, and using certificate or asymmetric key will need time (and money) I can't afford to invest now... Well, it may happen that learning how to/applying "straightforward" usage of "EXTERNAL ACCESS ASSEMBLY permission or UNSAFE ASSEMBLY permission for DBO, and TRUSTWORTHY database property set to 'On'" will take more time than investigating certificates and asymmetric keys, still hope it will not... I'm looking through this article now: http://www.devx.com/codemag/Article/31193/1763/page/2 and this one http://www.codeproject.com/KB/database/SQLCLRIntegrationSecurity.aspx and I'm hoping they have all the information I currently need to have the things here ready ASAP... Thank you. -- Shamil -----Original Message----- From: dba-vb-bounces at databaseadvisors.com [mailto:dba-vb-bounces at databaseadvisors.com] On Behalf Of Gustav Brock Sent: Thursday, June 24, 2010 12:12 PM To: dba-vb at databaseadvisors.com Subject: Re: [dba-VB] Deploying MSSQL2008 C#/VB.NET UDF's Assemblywith 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 _______________________________________________ dba-VB mailing list dba-VB at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-vb http://www.databaseadvisors.com _______________________________________________ dba-VB mailing list dba-VB at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-vb http://www.databaseadvisors.com