I'm trying to mimic the SQL xp_getfiledetails procedure, but I haven't been able to figure out how to do impersonation. How can it be implemented in the following code (which works fine when the SQL Server has access to the share.) The assembly already has External Access permission.
Imports System
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports Microsoft.SqlServer.Server
Imports System.Data.Sql
Imports System.Runtime.InteropServices
Imports System.IO
Imports System.Security.Permissions
Partial Public Class StoredProcedures
<Microsoft.SqlServer.Server.SqlProcedure()> _
Public Shared Sub spGetFileDetails(ByVal path As String)
Dim record As New SqlDataRecord( _
New SqlMetaData("Alternate Name", SqlDbType.NVarChar, 255),
New SqlMetaData("Size", SqlDbType.NVarChar, 255),
New SqlMetaData("Creation Date", SqlDbType.NVarChar, 8),
New SqlMetaData("Creation Time", SqlDbType.NVarChar, 8),
New SqlMetaData("Last Written Date", SqlDbType.NVarChar, 8),
New SqlMetaData("Last Written Time", SqlDbType.NVarChar, 8),
New SqlMetaData("Last Accessed Date", SqlDbType.NVarChar, 8),
New SqlMetaData("Last Accessed Time", SqlDbType.NVarChar, 8),
New SqlMetaData("Attributes", SqlDbType.NVarChar, 255) _
)
Dim filePerm As New FileIOPermission(FileIOPermissionAccess.Read, path)
filePerm.Assert()
Dim fi As New FileInfo(path)
If fi.Exists Then
record.SetString(0, fi.FullName)
record.SetString(1, fi.Length.ToString)
record.SetString(2, fi.CreationTime.Date.ToString("yyyyMMdd"))
record.SetString(3, fi.CreationTime.TimeOfDay.ToString)
record.SetString(4, fi.LastWriteTime.Date.ToString("yyyyMMdd"))
record.SetString(5, fi.LastWriteTime.TimeOfDay.ToString)
record.SetString(6, fi.LastAccessTime.Date.ToString("yyyyMMdd"))
record.SetString(7, fi.LastAccessTime.TimeOfDay.ToString)
record.SetString(8, fi.Attributes.ToString)
SqlContext.Pipe.Send(record)
End If
End Sub
End Class