Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: VB SQL CLR
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
 
' works as long as SQL Server has access to the file

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 newContext As System.Security.Principal.WindowsImpersonationContext ???
        'newContext = SqlContext.WindowsIdentity.Impersonate() ???

        Dim filePerm As New FileIOPermission(FileIOPermissionAccess.Read, path)
        filePerm.Assert()
        Dim fi As New FileInfo(path)
        If fi.Exists Then
            'populate the record.
            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)
            ' Send the record to the client
            SqlContext.Pipe.Send(record)
        End If
    End Sub
End Class
Posted 17-Jul-12 9:24am
dbugbee260

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

hi,
you can use impersination in you code
follow the below link

A Complete Impersonation Demo in C#.NET[^][^]
  Permalink  
Comments
dbugbee at 17-Jul-12 14:54pm
   
Thanks, but I literally need to know how to implement it in my specific example. I've read a TON of stuff about how to implement the impersonation but I still haven't been able to get it to work.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



Advertise | Privacy | Mobile
Web03 | 2.8.1411023.1 | Last Updated 17 Jul 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100