The UDP/UDF/UDT did not revert thread token.

Question

I have been struggling with this error for a while now. Not much when I put it in the search engines. I get the error as follows when I execute this CLR stored procedure:

 

Msg 10312, Level 16, State 49, Procedure SpPICK00, Line 0

.NET Framework execution was aborted. The UDP/UDF/UDT did not revert thread token.

 

The code for the stored procedure is as follows. If anybody could offer any advice on what this error means I would appreciate it. I know through debugging that it's erroring out on the ImpContext = ClientID.Impersonate() line.

 

Dim DbCon As New SqlConnection("context connection=true")

Dim DbSql As New SqlCommand("SELECT TOP 1 * FROM TblTransactions", DbCon)

Dim TransactionID As Int64

DbSql.Connection.Open()

Dim DbRs As SqlDataReader

DbRs = DbSql.ExecuteReader

While DbRs.Read

TransactionID = DbRs("TransactionID")

End While

DbRs.Close()

Dim MenuID As Int16

Dim MONumber As String

Dim LineNumber As String

Dim PTUse As String

Dim SEQN As String

Dim WorkCentre As String

Dim Stock As String

Dim Bin As String

Dim Qty As Double

 

DbSql = New SqlCommand("SELECT * FROM VwPickList WHERE TransactionID = @TransactionID", DbCon)

DbSql.Parameters.Add("@TransactionID", SqlDbType.BigInt).Value = TransactionID

DbRs = DbSql.ExecuteReader

If DbRs.Read Then

MenuID = DbRs("MenuID")

MONumber = DbRs("MONumber")

LineNumber = DbRs("LineNumber")

PTUse = DbRs("PT_USE")

SEQN = DbRs("SEQN")

WorkCentre = DbRs("WorkCentre")

Stock = DbRs("Stock")

Bin = DbRs("Bin")

Qty = DbRs("Hours")

End If

DbRs.Close()

DbSql.Connection.Close()

DbCon.Dispose()

DbSql.Dispose()

Dim FSClient As New FSTIClient

Dim ClientID As WindowsIdentity

Dim ImpContext As WindowsImpersonationContext

ClientID = SqlContext.WindowsIdentity

ImpContext = ClientID.Impersonate

Shell("NET USE K: \\FPTEST\FShift", AppWinStyle.Hide)

FSClient.InitializeByConfigFile("K:\Mfgsys\fs.cfg", False, False)

If FSClient.IsLogonRequired Then

FSClient.Logon("VBS", "visib", "")

End If

If MenuID = 2 Then

Dim Pck As New PICK08

Pck.OrderType.Value = "M"

Pck.IssueType.Value = "I"

Pck.OrderNumber.Value = MONumber

Pck.LineNumber.Value = LineNumber

Pck.PointOfUseID.Value = PTUse

Pck.OperationSequenceNumber.Value = SEQN

Pck.ItemNumber.Value = WorkCentre

Pck.Stockroom.Value = Stock

Pck.Bin.Value = Bin

Pck.IssuedQuantity.Value = Qty

FSClient.ProcessId(Pck)

ElseIf MenuID = 1 Then

Dim Pck As New PICK04

Pck.OrderType.Value = "M"

Pck.IssueType.Value = "I"

Pck.OrderNumber.Value = MONumber

Pck.LineNumber.Value = LineNumber

Pck.PointOfUseID.Value = PTUse

Pck.OperationSequenceNumber.Value = SEQN

Pck.ItemNumber.Value = WorkCentre

Pck.IssuedQuantity.Value = Qty

FSClient.ProcessId(Pck)

End If

FSClient.Terminate()

ImpContext.Undo()

 

 

Answer 1

What is your CLR Safety Level set to for this?  I am thinking it likely is going to need UNSAFE.  You can also check out this post and see if any of the information in it will help you out:

 

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=647059&SiteID=1

 

Are you certain  that you need to do impersonation?  You should be able to set the ACL for your service account on the folders being accessed and have it work.

 

Follow through this forum post, it should resolve issues with impersonation.

 

http://www.dbnewsgroups.net/group/microsoft.public.sqlserver.programming/topic17089.aspx

 

HTH

Answer 2

That error message is most likely to occur when you have an unhandled exception in your code (after the impersonation call).

I'd suggest to use try catch (start the try right before your impersonate call), in the catch you get the error message (perhaps return it as an output param - your code is used as a proc, yes?), and hav a finally blovk where you do the Undo().

Personally I think the exception happens when you do the shell call (I assume the Shell method goes away and uses the Process class). I duplicated your code (well, I did some data access, followed by impersonation, followed by a call to Process.Start) and I got the same error as you when something went wromg in Process.Start(). I.e, my impersonation call went OK.

Niels

Answer 3

This is really odd. I put a try cath round the whole code. I then returned the erorr using SQLContext.Pipe.Send and nothing come back.

 

Also I tried removing the Shell command completely as it's not really essential and it makes no difference. Any other ideas? I think we may be along the right lines trying to catch the exception but it doesn't seem to be doing so.

Answer 4

As far as I can tell, you can't do the impersonation thing in SQLCLR??? I have been playing with this since the first post off and on, and searching online.  I don't find anyplace where this has succeeded, only people with this exact same problem regarding the Impersonation.

Answer 5

Jonathan Kehayias wrote:

As far as I can tell, you can't do the impersonation thing in SQLCLR??? I have been playing with this since the first post off and on, and searching online.  I don't find anyplace where this has succeeded, only people with this exact same problem regarding the Impersonation.

Impersonation in SQLCLR works just fine. If you want any demo-code, please say so and I can send it to you.

Niels

Answer 6

I would be interested in seeing it, yes.  I don't have a specific need for it at the moment, but may need it at some point.  I haven't found a single reference online where someone had this resolved, which is why I put the ??? behind my comment.  I just don't know, and I haven't been able to correct it.  Can you also provide which safetly level you have the assembly registered for?

 

Answer 7

It is set as "UNSAFE"

Answer 8

I so $%^&*@%$ HATE these forums. This is now the third time I try to post this message an include some code snippets. The two previous times when I tried to format the snippets to dode, the whole post went away. Give me NNTP based newsgroups any day!!!!!!!

 

OK, so I feell better now - hopefully this time it will work. Here's some code to test out impersonation:

 

1. Create a text file somewhere on your hard-drive (c:\ is OK). Take away all permissions to that file from the account which SQL runs under.

2. Compile this code snippet into an assembly. Deploy (with permission set EXTERNAL_ACCESS), and create a function around the method:

 

Code Snippet

[SqlFunction]
public static string GetFileStuff(string path)
{
  FileStream fs = null;
  TextReader tr = null;
  try
  {
    fs = new FileStream(path, FileMode.Open, FileAccess.ReadWrite);
    tr = new StreamReader(fs);
    string ret = tr.ReadToEnd();
    return ret;
  }
  catch (Exception e)
  {
    return e.Message;
  }

  finally
  {
    if (tr != null)
      tr.Close();
    if (fs != null)
      fs.Close();
  }
}

 

 

3. Try and execute select dbo.GetFileStuff('c:\test.txt');. This should return an error message, saying file acees denied (or something like that).

4. Now take this code and compile etc as per step 2. Notice the SystemDataAccess property in the SqlFunction attribute. It's there just due to the fact that this method will be created as a UDF in SQL Server, and when we're doing impersonation we're also (under the covers) doing some data reads against the system meta data. For a method created as a stored procedure this is not necessary:

Code Snippet

[SqlFunction(SystemDataAccess=SystemDataAccessKind.Read)]
public static string GetFileStuff2(string path)
{
  WindowsImpersonationContext ctx = null;
  FileStream fs = null;
  TextReader tr = null;
  try
  {
    WindowsIdentity wi = SqlContext.WindowsIdentity;
    ctx = wi.Impersonate();
    fs = new FileStream(path, FileMode.Open, FileAccess.ReadWrite);
    tr = new StreamReader(fs);
    string ret = tr.ReadToEnd();
    ctx.Undo();
    return ret;
  }
  catch (Exception e)
  {
    if (ctx != null)
      ctx.Undo();

    return e.Message;

  }

  finally
  {
    if (tr != null)
      tr.Close();
    if (fs != null)
      fs.Close();
  }

   
}

 

5. Execute and everything should hopefully work.

 

Niels

 

Answer 9

Solved this in the end. Instead of using impersonation in the .net code, I created a separate stored procedure as below whcih uses EXECUTE AS to execute the CLR stored procedure as the user I was trying to impersonate:

 

CREATE PROCEDURE [dbo].[SpExec]

WITH EXEC AS 'DOMAIN\USERNAME'

AS

BEGIN

EXEC SpPICK00

END

Answer 10

I tried this as well and it does not work. The CLR is has it permissions set to unsafe and I am using the following code to load a .CSV file into a datatable which I need to loop through several times later.

 Public Shared Function LoadCSVFile(ByVal strFilename As String, ByVal bHasHeader As Boolean) As DataTable

        Dim dt As New DataTable()
        Dim newIdentity As Security.Principal.WindowsIdentity = Nothing
        Dim newContext As Security.Principal.WindowsImpersonationContext = Nothing
        If (Not SqlContext.WindowsIdentity Is Nothing) Then
            newIdentity = SqlContext.WindowsIdentity
            newContext = newIdentity.Impersonate()
        End If

        Try
            If System.IO.File.Exists(strFilename) = False Then
                Throw New Exception("ERROR: File does not exist (or not enough rights) on the given location!")
            End If

            Dim path As String = System.IO.Path.GetDirectoryName(strFilename)
            Dim file As String = System.IO.Path.GetFileName(strFilename)

            Dim strConn As String = ""
            If bHasHeader Then
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=""text;HDR=Yes;IMEX=1;FMT=Delimited"""
            Else
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties=""text;HDR=No;IMEX=1;FMT=Delimited"""
            End If


            Using con As New System.Data.OleDb.OleDbConnection(strConn)
                Using com As New System.Data.OleDb.OleDbCommand("select * from [" + file + "]", con)
                    Using da As New System.Data.OleDb.OleDbDataAdapter(com)
                        Try
                            con.Open()
                            da.Fill(dt)
                            con.Close()
                            com.Dispose()
                            con.Dispose()
                        Catch ex As Exception
                            Throw ex
                        Finally
                            If con.State = ConnectionState.Open Then
                                con.Close()
                            End If
                        End Try
                    End Using
                End Using
            End Using

        Catch ex As Exception
            Throw ex
        End Try
        Try
            If (Not newContext Is Nothing) Then
                newContext.Undo()
            End If
        Catch ex As Exception
            SqlContext.Pipe.Send(ex.Message)
        End Try

        Return dt

    End Function

Answer 11

Hi Danny,

You need to make sure Undo is always called to revert the thread token. Add
a finally block or use the Using construct to be sure its always called.
Then you can get the actual error and proceed from there.

Cheers,
Bob Beauchemin
SQLskills

"DannyD35" wrote in message news:ef26a3c9-c5fa-47d0-a517-53fcfb987e99...
I tried this as well and it does not work. The CLR is has it permissions set
to unsafe and I am using the following code to load a .CSV file into a
datatable which I need to loop through several times later.

Public Shared Function LoadCSVFile(ByVal strFilename As String, ByVal
bHasHeader As Boolean) As DataTable

Dim dt As New DataTable()
Dim newIdentity As Security.Principal.WindowsIdentity = Nothing
Dim newContext As Security.Principal.WindowsImpersonationContext =
Nothing
If (Not SqlContext.WindowsIdentity Is Nothing) Then
newIdentity = SqlContext.WindowsIdentity
newContext = newIdentity.Impersonate()
End If

Try
If System.IO.File.Exists(strFilename) = False Then
Throw New Exception("ERROR: File does not exist (or not
enough rights) on the given location!")
End If

Dim path As String =
System.IO.Path.GetDirectoryName(strFilename)
Dim file As String = System.IO.Path.GetFileName(strFilename)

Dim strConn As String = ""
If bHasHeader Then
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
path + ";Extended Properties=""text;HDR=Yes;IMEX=1;FMT=Delimited"""
Else
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
path + ";Extended Properties=""text;HDR=No;IMEX=1;FMT=Delimited"""
End If


Using con As New System.Data.OleDb.OleDbConnection(strConn)
Using com As New System.Data.OleDb.OleDbCommand("select *
from [" + file + "]", con)
Using da As New System.Data.OleDb.OleDbDataAdapter(com)
Try
con.Open()
da.Fill(dt)
con.Close()
com.Dispose()
con.Dispose()
Catch ex As Exception
Throw ex
Finally
If con.State = ConnectionState.Open Then
con.Close()
End If
End Try
End Using
End Using
End Using

Catch ex As Exception
Throw ex
End Try
Try
If (Not newContext Is Nothing) Then
newContext.Undo()
End If
Catch ex As Exception
SqlContext.Pipe.Send(ex.Message)
End Try

Return dt

End Function