12-15-2011 07:48 AM
Hi.
I have a simple bit of code that is installed in the tools directory accross 50+ ACT 2010 installations.
Basically, a set of buttons on the opportunity layout to ship data out to another application.
It works flawlessly for all users 99% of the time. So for 20,000 runs a year, no problems.
About once a week(52 times per year) a random user receives the following error:
No error message available, result code: DB_SEC_E_PERMISSIONDENIED(0x80040E09).
Here is the function that occasionally generates the error:
Private Function SqlToXML(ByVal strsql As String) As System.Data.DataTable
Dim dbDataTable As New System.Data.DataTable("rgxo")
Try
APFW = Act.UI.ActApplication.Instance
Dim dbConectString As String = APFW.ActFramework.CurrentACTOLEDB2
Using dbConnection As New OleDbConnection(dbConectString)
Dim dbDataAapter As New OleDbDataAdapter(strsql, dbConnection)
dbDataAapter.Fill(dbDataTable)
End Using
Catch ex As Exception
rgMainErrorHandler(System.Reflection.MethodInfo.GetCurrentMethod().ToString, ex.Message)
End Try
Return dbDataTable
End Function
the SQL that is passed to this is always simple stuff like:
SELECT * FROM opportunity WHERE opportunityid = '...'
SELECT * FROM contact WHERE contactid = '...'
A system reboot always fixes it.
Any ideas what's causing the error or how to recover in code without a reboot?
12-15-2011 08:49 AM
The issue is probably that from time to time the login credentials that are being used are invalid. I suspect that you have more than one domain controller in your network and when they log in using one of the backup domain controllers the permissions are not getting set correctly. That's just a wild guess on my part and I wouldn't want to be the one trying to figure out where the problem was. You'll probably have to do some tracing to find it. Here is a link to a Microsoft KB that describes one thing that might cause the issue.
http://support.microsoft.com/kb/890307
Stan
12-15-2011 08:50 AM
My first thought is It's throwing a permission denied error, and the OLE adheres to the security model, is it possible the user attempting to perform the operation has insuffecient rights to the opportunity/history etc? However that type of issue wouldn't be resolved by a reboot, is the exception thrown for every query attempt following the first time it's thrown?
Have you debugged through to know which line the exception is being thrown on?
12-15-2011 09:09 AM
Thanks for your thoughts.
It's been a very elusive problem. I've never been able to duplicate it myself. I'll try to narrow down the exact line but the only way is to rollout an update to all installations and wait a week or two.
So this code is triggered from within ACT, the user is always on the detail view of an opportunity. I'm just guessing here but if the user can see the opportunity on their screen, wouldn't they have rights to pull the opportunity record via OLE?
All act users in all our databases can view any of the data.
-0-
Question, Is the permission denied orginating from SQL server?
are we talking about the windows user or the act user?
12-15-2011 09:53 AM
Matthew will have to answer that one. I realized after reading your question that I really don't know how it logs in to the database. Earlier versions of ACT! didn't have Windows Authentication enabled in the SQL database so the OLEDB2 would have had to log in using SQL Authentication and I suspect that it still does so it is probably SQL that is returning the error message.
It could be as Matthew suggested that it is attempting to access data that it isn't entitled to access but that begs the question of why it will work after rebooting. I'm also surprised that the state is saved so that you can't just close and reopen ACT! and resolve the problem.
Like I said before I think you're going to have to do some low level tracing to find out what's really going on. It may be just a timing issue that could be solved by putting in some sort of pause statement between the login and execution of the query too.
Stan
12-15-2011 09:57 AM
P.S. I like the use of reflection to get the method in the error handling routine.
Stan
12-15-2011 11:04 AM
12-15-2011 02:58 PM
Great, you've given me a few different directions to work on.
I'll go after it from 3 angles:
1) when the error happens, I'll snoop around more before rebooting to see if I can find any other symptons of domain/windows user authentication being the issue.
2) I'll add some more detailed reporting so i can determine the exact line of failure
3) I'll try some timing/delay tactics in code. Also delaying before reboot to see if the systems ever recover.
Thanks very much for your help.
12-15-2011 04:42 PM
Here is my SWAG...
I would look into idle time on the server's connection pool and amount of connections in the pool..
1) The user may being logged out of the pool because an idle time has expired.
OR
2) The connection pool may be filled so the users connection gets dropped.
This SWAG is based on the fact I have received this error about once a month on my dev PC.
If I break the code, leave it over night, and continue the in the AM I have received the permission denied error. At which time I completely stop the debugger then start is again (F5) the VS solution with no more issues.
Just my 2cents
-- Jim Durkin
04-16-2012 05:49 AM
I am updating this post since some our our newer tech support ERMISSIONDENIED issues have been resolved by running ACT as administrator.
This is especially true on Windows 7 since the UAC does not always run the user as 'local administrator' The ACT OLEDB2.dll may have been installed under an admin role.
Hope this helps
-- Jim Durkin