SQL Server Security Problem

[Rob]

Hi,

We have a MS Access front-end connecting to a SQL Server back-end. We
use an ODBC connection using NT authentication, however the audit
problem we have is that a user has the security to run stored procs
outside the app e.g. open a blank Access db, connect to SQL Server
using his NT account and create a query.

What we want to do is to have the app use 1 SQL Server login, not NT,
to connect to the app, however we want to encrypt the login details
somewhere, as audit have a problem with the developers being able to
get their hands on this system account details.

You can do this in our C# apps easily using ASP.Net impersonation
(using aspnet_setreg), however does anyone have any idea how you can do
this in MSAccess?

Regards,
Rob

[Tom van Stiphout]

On 29 Nov 2006 00:42:30 -0800, "Rob" <ro*********@hotmail.comwrote:

AFAIK, ODBC does not support this, nor does Access Data Project (DAP).
I haven't checked if Access 2007 is improved in this area. I doubt it.

-Tom.

>Hi,

We have a MS Access front-end connecting to a SQL Server back-end. We
use an ODBC connection using NT authentication, however the audit
problem we have is that a user has the security to run stored procs
outside the app e.g. open a blank Access db, connect to SQL Server
using his NT account and create a query.

What we want to do is to have the app use 1 SQL Server login, not NT,
to connect to the app, however we want to encrypt the login details
somewhere, as audit have a problem with the developers being able to
get their hands on this system account details.

You can do this in our C# apps easily using ASP.Net impersonation
(using aspnet_setreg), however does anyone have any idea how you can do
this in MSAccess?

Regards,
Rob

[Lyle Fairfield]

"Rob" <ro*********@hotmail.comwrote in news:1164789750.233796.276670
@j44g2000cwa.googlegroups.com:

Hi,

We have a MS Access front-end connecting to a SQL Server back-end. We
use an ODBC connection using NT authentication, however the audit
problem we have is that a user has the security to run stored procs
outside the app e.g. open a blank Access db, connect to SQL Server
using his NT account and create a query.

What we want to do is to have the app use 1 SQL Server login, not NT,
to connect to the app, however we want to encrypt the login details
somewhere, as audit have a problem with the developers being able to
get their hands on this system account details.

You can do this in our C# apps easily using ASP.Net impersonation
(using aspnet_setreg), however does anyone have any idea how you can do
this in MSAccess?

Regards,
Rob

I use the crypt functions found at

http://support.microsoft.com/kb/821762

and other places.

I came to the place it seems that you are at now with an ADP several
years ago. The crypt functions and many hours work with them and
application roles saved the application. But I rejected Access for any
further SQL-Server front end development. Access is not safe for this.

--
Lyle Fairfield

from http://msdn.microsoft.com/library/de...l=/library/en-
us/dnmdac/html/data_mdacroadmap.asp

Obsolete Data Access Technologies
Obsolete technologies are technologies that have not been enhanced or
updated in several product releases and that will be excluded from future
product releases. Do not use these technologies when you write new
applications. When you modify existing applications that are written
using these technologies, consider migrating those applications to
ADO.NET.
The following components are considered obsolete:
....
Data Access Objects (DAO): DAO provides access to JET (Access) databases.
This API can be used from Microsoft Visual Basic®, Microsoft Visual C++®,
and scripting languages. It was included with Microsoft Office 2000 and
Office XP. DAO 3.6 is the final version of this technology. It will not
be available on the 64-bit Windows operating system.
.....