none
ADO.NET connection Problem when executing as non admin RRS feed

  • Question

  •  

    Hello,

    I'm working with a company that is having problems connecting to a database via vb.net. The application runs instantly when run as an administration. It takes 1 minute and 30 seconds to execute as a non administrator. The connection string is using SQL authentication. Here is the function executing the code. There have been console writes added to help debug the issue. The lines in red are the problem. The code is running on a Windows 2003 64bit processor. Any idea's would be greatly appreciated.

     

     

     

    Public Function SubmitLog(ByVal JobID As String, ByVal MessageID As Integer, ByVal LogLevel As Integer, ByVal componentID As String, ByVal MessageText As String) As Integer

    Dim ssql As String

    Console.Write("SubmitLog" & vbCrLf)

    ssql = " insert into LogFile (JobID, MessageDateTime, MessageID, LogLevel, ComponentID, MessageText, ServerID)" & _

    " values ('" & JobID & "'," & _

    "'" & Now() & "'," & _

    MessageID & "," & _

    LogLevel & "," & _

    "'" & componentID & "'," & _

    "'" & MessageText.Replace("'", " ") & "'," & _

    "'" & System.Environment.MachineName & "')"

    Try

    Console.Write("ssql =" & ssql & vbCrLf)

    Console.Write("Set SqlConnection: " & Now() & " " & conn & vbCrLf)

    Dim tComm As New SqlClient.SqlConnection(conn)

    Console.Write("Set SqlCommand" & Now() & " " & vbCrLf)

    Dim objcommand As New SqlClient.SqlCommand(ssql, tComm)

    Console.Write("run sql open" & Now() & " " & vbCrLf)

    objcommand.Connection.Open()

    Console.Write("run sql Query" & vbCrLf)

    objcommand.ExecuteNonQuery()

    Console.Write("run sql close" & vbCrLf)

    objcommand.Connection.Close()

    Console.Write("run sql dispose" & vbCrLf)

    objcommand.Dispose()

    objcommand = Nothing

    Console.Write("run sql done" & vbCrLf)

     

    ' SqlHelper.ExecuteScalar(Conn, CommandType.Text, ssql)

    Catch ex As Exception

    Console.Write(ex.Message & vbCrLf)

    Return 1

    End Try

    Return 0

    End Function

    Monday, July 30, 2007 1:55 PM

Answers

  •  

    Here's the solution.

     

    The Group or User needs rights to Create Global Object under Local Security Settings/ User Rights Assignment.

    Tuesday, August 14, 2007 8:59 PM

All replies

  • Here is the log. The names and passwords in the connectiong string have been turned into *** to protect the innocent.

     

    Starting

    conn=Data Source=***;Initial Catalog=***;UID=***;PWD=***

    ;

     

    SubmitLog

     

    ssql = insert into LogFile (JobID, MessageDateTime, MessageID, LogLevel, Compone

    ntID, MessageText, ServerID) values ('JobID','7/27/2007 2:14:01 PM',0,1,'compone

    ntID','MessageText','SMPDSCD01')

     

    Set SqlConnection: 7/27/2007 2:14:01 PM Data Source=***;Initial Catalog=***;UID=***;PWD=***

    ;

     

    Set SqlCommand 7/27/2007 2:15:37 PM

     

    run sql open7/27/2007 2:15:37 PM

    run sql Query

    run sql close

    run sql dispose

    run sql done

    ErrorLevel = 0

    Press any key to continue . . .

     

     

     

    Monday, July 30, 2007 2:07 PM
  •  

    I changed the line

    Dim tcomm as new SQLClient.SQLCommand(conn)

    to

    Dim tcomm as new SQLClient.SQLCommand

    tcomm.ConnectionString = conn

     

    I threw in some console.write lines and it is definetly hanging on the DIM statement reguardless of the connection string.

     

    To me this rules out firewall issues.

     

    I then gave the application a strong name and used something called caspol.exe or something like that to make the application fully trusted. That didn't help.

     

    I also found out that sometimes it will havg for 20 minutes with out a response. It actually only ran correctly a few times. Both times it took 1 minute and 30 seconds to dim the variable tcomm.

     

    When the user is logged in as an admin every thing works fine.

    Monday, July 30, 2007 8:54 PM
  • Have you checked the Event Log to see if there are any messages that might relate to the problem?

    Tuesday, July 31, 2007 1:18 PM
  • Yes, That was one of the first things we looked at. Nothing jumped out at us.

    I found this post.

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

     

    That is the exact problem I am having. I use remote desktop and VPN to log into this server. I'm trying to get setup locally with a server I can remotely connect to try and recreate the problem.

    Tuesday, July 31, 2007 1:34 PM
  • Have you tried using the SQL Profiler?

     

    Tuesday, July 31, 2007 1:47 PM
  • I did not but I the application is not reaching the database.

     

    if the code had one line

     

    Dim tcomm as new SQLClient.Connection

    it would hang there. I can't create the object

    Tuesday, July 31, 2007 3:06 PM
  • Sorry post 3 was incorrect.

    this code hits Begin Dim but does not reach Done with Dim. I pasted the wrong code in post 3.

     

    Try

    Console.Write("Set SqlConnection: " & Now() & " " & conn & vbCrLf)

    Console.WriteLine("Being Dim " & Now())

    Dim tComm As New SqlClient.SqlConnection

    Console.WriteLine("done with dim" & Now())

    Tuesday, July 31, 2007 3:09 PM
  • We wrote a smaller app that does three things.

    1. Write to console.

    2. Dim conn as SqlClient.Connection

    3. Write to console.

     

    We never get to step three.

     

    I used ProcMon and here's what I see as differences.

     

    Good Run

    RegOpenKey HKLM\System\CurrentControlSet\Control\ComputerName\ActiveComputerName SUCCESS
    RegQueryValue HKLM\System\CurrentControlSet\Control\ComputerName\ActiveComputerName\ComputerName SUCCESS
    RegCloseKey HKLM\System\CurrentControlSet\Control\ComputerName\ActiveComputerName SUCCESS
    RegCloseKey HKLM\System\CurrentControlSet\Control\ComputerName SUCCESS
    RegCloseKey HKLM\System\CurrentControlSet\Services\.NET Data Provider for SqlServer\Performance SUCCESS
    RegOpenKey HKLM\Software\Microsoft\Ole SUCCESS
    RegQueryValue HKLM\SOFTWARE\MICROSOFT\OLE\MaximumAllowedAllocationSize NAME NOT FOUND
    RegCloseKey HKLM\SOFTWARE\MICROSOFT\OLE SUCCESS

     

     

    Bad Run

    RegOpenKey HKLM\System\CurrentControlSet\Control\ComputerName\ActiveComputerName SUCCESS
    RegQueryValue HKLM\System\CurrentControlSet\Control\ComputerName\ActiveComputerName\ComputerName SUCCESS
    RegCloseKey HKLM\System\CurrentControlSet\Control\ComputerName\ActiveComputerName SUCCESS
    RegCloseKey HKLM\System\CurrentControlSet\Control\ComputerName SUCCESS

     

     

    There are about 800 lines in each run so I'm just posting where the bad one fails. The bad one gets stuck on

    RegCloseKey HKLM\System\CurrentControlSet\Control\ComputerName

    Then when I kill the app I get

    Thread Create SUCCESS
    Thread Exit SUCCESS
    Thread Exit SUCCESS
    Thread Exit SUCCESS

    Followed by a bunch of other stuff.

     

    I checked the registry permissions and they look ok to me. The wierd part is it hangs on a close of SqlSever\Performance even tho it read it fine earlier in the log.

    Tuesday, July 31, 2007 5:09 PM
  • I went as far as putting in a support call to microsoft and doing some memory dumps that some development team is going to look at. For now the code has been switched over to use OLEDB instead of ADO.

    Wednesday, August 1, 2007 6:57 PM
  •  

    Here's the solution.

     

    The Group or User needs rights to Create Global Object under Local Security Settings/ User Rights Assignment.

    Tuesday, August 14, 2007 8:59 PM