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

Hey guys,
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 operation with files:

Full error message:

Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate “fncArquivo_Ler”:
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 useLongPath, Boolean checkHost)
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.fncArquivo_Ler(String Ds_Path)

Don't know what CLR is, or don't know 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 analyzing the way the SQLCLR Assembly was published on the server:

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

Let's analyze which permission set is configured in Visual Studio:

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

And now ?

The solution

Guys, calm down. This is not a fault of Visual Studio. This error is occurring because an important step in the post Introduction to SQL CLR on SQL Server was 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 so that it is created correctly and your routines work as expected:

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