OLEDB2 works 99.9%, but fails sometimes with DB_SEC_E_PERMISSIONDENIED(0x80040E09)

[gedger]

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?

[wellmet]

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

[mwood]

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?

[gedger]

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?

[wellmet]

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

[wellmet]

P.S.  I like the use of reflection to get the method in the error handling routine.

Stan

[mwood]

Yes, I'd assume that if a user can see the opportunity there shouldn't be any access issues, I wonder if perhaps an activity or history item is attached to the opportunity that they don't have access to. In either case, if this were indeed the problem then a reboot should have no affect.

The CURRENTACTOLEDB2 object generates a connection string populated with the current users information, Allen could give much better details on this than I however.

Seems as though you weren't the first to receive this message: http://community.act.com/t5/Sage-ACT-Premium/Error-Message-from-Dashboard/td-p/109452, I think it may be SQL that's throwing the exception because in this post the error is occurring on the dashboard and not through the OLE.

With that being said it could potentially be a windows user account issue and not one with the act user, the intermittent nature and the fact that you can't reproduce will make that difficult to narrow down though.

[gedger]

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.

[jimdurkin]

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

[jimdurkin]

I am updating this post since some our our newer tech support ERMISSIOND​ENIED 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