Another lament about Application Roles

[Lyle Fairfield]

The Sproc is the record source for a report.

The Sproc builds a complicated string name @SQLString [varchar
(4000)].

At the end, it executes the string:

EXEC (@SQLString)

Everything is grand, almost.

Open the proc and one gets a datasheet showing of its records.
Open it in ADO and one gets a recordset.
Use it as the recordscource for a form or report and the
object shows its records.

Life is grand.

Introduce Application Roles. There is nothing in the database
window so forms and reports cannot use Sprocs, tables, views
or functions as recordsources. Solution -> use SQLStrings such
as "SELECT * FROM Table" or "EXEC Sproc".

Works well! EXCEPT in the case above for REPORTS.

Remember? The SPROC itself is executing a string, EXEC
(@SQLString).

The report recordsource cannot see the SPROC so we use, "EXEC
Sproc" (ie we are doubly EXECUTING) and it returns

GASP

a string; The first field of the first record contains "ALL".
And Access reports that it could not find the object "dbo.ALL
...."

Argggggggggggh! Am I nuts or is this too bizarre?

Solution: Change the Sproc to:

"SELECT (@SQLString)"

This works. But is it too wacko to include in a paid for and
relatively sensitive report justifying the employment of about
1400 employees at an average salary of $60-70 M CAD?

If you don’t use Application roles this may make no sense to
you whatever.

 

[Mary Chipman]

It is not recommended to use application roles from Access because of
the way Access opens additional connections under the covers. Have you
thrown a Profiler trace on the app to see what gives?

--Mary

 

[Lyle Fairfield]

It is not recommended to use application roles from Access because of
the way Access opens additional connections under the covers. Have you
thrown a Profiler trace on the app to see what gives?

--Mary

No. I have found another solution. And who cares what is not recommended?

 

[Mary Chipman]

When something is not recommended, it's usually for a good reason,
like, it doesn't work reliably. This is generally reason enough for
most people to care. In case you run into further issues with
approles, you might want to take a look at SQL Application Role Errors
with OLE DB Resource Pooling
http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q229564

--Mary

 

[Lyle Fairfield]

When something is not recommended, it's usually for a good reason,
like, it doesn't work reliably. This is generally reason enough for
most people to care. In case you run into further issues with
approles, you might want to take a look at SQL Application Role Errors
with OLE DB Resource Pooling
http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q229564

--Mary

Your grasp of language is breathtaking, Mary.

 

[Sylvain Lafontaine]

The more and more I read things like this, the more that I become convinced
to forget about ADP and go definitively to the .NET framework.

I just want to thank you, Lyle, for having taken the time to share with us
your research about Application Roles and ADP, but now I'm thinking that I
will be soon out of this line, not only about application roles but about
ADP as well.

Good luck!

Regards,
Sylvain Lafontaine, ing.

 

[Lyle Fairfield]

The more and more I read things like this, the more that I become
convinced to forget about ADP and go definitively to the .NET framework.

I just want to thank you, Lyle, for having taken the time to share with
us your research about Application Roles and ADP, but now I'm thinking
that I will be soon out of this line, not only about application roles
but about ADP as well.

I am feeling the same way. Here are my reasons. If they are wrong, please,
let me know.

If one uses bound forms with an ADP, then one must give insert, update and
delete permissions on the tables or views underlying the forms.

If these permissions rely on the user's login, NT permissions or group
membership then the user will have the same permissions beyond the ADP and
its constraints. This destroys security and any data integrity that may be
application dependent.

To prevent these difficulties, one may use Applications Roles.

Implementing Applications Roles is a horrendous adventure of hit-or-miss
stabs in the dark, because Access uses so many connections, and these are
poorly documented or not documented at all. These problems are greater in
Access2K than in later versions because Access2k allows shorter
RecordSource strings than later versions, and because Access2K does not
permit the direct assignment of a report RecordSet. Because one is required
to do very much unconventional coding and property setting, the application
may be difficult to maintain and may be inefficient, or it may not work at
all. What I am finding right now, is that contrivances that make
Application Roles work on machine A, do not necessarily make Application
Roles work on machine B, while they both seem to have exactly the same
versions and updates of software. So testing becomes a multiple machine
activity, right now, about 80. (As I have been developing in Access almost
since its beginning, and previously in Clipper, FoxPro, FoxBase and
dBaseIII since their beginning, variances among machines are not new to me;
but I have not seen anything like the stubbornness of ADPs with Application
Roles before).)

If one doesn't use bound forms then why use Access at all? It's an enormous
unwieldy platform, with many idiosyncrasies and a scripting (programming)
language that is archaic and clumsy. Well, Access reports are very good.
But there are now other routes to great reports, and as I understand it,
the .NET framework is one.

But then, I have not yet had my morning coffee ....

 

[Mary Chipman]

If these permissions rely on the user's login, NT permissions or group
membership then the user will have the same permissions beyond the ADP and
its constraints. This destroys security and any data integrity that may be
application dependent.

To prevent these difficulties, one may use Applications Roles.

Application roles aren't the only, or even the best, answer to this
problem. You could just as easily create a single regular database
role to achieve similar results without all of the angst that goes
with debugging application role mishaps. If you connect using a single
obfuscated SQL Server login (known only to your code) instead of using
integrated security, then you won't have a problem of users trying to
connect using other tools since they won't have valid SQLS logins to
let them in. If the argument is made that enabling using SQLS logins
is less secure than integrated security, then the counter-argument is
that approles also introduce insecurity since the login and password
must of necessity be stored in client code. And there's decompilers
for everything. My $.02.

--Mary