Failed SQL Connection over time

[joe_pool_is]

I've built a small PC app that resides in the Task Bar Tray until one of our employees sends a signal to it with a handheld device.

 

When the PC app is activated, we connect to the SQL Server to retrieve records using Integrated Security on the PC:

Private m_conn As SqlConnection

Public Sub New()
 InitializeComponent()
 m_conn = New SqlConnection("Data Source=ACTIVE1;Initial Catalog=Inventory;Integrated Security=SSPI;")
 ' more code that isn't relivant
End Sub

 

The problem is that after the PC app has sat around idle for a few hours, the employees get the generic SQL error message "Cannot generate SSPI context." Basically, it can not log in.

 

My routine that uses the SQL Server is all contained, so I don't know how to prevent this error.

Private Sub ViewData()
 Dim cmd As New SqlCommand("SELECT * FROM PartsTable WHERE SN=@SERNO", m_conn)
 cmd.Parameters.AddWithValue("@SERNO", SN)
 Dim da As New SqlDataAdapter(cmd)
 Dim dt As New DataTable()
 Try
   da.Fill(dt)
   If (0 < dt.Rows.Count)) Then
     ' sometimes it gets here, but not if the app has been idle for a while
   End If
 Catch ex As Exception
   MessageBox.Show(ex.Message)
 Finally
   dt.Dispose()
   da.Dispose()
 End Try
End Sub

If I can connect 5-10 times straight, what would cause code like this to fail after it has been sitting idle for a while? When the PC app is activated, a Dialog Box is displayed that the Employee has to confirm the operation on. Only then is this routine called.

[PlausiblyDamp]

http://support.microsoft.com/kb/811889 has some useful information on possible causes of this error - does it make any sense in your case?

 

Is the sql server being rebooted or similar in-between the client succeeding and failing to get a valid connection?

[joe_pool_is]

Hello, Sir! Welcome back.

 

I have seen the Microsoft KB article. It has a lot of information, and I went through it as much as I could. The rest of it (Kerbose -vs- named pipe, etc.) I passed on to our SQL administrator, who keeps telling me it isn't his server that is doing anything.

 

I am able to connect and interact with the server most of the time. It is only when the application has been idle for a while that this unique bug appears.

 

Typically, to fix it, we log the user off, then log them back on. Exiting and restarting my application does not fix the problem with connecting to SQL.

[PlausiblyDamp]

Sounds odd! Have you tried disabling connection pooling (add a Pooling=false to your connection string) - just in case something is happening there that is keeping broken connections around?

[joe_pool_is]

Looks like it was the IT guy's mistake. He had the group added, but the user wasn't a part of the group.