Using System.IO In A SQL2005 CLR Stored Procedure

I am attempting to develop a stored proc that will do the following:

1)  Take as an input parameter the filepath of a local directory

2)  Return a recordset showing all files contained in the local directory

At the code level, I am attempting to do the following:

1)  use system.io class to iterate through each file of a given local directory

2)  parse out a union query to show all file names and their system creation times

3)  insert the parsed sql into a sqldatareader, and send that out via a sqlpipe to the caller. 

After compiling and deploying the CLR stored proc, I get the following when I try to execute:

Msg 6522, Level 16, State 1, Procedure spclr_wcl_get_file_info, Line 0

A .NET Framework error occurred during execution of user defined routine or aggregate 'spclr_wcl_get_file_info':

System.Security.SecurityException: Request for the permission of type 'System.Security.Permissions.FileIOPermission, mscorlib, Version=2.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.Directory.InternalGetFileDirectoryNames(String path, String userPathOriginal, String searchPattern, Boolean includeFiles, Boolean includeDirs, SearchOption searchOption)

at System.IO.Directory.GetFiles(String path, String searchPattern, SearchOption searchOption)

at System.IO.Directory.GetFiles(String path)

at StoredProcedures.spclr_wcl_get_file_info(String str_dir)

This occurs even though I've set the SQL Server service to run as a local administrator on the machine (ie, the account should have full rights to virtually any local directory or file). 

I found the following article which I thought might resolve:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=494009&SiteID=1

but when I include the WindowsImpersonationContext, etc., I instead get the following:

Msg 10312, Level 16, State 49, Procedure spclr_wcl_get_file_info, Line 0

.NET Framework execution was aborted. The UDP/UDF/UDT did not revert thread token.

Was hoping someone could illuminate as to what I'm doing wrong?  Below is a code sample.  TIA.

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.IO;

using System.Diagnostics;

 

public partial class StoredProcedures

{

[Microsoft.SqlServer.Server.SqlProcedure]

public static void spclr_wcl_get_file_info(string str_dir)

{

// this clr stored proc will read all files in the trans log directory and return

// a recordset with file names and create dates.

//impersonate the calling user

System.Security.Principal.WindowsImpersonationContext newContext;

newContext = SqlContext.WindowsIdentity.Impersonate();

//enumerate files in directory, use to parse out union select query, return table with file names and create times.

string str_sql = "";

System.DateTime d_date;

FileInfo obj_fsi;

string[] str_arr_files = Directory.GetFiles(str_dir);

foreach(string str_file in str_arr_files)

{

obj_fsi = new FileInfo(str_dir + str_file);

if (obj_fsi.Exists)

{

d_date = obj_fsi.CreationTime;

str_sql += "SELECT CHAR(39)" + str_file + "CHAR(39) AS [file_name], ";

str_sql += d_date.ToLongDateString() + " file_creation_time UNION ";

}

}

// parse off the last 'union'

if (str_sql.Length > ("UNION ").Length)

str_sql = str_sql.Substring(0, str_sql.Length - ("UNION ").Length);

// use sql to send dataset back to caller.

SqlCommand obj_cmd = new SqlCommand();

obj_cmd.Connection = new SqlConnection("Context connection=true");

obj_cmd.Connection.Open();

obj_cmd.CommandText = str_sql;

SqlDataReader obj_reader = obj_cmd.ExecuteReader();

SqlPipe obj_pipe = SqlContext.Pipe;

obj_pipe.Send(obj_reader);

//clean up

obj_reader.Close();

obj_cmd.Connection.Close();

}



};