Sql Server Azure PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE

2
votes

We received this error yesterday.I found a few articles suggesting it was an issue with orphaned userIds, but because the database is hosted on Azure the stored procedure to access the user profiles isn't available and I'm no database expert.

One of the articles I was reading

https://social.technet.microsoft.com/Forums/en-US/3f30c3fd-e659-4ed2-a0f8-fbe8de140037/microsoft-net-framework-while-trying-to-load-assembly-id-1?forum=ssdsgetstarted

It says it cant find assembly 'microsoft.sqlserver.types' 11.1.0.0 the version that was deployed a few weeks back was 10.0.0.0 so i updated my nuget packages but that version is only on 11.0.2

https://www.nuget.org/packages/Microsoft.SqlServer.Types/

We resolved the issue at the moment by taking a copy of the database. Its only temporarily resolved i feel and it'll happen again.

System.Data.SqlClient.SqlException : An error occurred in the Microsoft .NET Framework while trying to load assembly id 1. 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: System.IO.FileNotFoundException: Could not load file or assembly 'microsoft.sqlserver.types, Version=11.1.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified. ---> System.IO.FileNotFoundException: Could not load file or assembly 'microsoft.sqlserver.types, Version=11.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91' or one of its dependencies. The system cannot find the file specified. System.IO.FileNotFoundException: System.IO.FileNotFoundException: at System.Reflection.RuntimeAssembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, RuntimeAssembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection, Boolean suppressSecurityChecks) at System.Reflection.RuntimeAssembly.InternalLoadAssemblyName(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection, Boolean suppressSecurityChecks) at System.Reflection.RuntimeAssembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection) at System.Reflection.Assembly.Load(String assemblyString)

1
sql-serverazureazure-sql-database
are you still experiencing the same issue? – Joseph Idziorek
Hi Joesph, it happened again two weeks ago. we have since upgraded to Azure sql server v12. So im hoping that might have resolved it. we shall see – InitLipton
I would love to better understand you scenario and what triggered this error. Can you please email me at joseidz at microsoft.com and we will report back to this thread the findings? – Joseph Idziorek

1 Answers

3
votes

We’d like to give our apologies about the issue you have been experiencing using the service. While we have been actively investigating the issue, it is difficult to reproduce (and thus fully fix). The relevant data we have at this point: • The issue does not happen all the time. When it starts happening on a SQL instance, it is persistent until the process is recycled. • While we have been getting file-not-found errors, our inspection of the machines where the issue has been found actually has the file there that should be there • We know that the issue happens on V11 and V12 servers, but it is worse on the V11 servers due to some architectural differences in that version of the service code.

For the time being, we recommend that customers hitting this issue consider whether moving their server to V12 now is possible. The reasons for this are: • Based on our telemetry data, we know V12 servers hit this issue less often • The scope of the impact on V11 is larger than on V12 (meaning that more customers are impacted when it happens) due to architectural differences between V11 and V12. • We have additional service capabilities on V12 that allow us to more quickly identify and mitigate this issue. We are actively working on deploying the needed changes. So, when ready, we should be able to apply a mitigation step automatically for any customer hitting this issue.

Some bugs are harder than others to fix, and this one has been a bit tougher than we expected to be. We will continue working on tracking down the exact root cause of this issue (why the file can’t be loaded even when it is there). Hopefully, this gives you enough information to work around it while we track it down.

Thanks and Apologies, Conor Cunningham Architect, SQL