Возможно, вы не сможете установить удаленное подключение к SQL Server из триггера СРЕДЫ CLR

Статья
06/20/2023

Эта статья поможет устранить проблему, из-за которой вы не сможете установить удаленное подключение к SQL Server из триггера CLR.

Оригинальная версия продукта: SQL Server
Исходный номер базы знаний: 2000373

Симптомы

При развертывании триггера CLR, который получает доступ к данным из удаленного SQL Server с помощью проверка подлинности Windows после олицетворения учетной записи пользователя с помощью WindowsImpersonationContext, при выполнении триггера появляется следующее сообщение об ошибке.

Msg 6522, Level 16, State 1, Procedure mytrigger, Строка 1
При выполнении определяемой пользователем подпрограммы или агрегатного "mytrigger" произошла ошибка платформа .NET Framework:
System.InvalidOperationException: доступ к данным в этом контексте запрещен. Контекст является функцией или методом, не помеченным как DataAccessKind.Read или SystemDataAccessKind.Read, является обратным вызовом для получения данных из метода FillRow функции с табличным значением или методом проверки определяемого пользователем типа.
System.InvalidOperationException:
в System.Data.SqlServer.Internal.ClrLevelContext.CheckSqlAccessReturnCode(SqlAccessApiReturnCode eRc)
в System.Data.SqlServer.Internal.ClrLevelContext.GetCurrentContext(SmiEventSink sink, Boolean throwIfNotASqlClrThread, Boolean fAllowImpersonation)
в System.Data.SqlServer.Internal.ClrLevelContext.GetCurrentContext(Boolean throwIfNotASqlClrThread, Boolean fAllowImpersonation)
в System.Data.SqlServer.Internal.ClrLevelContext.SuperiorTransaction.Promote()
в System.Transactions.TransactionStatePSPEOperation.PSPEPromote(InternalTransaction tx)
в System.Transactions.TransactionStateDelegatedBase.EnterState(InternalTransaction tx)
в System.Transactions.EnlistableStates.Promote(InternalTransaction tx)
в System.Transactions.Transaction.Promote()
в system.Transactions.TransactionInterop.ConvertToOletxTransaction(Transaction transaction)
в System.Transactions.TransactionInterop.GetExportCookie(Транзакция транзакций, байт[] местонахождение)
в System.Data.SqlClient.SqlInternalConnection.GetTransactionCookie(Transaction transaction, Byte[] whereAbouts)
в System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx)
в System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx)
в System.Data.SqlClient.SqlInternalConnectionTds.Activate(Транзакция транзакции)
в System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Транзакция)
в System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
в System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConn...
Выполнение инструкции прекращено.

Причина

Такое поведение является особенностью данного продукта. Код СРЕДЫ CLR, выполняемый внутри SQL Server, всегда вызывается в контексте учетной записи процесса. При выполнении триггера СРЕДЫ CLR, содержащего код для доступа к данным с удаленного сервера SQL Server, SQL Server автоматически переводит транзакцию DML или DDL в распределенную транзакцию и подключается к удаленному серверу с помощью SQL Server удостоверения. WindowsImpersonationContext Если используется для олицетворения удостоверения вызывающего пользователя, то для подключений к удаленной SQL Server продвижение контекстной транзакции до транзакции распространения завершается сбоем, что приводит к ошибке, указанной в разделе Симптомы.

Разрешение

Если требуется функциональность олицетворения удостоверения вызывающего объекта в триггере СРЕДЫ CLR SQL, явно управляйте транзакциями в коде. TransactionScopeOption.Supress Используйте метод для подавления встроенной обработки транзакций SQL и управления удаленной транзакцией с фиксацией или откатом в соответствии с вашими требованиями. См. раздел Шаги по воспроизведению , чтобы узнать, как можно воспроизвести эту проблему, и пример использования предыдущего метода для устранения проблемы.

Действия по воспроизведению

Откройте SQL Server Management Studio (SSMS), а затем подключитесь к экземпляру SQL Server 2008.

Создайте тестовую базу данных с помощью следующего скрипта.

 CREATE DATABASE dbTriggerTest 
 GO 
 ALTER DATABASE dbTriggerTest SET TRUSTWORTHY ON 
 GO 
 USE dbTriggertest 
 GO 
 CREATE TABLE t(c1 int) 
 GO  
 sp_configure 'clr enabled', 1 
 GO  
 reconfigure 
 GO

В Microsoft Visual Studio 2008 создайте проект Visual C# с помощью шаблона проекта SQL Server.
Присвойте проекту имя SQLCLRTriggerProject.
В меню Проект выберите SQLCLRTriggerProject и настройте раздел База данных , чтобы он указывал на базу данных, созданную ранее в процедуре (dbTriggerTest), и задайте для параметра Уровень разрешений значениеВнешний.
В меню Проект выберите Добавить новый элемент.
Выберите Триггер в диалоговом окне Добавление нового элемента .
Введите имя нового триггера.

Замените код только что созданного триггера приведенным ниже примером кода.

Проблемный список кода:

using System; 
using System.Data; 
using System.Data.SqlClient; 
using Microsoft.SqlServer.Server; 
using System.Security.Principal;  

public partial class Triggers 
{ 
    [Microsoft.SqlServer.Server.SqlTrigger(Name = "mytrigger", Target = "t", Event = "FOR insert")] 
    public static void mytrigger() 
    { 
        WindowsIdentity clientId = null; 
        WindowsImpersonationContext impersonatedUser = null;  

        // Get the client ID. 
        clientId = SqlContext.WindowsIdentity;  

        // This outer try block is used to thwart exception filter 
        // attacks which would prevent the inner finally 
        // block from executing and resetting the impersonation  

        try 
        { 
            try 
            { 
                impersonatedUser = clientId.Impersonate(); 
                if (impersonatedUser != null) 
                { 
                    SqlConnection conn = new SqlConnection(@"Data Source=<Your server name>;Initial Catalog=master;Integrated Security=SSPI"); 
                    conn.Open(); 
                    SqlCommand cmd = conn.CreateCommand(); 
                    cmd.CommandText = "select * from sys.sysobjects"; 
                    cmd.CommandType = CommandType.Text; 
                    cmd.ExecuteNonQuery(); 
                } 
            } 
            finally 
            { 
                // Undo impersonation. 
                if (impersonatedUser != null) 
                    impersonatedUser.Undo(); 
            } 
        } 
        catch 
        { 
            throw; 
        }  
    }  
}

Разверните проект в базе данных, созданной на шаге 2, с помощью параметра Развернуть проект триггера SQLCLR в меню Сборка .
Откройте SSMS и подключитесь к экземпляру SQL Server 2008, в котором развернут триггер.
В тестовой базе данных dbTriggerTestдолжны появиться следующие два элемента:
- Триггеры — mytrigger
- Сборки — SQLCLRTriggerProject
На панели Свойства сборки в SSMS убедитесь, что в наборе разрешений для сборки SQLCLRTriggerProject отображается внешний доступ.
Выполните следующую инструкцию, чтобы воспроизвести проблему. insert into t values (1)

Замените список проблемного кода приведенным ниже примером кода, чтобы устранить проблему.

Исправлено перечисление кода:

using System; 
using System.Data; 
using System.Data.SqlClient; 
using Microsoft.SqlServer.Server; 
using System.Security.Principal; 
using System.Transactions;  

public partial class Triggers 
{  
    [Microsoft.SqlServer.Server.SqlTrigger(Name = "mytrigger", Target = "t", Event = "FOR insert")] 
    public static void mytrigger() 
    {  
        using (new TransactionScope(TransactionScopeOption.Suppress)) 
        { 
            WindowsIdentity clientId = null; 
            WindowsImpersonationContext impersonatedUser = null; 
            // Get the client ID. 
            clientId = SqlContext.WindowsIdentity; 
            // This outer try block is used to thwart exception filter 
            // attacks which would prevent the inner finally 
            // block from executing and resetting the impersonation 
            try 
            { 
                SqlTransaction tran = null;  
                try 
                { 
                    impersonatedUser = clientId.Impersonate(); 
                    if (impersonatedUser != null) 
                    {  
                        SqlConnection conn = new SqlConnection(@"Data Source=<Your server name>;Initial Catalog=master;Integrated Security=SSPI");  
                        conn.Open(); 
                        tran = conn.BeginTransaction(); 
                        SqlCommand cmd = conn.CreateCommand(); 
                        cmd.Transaction = tran; 
                        cmd.CommandText = "select * from sys.sysobjects"; 
                        cmd.CommandType = CommandType.Text; 
                        cmd.ExecuteNonQuery(); 
                        tran.Commit(); 
                    } 
                } 
                catch (Exception ex) 
                { 
                    if (null != tran) 
                    tran.Rollback(); 
                    throw ex; 
                }  
                finally 
                { 
                // Undo impersonation. 
                if (impersonatedUser != null) 
                impersonatedUser.Undo(); 
                } 
            } 
            catch 
            { 
                throw; 
            }  
        }  
    }
}