C# SQL CLR to download an *.xlsx file from the SSRS webserver.

Question

C# SQL CLR to download an *.xlsx file from the SSRS webserver.

vagaines

SSC Veteran

Points: 216
More actions
October 24, 2017 at 6:54 am

#329271

I have recently been given the task to create an SQL CLR stored procedure for SQL 2012, to download an *.xlsx file from an SSRS url. After some research I have created an SSIS script task in C# which will perform this task. It has two parameters one resource name (url), and the other the file name (share name). I have used the same logic in a database project to create a C# SQL CLR to perform this task. The stored procedure runs but throws an error:
Msg 6522, Level 16, State 1, Procedure Download_xlsx, Line 2
A .NET Framework error occurred during execution of user-defined routine or aggregate "Download_xlsx":
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.Net.WebClient.DownloadFile(Uri address, String fileName)
at StoredProcedures.Download_xlsx(SqlString resourcename, SqlString filename).
Here is the code for the SQL CLR:
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Threading.Tasks;
using System.Net;
public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure(Name = "Download_xlsx")]
public static void Download_xlsx(SqlString resourcename, SqlString filename)
{
   // New Web Client
   WebClient webClient = new WebClient();
   webClient.Credentials = new NetworkCredential("Domain\\User", "password*********");
   // Fake a user Agent
   webClient.Headers.Add(HttpRequestHeader.UserAgent, "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1)");
   webClient.DownloadFile(resourcename.ToString(), filename.ToString());

}
}
How can I resolve this permission issue. I do not want to change any security on the SSRS web server.

Viewing 3 posts - 1 through 3 (of 3 total)

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

Sue_H SSC Guru Points: 90860 More actions · Answer 1

You may want to check the permission set for the assembly you created and see if it's set to external_access. You can query sys.assemblies using something like:
SELECT [name], is_user_defined, [permission_set], permission_set_desc FROM sys.assemblies;

There are some other requirements and security considerations. Refer to this documentation:
Creating an Assembly

Sue

vagaines SSC Veteran Points: 216 More actions · Answer 2

Hi I believe Sue_H has the correct answer. After reading these posts:
https://stackoverflow.com/questions/33727844/c-sharp-clr-throws-security-exception-unless-marked-as-unsafe

and
https://docs.microsoft.com/en-us/sql/relational-databases/clr-integration/security/clr-integration-code-access-security

I changed the Permission level under the SQLCLR project setting to "UNSAFE". This is apparently not an ideal solutions, but my SQLCLR now works.
I also replaced the "webClient.Credentials = new NetworkCredential("Domain\\User", "password*********");" line with
"webClient.UseDefaultCredentials = true;" to use the windows credentials.