ERROR [42724] - .NET to DB2 via Managed Provider

[Frank Buchan]

The complete error is at the end of this cry for help.

I've been tasked with hooking an interface in ASP.NET and a handheld (via
web services) to an existing DB2 data store. The requirement for real-time
data submission is driving the development, and all is going well, In fact,
the managed provider is working perfectly to execute stored procedures to
read from the data store, both ones that have no parameters (simple lookup
lists), lookup lists that are pre-filtered with parameters, and ones that
use identity columns to pull editable records. Performance is fine, and
everything on the interface side is great.

BUT (always is one), I get the same basic error (see below) every time I
execute a stored procedure that inserts or updates a record. These same
procedures all work perfectly from inside the command editor, and the logon
to the data store there is the same as the one in the .NET development
environment (to eliminate permissions issues). Yet, the error is pulled
every time, and being somewhat of a newcomer to the DB2 world, I expect it
is probably looking me right in the face.

The code accessing the stored procedure is almost identical to the read
code, rendered in VB.NET, and patterned after examples on the IBM sample
set.

Absolutely any insight, even where to look to resolve this, would be highly
appreciated. Thanks.

Frank Buchan

***ERROR DETAILS BELOW***


ERROR [42724] [IBM][DB2/NT] SQL0444N Routine "*ERT_DEST" (specific name
"SQL050622125403880") is implemented with code in library or path
"...2.V1_INSERT_DEST", function "MDM2.V1_INSERT_DEST" which cannot be
accessed. Reason code: "4". SQLSTATE=42724

 

[Frans Bouma [C# MVP]]

The complete error is at the end of this cry for help.

I've been tasked with hooking an interface in ASP.NET and a handheld
(via web services) to an existing DB2 data store. The requirement for
real-time data submission is driving the development, and all is
going well, In fact, the managed provider is working perfectly to
execute stored procedures to read from the data store, both ones that
have no parameters (simple lookup lists), lookup lists that are
pre-filtered with parameters, and ones that use identity columns to
pull editable records. Performance is fine, and everything on the
interface side is great.

BUT (always is one), I get the same basic error (see below) every
time I execute a stored procedure that inserts or updates a record.
These same procedures all work perfectly from inside the command
editor, and the logon to the data store there is the same as the one
in the .NET development environment (to eliminate permissions
issues). Yet, the error is pulled every time, and being somewhat of a
newcomer to the DB2 world, I expect it is probably looking me right
in the face.

The code accessing the stored procedure is almost identical to the
read code, rendered in VB.NET, and patterned after examples on the
IBM sample set.

Absolutely any insight, even where to look to resolve this, would be
highly appreciated. Thanks.

Frank Buchan

***ERROR DETAILS BELOW***


ERROR [42724] [IBM][DB2/NT] SQL0444N Routine "*ERT_DEST" (specific
name "SQL050622125403880") is implemented with code in library or
path "...2.V1_INSERT_DEST", function "MDM2.V1_INSERT_DEST" which
cannot be accessed. Reason code: "4". SQLSTATE=42724

Without more information, I think it's a security error of some sort.

FB


--

 

[Frank Buchan]

Thanks for the input, Frans. It struck me that you are probably correct.

I only wish I had more information to share.

Within a stored procedure for DB2 is it necessary to mark the procedure in
some special way to permit its insertion of a row? The stored procedure was
created in the DB2 Add-In interface, and the login is the same, but I
haven't enough ground-level experience in DB2 to know if that could still
require some authorization specific to the table or schema over and above a
login. It runs fin in the command editor interface, so I suspected it was
some kind of security issue, but I'm stumped from that point. Does an
insertion procedure have different inherent permission requirements?

The complete error is at the end of this cry for help.

I've been tasked with hooking an interface in ASP.NET and a handheld
(via web services) to an existing DB2 data store. The requirement for
real-time data submission is driving the development, and all is
going well, In fact, the managed provider is working perfectly to
execute stored procedures to read from the data store, both ones that
have no parameters (simple lookup lists), lookup lists that are
pre-filtered with parameters, and ones that use identity columns to
pull editable records. Performance is fine, and everything on the
interface side is great.

BUT (always is one), I get the same basic error (see below) every
time I execute a stored procedure that inserts or updates a record.
These same procedures all work perfectly from inside the command
editor, and the logon to the data store there is the same as the one
in the .NET development environment (to eliminate permissions
issues). Yet, the error is pulled every time, and being somewhat of a
newcomer to the DB2 world, I expect it is probably looking me right
in the face.

The code accessing the stored procedure is almost identical to the
read code, rendered in VB.NET, and patterned after examples on the
IBM sample set.

Absolutely any insight, even where to look to resolve this, would be
highly appreciated. Thanks.

Frank Buchan

***ERROR DETAILS BELOW***


ERROR [42724] [IBM][DB2/NT] SQL0444N Routine "*ERT_DEST" (specific
name "SQL050622125403880") is implemented with code in library or
path "...2.V1_INSERT_DEST", function "MDM2.V1_INSERT_DEST" which
cannot be accessed. Reason code: "4". SQLSTATE=42724

Without more information, I think it's a security error of some sort.

FB


--
------------------------------------------------------------------------
Get LLBLGen Pro, productive O/R mapping for .NET: http://www.llblgen.com
My .NET blog: http://weblogs.asp.net/fbouma
Microsoft MVP (C#)
------------------------------------------------------------------------

 

[Frank Buchan]

Thanks to Frans for his confirmation of the suspicion I had it might be
security. I just decided that given a like-minded thought, I should work my
way down the security ladder...and I've found and resolved the problem. As
embarrassing as the oversight is, it warrants a mention here in aid of any
other DB2 newbies who might experience a similar error.

I had given correct permissions for the stored procedures, etc. What I had
not done on the schema set up for testing was give insert/update permission
to the calling security account. So, the account "devtest" was calling the
stored procedure properly, executing the insert, and promptly being rejected
by the server. Granting insert/update on the specific tables resolved the
problem entirely and instantly. Apparently the way the DDL wrote the schema
into the test set allowed reads, but explicitly denied writes.

So, the moral of the story is that even old dogs (20 years experience on
Oracle and SQL Server, as well as time on MySQL and some other lesser
engines) can learn new tricks...and make infuriatingly simple mistakes.
Worse, I compounded my grief by not explicitly checking the account I logged
into the Command Editor with. It was a default maintenance account (local
logon and automatic), the very account I executed the DDL from, and hence
privileged to execute read/writes/etc.

Thanks again, Frans, as it was your think that sparked my deeper examination
of the chain of security.

Frank Buchan

 

[Frans Bouma [C# MVP]]

Thanks to Frans for his confirmation of the suspicion I had it might
be security. I just decided that given a like-minded thought, I
should work my way down the security ladder...and I've found and
resolved the problem. As embarrassing as the oversight is, it
warrants a mention here in aid of any other DB2 newbies who might
experience a similar error.

I had given correct permissions for the stored procedures, etc. What
I had not done on the schema set up for testing was give
insert/update permission to the calling security account. So, the
account "devtest" was calling the stored procedure properly,
executing the insert, and promptly being rejected by the server.
Granting insert/update on the specific tables resolved the problem
entirely and instantly. Apparently the way the DDL wrote the schema
into the test set allowed reads, but explicitly denied writes.

So, the moral of the story is that even old dogs (20 years experience
on Oracle and SQL Server, as well as time on MySQL and some other
lesser engines) can learn new tricks...and make infuriatingly simple
mistakes. Worse, I compounded my grief by not explicitly checking
the account I logged into the Command Editor with. It was a default
maintenance account (local logon and automatic), the very account I
executed the DDL from, and hence privileged to execute
read/writes/etc.

Thanks again, Frans, as it was your think that sparked my deeper
examination of the chain of security.

Glad I could help
It's partly IBM's fault though. Their error message was pretty
obscure, so if they just had reported an ACCESS DENIED or something,
you would have known the cause immediately. But then again, perhaps
that error code states just that 'ACCESS DENIED',

FB


--