如何在CLR函数中切换至指定AD账户而非SQL服务账户运行

Alright, let's break down your questions one by one and adjust your CLR code to handle credential switching properly:

1. How to switch accounts and revert back? #

You'll need to use Windows Impersonation to temporarily switch to your target AD account. Here's the core workflow:

• Use the Windows API LogonUser to get a security token for the target AD account
• Create a WindowsIdentity from that token and start an impersonation context
• Run all file operations while this context is active
• Revert back to the original SQL service account once operations are complete

We'll use P/Invoke to call the necessary Windows APIs since .NET's built-in methods don't cover explicit credential-based logon for this scenario.

2. When to switch credentials for a table-valued function? #

Since your TVF reads the file line-by-line via IEnumerator, you only need to switch once before opening the file. Keep the impersonation active for the entire lifecycle of the file (from open to close). Switching on every MoveNext() would introduce unnecessary overhead and risk file access failures if the context changes mid-read.

3. Does the credential switch affect the entire SQL instance? #

No—Windows impersonation is thread-specific. The impersonation context only applies to the thread running your CLR function. Once you revert the context, the thread returns to using the SQL service account, and other parts of the SQL instance are completely unaffected. This matches the "per-execution" behavior you want, just like SQL impersonation.

Modified CLR Code with Impersonation #

Here's your updated code that handles AD account impersonation for file access. We've added the necessary Windows API calls and wrapped file operations in an impersonation context that cleans up automatically:

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

// TextLine holds a single line from the file
public class TextLine
{
    public int LineIndex { get; set; }
    public string Data { get; set; }

    public TextLine(int lineIndex, string data)
    {
        LineIndex = lineIndex;
        Data = data;
    }
}

public class TextFile : IEnumerable, IEnumerator
{
    private int _currentLineIndex = -1;
    private string _currentLineData;
    private StreamReader _fileReader;
    private WindowsImpersonationContext _impersonationContext;

    // Windows API imports for impersonation
    [DllImport("advapi32.dll", SetLastError = true, CharSet = CharSet.Unicode)]
    private static extern bool LogonUser(
        string lpszUsername,
        string lpszDomain,
        string lpszPassword,
        int dwLogonType,
        int dwLogonProvider,
        out IntPtr phToken);

    [DllImport("kernel32.dll", SetLastError = true)]
    private static extern bool CloseHandle(IntPtr hObject);

    private const int LOGON32_LOGON_INTERACTIVE = 2;
    private const int LOGON32_PROVIDER_DEFAULT = 0;

    // Constructor takes file path + AD account credentials
    public TextFile(string filePath, string adUsername, string adDomain, string adPassword)
    {
        // Impersonate target AD account before opening the file
        IntPtr userToken = IntPtr.Zero;
        try
        {
            bool logonSuccess = LogonUser(adUsername, adDomain, adPassword,
                LOGON32_LOGON_INTERACTIVE, LOGON32_PROVIDER_DEFAULT, out userToken);

            if (!logonSuccess)
            {
                throw new System.ComponentModel.Win32Exception(Marshal.GetLastWin32Error());
            }

            using (var identity = new WindowsIdentity(userToken))
            {
                _impersonationContext = identity.Impersonate();
            }

            // Open file under the impersonated account
            _fileReader = new StreamReader(filePath);
        }
        finally
        {
            // Clean up token even if logon fails
            if (userToken != IntPtr.Zero)
            {
                CloseHandle(userToken);
            }
        }
    }

    public IEnumerator GetEnumerator() => this;

    public object Current => new TextLine(_currentLineIndex, _currentLineData);

    public bool MoveNext()
    {
        _currentLineData = _fileReader.ReadLine();
        if (_currentLineData != null)
        {
            _currentLineIndex++;
            return true;
        }
        else
        {
            // Close file and revert impersonation when done reading
            _fileReader.Close();
            _impersonationContext?.Undo();
            return false;
        }
    }

    public void Reset()
    {
        _currentLineIndex = -1;
        // Clean up resources if reset is called
        _fileReader?.Close();
        _impersonationContext?.Undo();
    }
}

public partial class UserDefinedFunctions
{
    [SqlFunction(
        Name = "ReadTextFileAsADUser",
        FillRowMethodName = "ReadTextFile_FillRow",
        TableDefinition = "LineIndex INT, Data NVARCHAR(4000)")]
    public static IEnumerable ReadTextFileAsADUser(
        SqlString filePath,
        SqlString adUsername,
        SqlString adDomain,
        SqlString adPassword)
    {
        TextFile fileReader = null;
        try
        {
            fileReader = new TextFile(
                filePath.Value,
                adUsername.Value,
                adDomain.Value,
                adPassword.Value);
            return fileReader;
        }
        catch
        {
            // Clean up if initialization fails
            fileReader?.Reset();
            throw;
        }
    }

    public static void ReadTextFile_FillRow(object row, out SqlInt32 lineIndex, out SqlString data)
    {
        var line = (TextLine)row;
        lineIndex = line.LineIndex;
        data = line.Data;
    }
}

Key Implementation Notes: #

• Credential Security: In production, avoid passing passwords directly as parameters. Instead, store credentials in a secure SQL Credential object and retrieve them programmatically to reduce exposure.
• Guaranteed Cleanup: We use finally blocks and Undo() to ensure the impersonation context is always reverted, even if an error occurs during file operations.
• Error Visibility: The LogonUser call includes error checking via Win32Exception to surface issues with AD account logons directly in SQL.

内容的提问来源于stack exchange，提问作者Mark Roworth