none
Error with SQL CLR - Msg 6522, Level 16

    Question

  • I have a SQL stored procedure, which calls a DLL, that calls a WCF control, which in turn calls a Virtual Earth web service (confused yet?).  The WCF works fine when testing and returns Virtual Earth values.  But when I call the WCF control from a DLL (in SQL), I am getting error 6522 which I have read is a impersonation issue. 

    Here is the path

    SQL Procedure ==>> DLL ==>> WCF ==>> Virtual Earth Webservice

    I reference the DLL in SQL, creating an assembly with External_Access permission set.  Database configuration is set to 90.  Trustworthy is set to ON.

    In my DLL, I am making the call as followed

    [SqlProcedure]
    public static void ReturnLatLong(string FullAddress)
    {
         string geoAddress = string.Empty;
         WindowsImpersonationContext newContext = null;
         WindowsIdentity newIdentity = SqlContext.WindowsIdentity;

     

    try
    {
         if (newIdentity != null) newContext = newIdentity.Impersonate();
         Service wcfService = new Service();
         geoAddress = wcfService.GetAddress(FullAddress);
         SaveData(geoAddress, FullAddress);
    }
    catch (Exception ex)
    {
         LogError(ex.Message);
    }
    finally
    {
         newContext.Undo();
    }
    }

    Assembly is created in SQL as follows:

    CREATE ASSEMBLY [GeocodeAddress]
    FROM 'C:\<path>\GeocodeAddress.dll'
    WITH PERMISSION_SET = EXTERNAL_ACCESS
    GO

    SQL Stored procedure is create as:

    CREATE

     

    PROCEDURE ReturnLatLong
    @FullAddress
    NVARCHAR(128)
    AS EXTERNAL NAME [GeocodeAddress].SQLClr.ReturnLatLong

    But when I execute the procedure, I get the following.  Any Thoughts?

    Msg 6522, Level 16, State 1, Procedure ReturnLatLong, Line 0

    A .NET Framework error occurred during execution of user-defined routine or aggregate "ReturnLatLong":
    System.InvalidOperationException: Data access is not allowed in this context. Either the context is a
    function or method not marked with DataAccessKind.Read or SystemDataAccessKind.Read,
    is a callback to obtain data from FillRow method of a Table Valued Function, or is a UDT validation method.

    System.InvalidOperationException:
    at System.Data.SqlServer.Internal.ClrLevelContext.CheckSqlAccessReturnCode(SqlAccessApiReturnCode eRc)
    at System.Data.SqlServer.Internal.ClrLevelContext.GetCurrentContext(SmiEventSink sink, Boolean throwIfNotASqlClrThread, Boolean fAllowImpersonation)
    at Microsoft.SqlServer.Server.InProcLink.GetCurrentContext(SmiEventSink eventSink)
    at Microsoft.SqlServer.Server.SmiContextFactory.GetCurrentContext()
    at System.Data.SqlClient.SqlConnectionFactory.GetContextConnection(SqlConnectionString options, Object providerInfo, DbConnection owningConnection)
    at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
    at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
    at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
    at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
    at System.Data.SqlClient.SqlConnection.Open()
    at SQLClr.LogError(String ErrorMessage)
    at SQLClr.ReturnLatLong(String FullAddress)

     

    If I comment out the call to the web service starting at, Service wcfService = new Service();, then it runs fine, so it's with this code that is the issue. 

    I'm obviously missing something.  Any ideas?

    Thanks
    Jay

    Friday, April 10, 2009 11:23 PM

All replies

  • Hi,

    One thing i know is that the messages you get from SqlClr don;t nescessarily point in the right direction.

    One thing i noticed with my project is that Sql throws a fit when the error comes back with a different user context that the original call, but my messages were different yours. Maybe this can help.

    Here's some sample code of how i got around that issue, maybe that will let you get the original error. The ImpWrap class handels the impersonation, wich is undone when Dispose() is called on it. The idea is that you assign the error from the impersonated context to local error variable that you can then check and throw under your own context.

    Regards GJ


    [Microsoft.SqlServer.Server.SqlProcedure]
        public static void pa_SetParent(SqlInt64 ObjId, SqlInt32 ObjTypeRelId, SqlInt64 ParObjId) {
        
            // wrap impersonated call
            Exception e2 = null;

            using (ImpWrap w = new ImpWrap(true)) {
                IDreamModel proxy = w.Proxy;
                try { proxy.SetParent((long)ObjId, (int)ObjTypeRelId, (long)ParObjId); }
                catch (Exception e) { e2 = e; }
            }

            // now check error under own context and throw if not null
            if (e2 != null) throw e2;
        }

    Have faith in people who seek the truth, be cautious with people who claim to have found it
    Tuesday, April 21, 2009 12:41 PM
  • Actually this does not seem to be the problem.

    Have you tried PERMISSION_SET = UNSAFE ? That could help.

    GJ

    Have faith in people who seek the truth, be cautious with people who claim to have found it
    Tuesday, April 21, 2009 12:45 PM
  • I had tried Permission_Set = Unsafe and e2 = ex also. But doesn't work.

    Tuesday, August 27, 2013 7:33 AM
  • Does the SaveData() method save the data into the database? You can't do data access in an impersonated context, so if this is the case, you need to Undo the impersonation before you call SaveData().

    Hope this helps, Bob

    Tuesday, August 27, 2013 3:59 PM