Vous ne pouvez peut-être pas établir une connexion à distance à SQL Server à partir d’un déclencheur CLR

Cet article vous aide à résoudre le problème où vous ne pouvez peut-être pas établir une connexion à distance à SQL Server à partir d’un déclencheur CLR (Common Language Runtime).

Version du produit d’origine : SQL Server
Numéro de base de connaissances d’origine : 2000373

Symptômes

Lorsque vous déployez un déclencheur CLR qui accède aux données à partir d’un serveur SQL Server distant à l’aide de Authentification Windows après l’emprunt d’identité du compte WindowsImpersonationContextd’utilisateur, vous obtenez le message d’erreur suivant lorsque le déclencheur est exécuté.

Msg 6522, Level 16, State 1, Procedure mytrigger, Line 1
Une erreur .NET Framework s’est produite lors de l’exécution d’une routine définie par l’utilisateur ou d’un agrégat « mytrigger » :
System.InvalidOperationException : l’accès aux données n’est pas autorisé dans ce contexte. Le contexte est une fonction ou une méthode non marquée avec DataAccessKind.Read ou SystemDataAccessKind.Read, est un rappel pour obtenir des données à partir de la méthode FillRow d’une fonction Table valued, ou est une méthode de validation UDT.
System.InvalidOperationException :
sur System.Data.SqlServer.Internal.ClrLevelContext.CheckSqlAccessReturnCode(SqlAccessApiReturnCode eRc)
at System.Data.SqlServer.Internal.ClrLevelContext.GetCurrentContext(SmiEventSink sink, Boolean throwIfNotASqlClrThread, Boolean fAllowImpersonation)
sur System.Data.SqlServer.Internal.ClrLevelContext.GetCurrentContext(Boolean throwIfNotASqlClrThread, Boolean fAllowImpersonation)
sur System.Data.SqlServer.Internal.ClrLevelContext.SuperiorTransaction.Promote()
sur System.Transactions.TransactionStatePSPEOperation.PSPEPromote(InternalTransaction tx)
sur System.Transactions.TransactionStateDelegatedBase.EnterState(InternalTransaction tx)
sur System.Transactions.EnlistableStates.Promote(InternalTransaction tx)
sur System.Transactions.Transaction.Promote()
sur System.Transactions.TransactionInterop.ConvertToOletxTransaction(Transaction)
sur System.Transactions.TransactionInterop.GetExportCookie(Transaction transaction, Byte[] whereabouts)
at System.Data.SqlClient.SqlInternalConnection.GetTransactionCookie(Transaction transaction, Byte[] whereAbouts)
sur System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx)
sur System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx)
sur System.Data.SqlClient.SqlInternalConnectionTds.Activate(transaction)
sur System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(transaction)
sur System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
sur System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConn...
L’instruction a été arrêtée.

Cause

Ce comportement est fait exprès. Le code CLR s’exécutant à l’intérieur de SQL Server est toujours appelé dans le contexte du compte de processus. Lorsqu’un déclencheur CLR qui contient du code permettant d’accéder aux données à partir d’un serveur SQL distant est exécuté, SQL Server promeut automatiquement la transaction DML ou DDL vers une transaction distribuée et se connecte au serveur distant à l’aide de l’identité SQL Server. Dans le cas où WindowsImpersonationContext est utilisé pour emprunter l’identité de l’utilisateur appelant, pour les connexions à sql Server distant, la promotion de la transaction de contexte vers une transaction de distribution échoue, ce qui entraîne l’erreur mentionnée dans la section Symptômes .

Résolution

Si vous avez besoin de la fonctionnalité d’emprunt d’identité de l’appelant à l’intérieur d’un déclencheur SQL CLR, gérez les transactions explicitement dans votre code. Utilisez la TransactionScopeOption.Supress méthode pour supprimer la gestion des transactions SQL intégrée et gérer la transaction distante avec validation ou restauration en fonction de vos besoins. Reportez-vous à la section Étapes à reproduire pour obtenir un exemple sur la façon dont vous pouvez reproduire ce problème et pour obtenir un exemple sur l’utilisation de la méthode précédente pour résoudre le problème.

Opérations à reproduire

Ouvrez SQL Server Management Studio (SSMS), puis connectez-vous à votre instance de SQL Server 2008.

Créez une base de données de test à l’aide du script suivant.

 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

Dans Microsoft Visual Studio 2008, créez un projet Visual C# à l’aide du modèle de projet SQL Server.
Nommez le projet SQLCLRTriggerProject.
Dans le menu Projet , sélectionnez SQLCLRTriggerProject et configurez la section Base de données pour qu’elle pointe vers la base de données créée précédemment dans la procédure (dbTriggerTest) et définissez le niveau d’autorisation sur Externe.
Dans le menu Projet, sélectionnez Ajouter un nouvel élément.
Sélectionnez Déclencheur dans la boîte de dialogue Ajouter un nouvel élément .
Tapez un nom pour le nouveau déclencheur.

Remplacez le code du déclencheur nouvellement créé par l’exemple de code suivant.

Liste de codes problématique :

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; 
        }  
    }  
}

Déployez le projet sur la base de données créée à l’étape 2 à l’aide de l’option Déployer le projet de déclencheur SQLCLR dans le menu Générer .
Ouvrez SSMS, puis connectez-vous à l’instance de SQL Server 2008 sur laquelle le déclencheur est déployé.
Vous devez voir les deux éléments suivants créés sous la base de données dbTriggerTestde test :
- Déclencheurs - mytrigger
- Assemblys - SQLCLRTriggerProject
À l’aide du volet Propriétés de l’assembly dans SSMS, vérifiez que le jeu d’autorisations sur l’assembly SQLCLRTriggerProject affiche l’accès externe.
Exécutez l’instruction suivante pour reproduire le problème. insert into t values (1)

Remplacez la liste de codes problématique par l’exemple de code suivant pour résoudre le problème.

Correction de la description du code :

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; 
            }  
        }  
    }
}

Commentaires

Cette page a-t-elle été utile ?

Last updated on 2024-12-14