CLR - Solving the System.Security.Permissions.FileIOPermission problem in SQL Server

Hello people,
Good Morning!

In this post I will show you a very common problem during the development of SQL CLR projects, which consists of the error message below when trying to perform any file operation:

Complete error message:

Msg 6522, Level 16, State 1, Line 1
The .NET Framework error occurred during execution of user-defined routine or aggregate “fncArquivo_Read”:
System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.FileIOPermission, mscorlib, Version = 4.0.0.0, Culture = neutral, PublicKeyToken = b77a5c561934e089' failed.
System.Security.SecurityException:
at System.Security.CodeAccessSecurityEngine.Check (Object demand, StackCrawlMark & ​​stackMark, Boolean isPermSet)
at System.Security.CodeAccessPermission.Demand ()
at System.IO.FileStream.Init (String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy, Boolean checkLongHoleanPath
at System.IO.FileStream..ctor (String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, FileOptions options, String msgPath, Boolean bFromProxy, Boolean useLongPath, Boolean checkHost)
at System.IO.StreamReader..ctor (String path, Encoding encoding, Boolean detectEncodingFromByteOrderMarks, Int32 bufferSize, Boolean checkHost)
at System.IO.StreamReader..ctor (String path)
at UserDefinedFunctions.fncArchive_Ler (String Ds_Path)

Not sure what CLR is, or how to create your first SQLCLR project, how to publish, or have questions about this powerful SQL Server tool? Access this post and learn the basics.

Identifying the problem

The reason this error is occurring can be verified by looking at how the SQLCLR Assembly was published to the server:

As you know, with permission set Safe the assembly will not really have access to file manipulation, only with the External Access or Unsafe permission levels (now called Unrestricted).

Let's look at what permission set is configured in Visual Studio:

Here's the problem. In Visual Studio it is set for the assembly to be compiled with permission level UNSAFE, but the assembly is being created with permission level SAFE.

And now ?

The solution

Guys, calm down. This is not a glitch of Visual Studio. This error is occurring because an important post step Introduction to SQL CLR in SQL Server not followed, which is the definition of the project permissions (Class Library) that was imported into our CLR project:

Just make this simple change and republish your CLR and it will be created correctly and your routines will work as expected:

That's it!
Thanks for stopping by and see you in the next post.