Prev: Corruption errors in acc 2000 program
Next: Custom Autonumber Incrementation Question
From: adjo on 27 Jun 2008 11:15
I am working on an app with an Access2002 frontend and Sql2005
backend. I have to use integrated security. I want to prevent my users
from altering data in another way than via the frontend.
It looks to me that the mechanism to do it is the Sqlserver
sp_setapprole procedure. Works fine when programming directly to
Sqlserver, and also een Access Data Project at first sight seems to
work as it should via the call to the sp_setapprole proc.
But for a number of reasons I would like to use a normal MDB as
frontend with Dao3.6 as data access method. This works fine normally
when I use SqlServer as backend, but now when I want to use
Intergrated Security the necessary sp_setapprole won't behave as
expected:
1) Excuting it via a passthrough query while using a DSN seems to
work, but suddenly the changes in tablepriviliges (because of
activating the role) can be gone. Seems like the mechanism is
unstable.
2) Using a DSN less connectionstring has the result that the sql user
for the connection changes in the rolename (as it should be) but table
privs don't change at all.
I read about the '3 connections Access uses' when connecting to
Sqlserver ('How to use Application roles with Access projects and SQL
Server 2000 Desktop Edition'). Maybe this has got to do something with
the strange behaviour after executing sp_setapprole.
Is there some with experience with this problem. And hopelfully some
tips, because I desperatly need the Int.Security + an Mdb frontend.
From: Rich P on 27 Jun 2008 13:13
The issue you are experiencing is caused by the limited capabilities of
ODBC. For basic operations ODBC is an easy way to connect an Access mdb
to a sql server backend. But once the operations get a little more
sophisticated - as in your case - the limitations of ODBC become
apparent. The workaround is to switch to ADO - this is why ADO was
developed - to overcome the limitations of ODBC.

you can execute sp_setapprole through an ADODB.Command object very
easily (just make a reference in Tools/References to Microsoft ActiveX
DataObjects Library 2.5 or higher)

Sub xyz()

Dim cmd As New ADODB.Command
cmd.ActiveConnection = "Provider=SQLOLEDB; Data
Source=yourSrvr;Database=yourSvrDB;Trusted_Connection=Yes
cmd.ActiveConnection.CursorLocation = adUseClient
cmd.CommandType = adCmdStoredProc
cmd.CommandText = "sp_setapprole"
cmd.Execute
cmd.ActiveConnection.Close

End Sub
Rich

*** Sent via Developersdex http://www.developersdex.com ***
From: adjo on 28 Jun 2008 06:09
On 27 jun, 19:13, Rich P <rpng...(a)aol.com> wrote:
> The issue you are experiencing is caused by the limited capabilities of
> ODBC.  For basic operations ODBC is an easy way to connect an Access mdb
> to a sql server backend.  But once the operations get a little more
> sophisticated - as in your case - the limitations of ODBC become
> apparent.  The workaround is to switch to ADO - this is why ADO was
> developed -  to overcome the limitations of ODBC.
>
> you can execute sp_setapprole through an ADODB.Command object very
> easily (just make a reference in Tools/References to Microsoft ActiveX
> DataObjects Library 2.5 or higher)
>
> Sub xyz()
>
> Dim cmd As New ADODB.Command
> cmd.ActiveConnection = "Provider=SQLOLEDB; Data
> Source=yourSrvr;Database=yourSvrDB;Trusted_Connection=Yes
> cmd.ActiveConnection.CursorLocation = adUseClient
> cmd.CommandType = adCmdStoredProc
> cmd.CommandText = "sp_setapprole"
> cmd.Execute
> cmd.ActiveConnection.Close
>
> End Sub
> Rich
>
> *** Sent via Developersdexhttp://www.developersdex.com***

Thx. But I would realy like to stick to DAO for the application
itsself. I know DAO isn't the nr 1 technology any more, but I know DAO
well and don't do that much programming work as I used to do in the
past. For new stuff I concentrate on the Dotnet framework with it's
Ado.Net.
What would be nice is if the ADO code you send would have its effect
on the connection DAO/ODBC uses. That would be perfect, but as most
things in life 'perfect' isn't often the case. I'll test it anyway....
From: adjo on 28 Jun 2008 07:02
On 27 jun, 17:15, adjo <adgn...(a)gmail.com> wrote:
> I am working on an app with an Access2002 frontend and Sql2005
> backend. I have to use integrated security. I want to prevent my users
> from altering data in another way than via the frontend.
> It looks to me that the mechanism to do it is the Sqlserver
> sp_setapprole procedure. Works fine when programming directly to
> Sqlserver, and also een Access Data Project at first sight seems to
> work as it should via the call to the sp_setapprole proc.
> But for a number of reasons I would like to use a normal MDB as
> frontend with Dao3.6 as data access method. This works fine normally
> when I use SqlServer as backend, but now when I want to use
> Intergrated Security the necessary sp_setapprole won't behave as
> expected:
> 1) Excuting it via a passthrough query while using a DSN seems to
> work, but suddenly the changes in tablepriviliges (because of
> activating the role) can be gone. Seems like the mechanism is
> unstable.
> 2) Using a DSN less connectionstring has the result that the sql user
> for the connection changes in the rolename (as it should be) but table
> privs don't change at all.
> I read about the '3 connections Access uses' when connecting to
> Sqlserver ('How to use Application roles with Access projects and SQL
> Server 2000 Desktop Edition'). Maybe this has got to do something with
> the strange behaviour after executing sp_setapprole.
> Is there some with experience with this problem. And hopelfully some
> tips, because I desperatly need the Int.Security + an Mdb frontend.

By the way: is there another way to solve the 'get to the data via
another way than the app' problem then using the sp_setapprol
mechanism? 90% solutions are welcome as well.....
From: lyle fairfield on 28 Jun 2008 08:56
On Jun 28, 7:02 am, adjo <adgn...(a)gmail.com> wrote:
> On 27 jun, 17:15, adjo <adgn...(a)gmail.com> wrote:
>
>
>
> > I am working on an app with an Access2002 frontend and Sql2005
> > backend. I have to use integrated security. I want to prevent my users
> > from altering data in another way than via the frontend.
> > It looks to me that the mechanism to do it is the Sqlserver
> > sp_setapprole procedure. Works fine when programming directly to
> > Sqlserver, and also een Access Data Project at first sight seems to
> > work as it should via the call to the sp_setapprole proc.
> > But for a number of reasons I would like to use a normal MDB as
> > frontend with Dao3.6 as data access method. This works fine normally
> > when I use SqlServer as backend, but now when I want to use
> > Intergrated Security the necessary sp_setapprole won't behave as
> > expected:
> > 1) Excuting it via a passthrough query while using a DSN seems to
> > work, but suddenly the changes in tablepriviliges (because of
> > activating the role) can be gone. Seems like the mechanism is
> > unstable.
> > 2) Using a DSN less connectionstring has the result that the sql user
> > for the connection changes in the rolename (as it should be) but table
> > privs don't change at all.
> > I read about the '3 connections Access uses' when connecting to
> > Sqlserver ('How to use Application roles with Access projects and SQL
> > Server 2000 Desktop Edition'). Maybe this has got to do something with
> > the strange behaviour after executing sp_setapprole.
> > Is there some with experience with this problem. And hopelfully some
> > tips, because I desperatly need the Int.Security + an Mdb frontend.
>
> By the way: is there another way to solve the 'get to the data via
> another way than the app' problem then using the sp_setapprol
> mechanism? 90% solutions are welcome as well.....

One can use "normal" roles and logins and hide and encrypt the
usernames and passwords in code and compile applications to mdes or
ades or accdes.
This is as safe as the coding skills of the developer are good.
In this way users have no login or permissions of their own, so when
they create another adp, and the connection dialog opens they see no
servers. They can't login to the server, so they can't examines
usernames and passwords there.
Of course, such logins and permissions can be associated with
application roles, but if they are hidden and unknown, what's the
point of going that extra step?
Because Access opens multiple new connections erratically and
unpredictably, and because each of those connections must be
explicitly associated with an application role (where application
roles are used) this has been my practice. Actually it's not much
different than how we might do asp, where we hide connection
parameters in a special secure folder, or asp.net, where the
application hides them for us.

My experience in trying to use application roles with Access, and I've
done this on a very extensive project, is that this might double
development time. This is because Access may use connections one way
on Monday, but a slightly different way on Tuesday, depending of
course on what you had for breakfast. I know of no way to ensure that
Access will use application roles properly and consistently for pull-
downs and I resort to creating lists (strings) for them, based on very
basic ADO calls in code.

In my opinion this problem is likely to have been the determining
factor in MS abandoning ADPs. It makes ADPs potentially explosively
dangerous and MS had and has no credible solution.

There was a little girl,
Who had a little curl,
Right in the middle of her forehead.
When she was good,
She was very, very good,
But when she was bad, she had MS.
 |  Next  |  Last
Pages: 1 2 3 4 5
Prev: Corruption errors in acc 2000 program
Next: Custom Autonumber Incrementation Question