External access assembly permissions denied while deploying CLR assembly

Question

Post reply

External access assembly permissions denied while deploying CLR assembly

ravithegreat

Old Hand

Points: 370
More actions
December 29, 2008 at 1:33 am

#195525

Hi,
I am currently working on a SQL CLR assembly. When I try to deploy the assembly into my server I get the following error message
Error 12 EXTERNAL ACCESS ASSEMBLY permission was denied on object 'server', database 'master'.
I have set the trustworthy property of the database to 'True' and also assigned the user to the role of db_owner but still doesn't work. Then I gave the permissions to the login using the statement
'GRANT EXTERNAL ACCESS ASSEMBLY TO [loginname]' but no avail.
Any help would be appriciated.
Thanks,
Ravi

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Carolyn Richardson SSCrazy Eights Points: 8357 More actions · Answer 1

Have you altered the database properties to set trustworthy on?

ALTER DATABASE Databasename SET TRUSTWORTHY ON;

From BOL

Because a database that is attached to an instance of SQL Server cannot be immediately trusted, the database is not allowed to access resources beyond the scope of the database until the database is explicitly marked trustworthy. Also, modules that are designed to access resources outside the database, and assemblies with either the EXTERNAL_ACCESS and UNSAFE permission setting, have additional requirements in order to run successfully.

Facts are stubborn things, but statistics are more pliable - Mark Twain
Carolyn
SQLServerSpecialists[/url]

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 2

Why are you trying to create the assembly in the master database? Is this really what you intended to to do? I ask because master is owned by sa and sa would have the permissions to create the assembly. Is your SQL Server in Windows only Authentication mode?

ravithegreat Old Hand Points: 370 More actions · Answer 3

Hi,

Here are the answers to both of your questions.

I have used the ALTER DATABASE property to set the trustworthy property on.

Also, I believe External Assembly Access permission is given to a login and that has to be done on the master database(I tried doing it to the user in our test database and it threw me an error saying permissions at server scope should be given to the master database). I am trying to install my assembly on one of our test databases.

Thanks,

Ravi

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 4

Please post the exact code that you ran, and the exact error message that you received back.

matsinopoulos Ten Centuries Points: 1049 More actions · Answer 5

[font="Comic Sans MS"]

Hi,

I have come across this problem myself in the past, and I found the solution here:

http://www.sqljunkies.com/WebLog/ktegels/articles/SigningSQLCLRAssemblies.aspx

Generally, the assembly you are trying to deploy needs to be signed with the a certificate that is linked to a trusted login.

If you need more help let me know.

BR

Panayotis Matsinopoulos

[/font]

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 6

matsinopoulos (1/4/2009)
[font="Comic Sans MS"]
Hi,
I have come across this problem myself in the past, and I found the solution here:
http://www.sqljunkies.com/WebLog/ktegels/articles/SigningSQLCLRAssemblies.aspx
Generally, the assembly you are trying to deploy needs to be signed with the a certificate that is linked to a trusted login.
If you need more help let me know.
BR
Panayotis Matsinopoulos
[/font]

That will only work for your own assemblies. If you are trying to add an unapproved assembly from the .NET BCL to SQL, it doesn't work out the same way.

matsinopoulos Ten Centuries Points: 1049 More actions · Answer 7

[font="Comic Sans MS"]

Hi Jonathan,

Can you please clarify this please? Is the ravithegreat case/question a case of an unapproved .NET BCL (Base Class Library) assembly?

Thanks in advance

Panayotis Matsinopoulos

[/font]

ravithegreat Old Hand Points: 370 More actions · Answer 8

Hi,

1. This is my own assembly and not an assembly from the BCL.

2. The same code was working previously with the permissions as I have mentioned above. It suddenly stopped working. Even a helloworld SP does not work.'

3. As far as the trusted certificate approach goes I do not want to use it since, even earlier without using this approach I was able to deploy. What I am looking for is the specific permission which I need to provide for this to work as earlier.

Thanks for the responses though.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 9

From what you have posted, I can only guess what your problem might be. It looks like you are doing the correct thing, but you haven't posted enough information like the complete code set that you ran. Did you restore this database by chance from a backup? If you did have a look at this blog post where I cover how to resolve safety problems after restoring the database:

http://jmkehayias.blogspot.com/2008/03/clr-safety-issues-after-database.html