SQL CLR 을 통한 공유 폴더 파일 복사 예

SQL Server CLR 은 닷넷 프레임워크 런타임을 통합하여  SQL Server 의 확장성을 매우 높혀주는 방식으로,. 자세한 사항은 http://msdn.microsoft.com/ko-kr/library/ms131102 를 참조. 아래 예제 코드는 SQL Server 에서 공유 폴더의  파일 정보를 가져오는 사용자 정의 함수 간단 코드.

using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Security.Principal;
using System.IO;
using System.Runtime.InteropServices;
using System.Security.Permissions;

public partial class UserDefinedFunctions {
    [DllImport("advapi32.dll", SetLastError = true)]
[PermissionSetAttribute(SecurityAction.Demand, Name = "FullTrust")] 

public static extern bool LogonUser(string lpszUsername,
        string lpszDomain,
        string lpszPassword,
        int dwLogonType,
        int dwLogonProvider,
        ref IntPtr phToken);

    [Microsoft.SqlServer.Server.SqlFunction(DataAccess = DataAccessKind.Read)] 
public static SqlString yeonpilTestFunction(SqlString LogonName,
        SqlString DomainName,
        SqlString LogonPassword,
        SqlString LocalPathFile,
        SqlString RemoteUNCFile) {
        // ALTER DATABASE CLRTest SET TRUSTWORTHY ON 
        IntPtr token = IntPtr.Zero;
        bool valid = LogonUser((string) LogonName, (string) DomainName,
            (string) LogonPassword, 2, 0, ref token);

        if (valid) {
            using(WindowsImpersonationContext context = WindowsIdentity.Impersonate(token)) {
                File.Copy((string) LocalPathFile, (string) RemoteUNCFile, true);
                context.Undo();
            }
        }

        return new SqlString("Success");
    }
};

간단하며,. 공유 폴더에 접근 하려면 원격지 서버에 대한 인증을 해야 하는데, 레거시 API  참조. 인증을 통과한 후에는 일반적인 닷넷 코드를 그대로 사용 하면 되며 SQL 에서 사용자 정의 함수의 데이터 형에 맞게 처리만 해주면 완료.

 배포는,  VS.NET 의 배포를 하게 되면 원격지 SQL 서버에 배포가 자동으로 됨. 하지만 자동으로 배포시에는 SQL 서버에서 권한 문제로 설정값 편집이 되지 않으므로, SQL 쿼리 스트립트를 생성하여 서버에서 실행하여 등록하는 것이 좋음.

CREATE FUNCTION [dbo].[yeonpilFunction](@LogonName [nvarchar](4000), @DomainName [nvarchar](4000), @LogonPassword [nvarchar](4000), @LocalPathFile [nvarchar](4000), @RemoteUNCFile [nvarchar](4000)) RETURNS [nvarchar](4000) WITH EXECUTE AS CALLER AS EXTERNAL NAME [SqlServerCLRTest].[UserDefinedFunctions].[yeonpilFunction]
 GO

EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFile', @value=N'Function1.cs' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'yeonpilFunction'
 GO

 EXEC sys.sp_addextendedproperty @name=N'SqlAssemblyFileLine', @value=N'17' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'FUNCTION',@level1name=N'yeonpilFunction'
 GO

위와 같은 함수를 생성후에 아래와 같은 방식으로 사용자 쿼리문에서 호출 하여 사용

exec yeonpilFunction  'id','domain', 'pwd','d:\test11.txt','\\원격지서버\d$\saved.txt'