none
assembly '' failed because assembly '' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS RRS feed

  • Question

  • Hi

    I seem to be asking a question here that has been regularly asked before.

    I have created a stored proc clr in visual studio 2012, the proc references a service using a url that returns some xml, below is the code

    SqlDataRecord rec = new SqlDataRecord(cols);
    
            ArrayList AddressLookup = new ArrayList();
            string url = "http://the url";
            byte[] data;
            using (WebClient webClient = new WebClient())
            {
                webClient.Proxy = WebRequest.GetSystemWebProxy();
                webClient.Proxy.Credentials = System.Net.CredentialCache.DefaultNetworkCredentials;
                data = webClient.DownloadData(url);
            }
            
    
            string str = Encoding.GetEncoding("Windows-1252").GetString(data);
            XDocument xd = XDocument.Parse(str);
            pipe.SendResultsStart(rec);
            foreach (KeyValuePair<string, int> pair in dictionary)
            {
                foreach (XElement element in xd.Descendants(pair.Key))
                {
                    rec.SetSqlString(0, new SqlString(pair.Key));
                    rec.SetSqlString(1, new SqlString(element.Value));
                    pipe.SendResultsRow(rec);
                }
    
            }
            pipe.SendResultsEnd();

    this code works fine as a part of a console project when putting it a stored proc clr and then running the clr from sql server i get the error

    (87,1): SQL72014: .Net SqlClient Data Provider: Msg 10327, Level 14, State 1, Line 1 CREATE ASSEMBLY for assembly '' failed because assembly '' 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.
    An error occurred while the batch was being executed.

    The general solution seems to be to set the permissions to external access (i have done this in the visual studio solution properties) and use

    ALTER DATABASE somedatabase SET TRUSTWORTHY ON; 

    however this doesnt seem to work for me or perhaps im not doing it in the right place

    Im running the above code in management studio

    can anyone help please

    Thursday, October 4, 2012 12:29 PM

Answers

  • I'm assuming that the error CREATE ASSEMBLY for assembly '' failed because assembly '' is not authorized for... refers to a real assembly name.

    Also that "ALTER DATABASE somedatabase SET TRUSTWORTHY ON;" refers to the database in which you are deploying the assembly. Target database in SSDT tools.

    After you get the error, you can check and see if trustworthy is indeed on by issuing "select name, is_trustworthy_on from sys.databases where name = 'somedatabase'  ".

    Also, you can try manually registering your assembly (rather then deploying with SSDT) with "CREATE ASSEMBLY ... from 'filename.dll' with permission_set = external_access". If this works, there may be a problem with the settings in your SSDT project and it might be better to repost on the SSDT forum (http://social.msdn.microsoft.com/Forums/en-US/ssdt/threads).

    Cheers, Bob

    • Marked as answer by ez44 Friday, October 5, 2012 10:17 AM
    Thursday, October 4, 2012 7:02 PM
    Moderator

All replies

  • I'm assuming that the error CREATE ASSEMBLY for assembly '' failed because assembly '' is not authorized for... refers to a real assembly name.

    Also that "ALTER DATABASE somedatabase SET TRUSTWORTHY ON;" refers to the database in which you are deploying the assembly. Target database in SSDT tools.

    After you get the error, you can check and see if trustworthy is indeed on by issuing "select name, is_trustworthy_on from sys.databases where name = 'somedatabase'  ".

    Also, you can try manually registering your assembly (rather then deploying with SSDT) with "CREATE ASSEMBLY ... from 'filename.dll' with permission_set = external_access". If this works, there may be a problem with the settings in your SSDT project and it might be better to repost on the SSDT forum (http://social.msdn.microsoft.com/Forums/en-US/ssdt/threads).

    Cheers, Bob

    • Marked as answer by ez44 Friday, October 5, 2012 10:17 AM
    Thursday, October 4, 2012 7:02 PM
    Moderator
  • Thanks a lot for thr reply

    all your assumptions are correct sorry for not specifying :)

    and when i run

    select name, is_trustworthy_on from sys.databases where name = 'somedatabase' 

    it looks like trustworthy isnt on, i get a 0 returned, when i first run "set trustworthy on" it is on and then once i try and publish my assembly it switches off

    Thanks for the help

    Friday, October 5, 2012 9:44 AM
  • ill try manually registering it
    Friday, October 5, 2012 9:45 AM
  • Manually registering it worked
    Friday, October 5, 2012 10:09 AM