Blocking MS Access from linking tables...

Good morning...
I have an Access front end that uses SQL Server linked tables. SQL Server
uses Windows authentication. I have one Windows group that all Access users
are a member of. I added that group to SQL Server logins and gave it
public, datareader, and datawriter rights to the one database that's used.
My front end is locked down, but I want to stop users from creating a new
.mdb and linking SQL Server tables through DSNs or ADO connections or even
just importing the links from the actual front end.. I've tried setting the
"denydatareader" security policy - that keeps the SQL tables from being seen
in the import/link list- but also blocks read rights from the actual front
end database. I could set an Access database password on the front end to
block importing the links, but that only solves one of the three problems
and I want to stay away from Access security altogether.
Is there a way to stop users from creating their own DSNs or connection
objects or linking tables while still using Windows authentication?
Thanks.
Matthew Wells
MWells@.FirstByte.netNo. If you're using Windows authentication, and have granted
users/roles permissions on the base tables, then they can get at the
data no matter what tool they use. An alternative would be to revoke
permissions to public on the tables, and create an Access application
that does not use linked tables, but instead uses pass-through queries
to execute stored procedures. This is a lot more work since you'll
need to create an unbound FE, but it can be done. Only users who are
comfortable working with stored procedures would be able to get at the
data. Another option would be application roles, but they are a really
poor choice for linked table apps. see
http://support.microsoft.com/defaul...;EN-US;Q229564.
--Mary
On Thu, 28 Oct 2004 13:54:05 GMT, "Matthew Wells"
<MWells@.FirstByte.net> wrote:

>Good morning...
>I have an Access front end that uses SQL Server linked tables. SQL Server
>uses Windows authentication. I have one Windows group that all Access user
s
>are a member of. I added that group to SQL Server logins and gave it
>public, datareader, and datawriter rights to the one database that's used.
>My front end is locked down, but I want to stop users from creating a new
>.mdb and linking SQL Server tables through DSNs or ADO connections or even
>just importing the links from the actual front end.. I've tried setting th
e
>"denydatareader" security policy - that keeps the SQL tables from being see
n
>in the import/link list- but also blocks read rights from the actual front
>end database. I could set an Access database password on the front end to
>block importing the links, but that only solves one of the three problems
>and I want to stay away from Access security altogether.
>Is there a way to stop users from creating their own DSNs or connection
>objects or linking tables while still using Windows authentication?
>Thanks.
>Matthew Wells
>MWells@.FirstByte.net
>|||I read that article. It seems to apply only to ADO conenctions. Aren't
linked tables DAO? Does connection pooling work the same way? This is a
database that was converted from Access to SQL Server. We have to lock down
the data from any outside attempts to get it. I don't want to use SQL
authentication because I don't want to maintain two sets of security logins.
I know that using an Access form can create multiple SPIDs on SQL Server
(combo box rowsources et al). What is the downside of using Application
Roles?
Thanks.
Matthew Wells
MWells@.FirstFleet.com
"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
news:vh62o051su3gi6fsaljgvmh1me12b8fu8p@.
4ax.com...
> No. If you're using Windows authentication, and have granted
> users/roles permissions on the base tables, then they can get at the
> data no matter what tool they use. An alternative would be to revoke
> permissions to public on the tables, and create an Access application
> that does not use linked tables, but instead uses pass-through queries
> to execute stored procedures. This is a lot more work since you'll
> need to create an unbound FE, but it can be done. Only users who are
> comfortable working with stored procedures would be able to get at the
> data. Another option would be application roles, but they are a really
> poor choice for linked table apps. see
> http://support.microsoft.com/defaul...;EN-US;Q229564.
> --Mary
> On Thu, 28 Oct 2004 13:54:05 GMT, "Matthew Wells"
> <MWells@.FirstByte.net> wrote:
>
Server[vbcol=seagreen]
users[vbcol=seagreen]
used.[vbcol=seagreen]
even[vbcol=seagreen]
the[vbcol=seagreen]
seen[vbcol=seagreen]
front[vbcol=seagreen]
to[vbcol=seagreen]
>|||Yes, connection pooling works the same way. If you are serious about
locking down the SQL Server database, then DO NOT use Access as a
front-end unless you use it in an unbound scenario as I described
below. You will need to revoke or deny permissions on the tables and
create parameterized stored procedures for all DML operations so that
users can interact with the data only through your stored procedures,
which are executed using a least-priviledged account.
Application roles are intrinsically insecure because you must store
the password that activates them on the client, where it can be
discovered by a determined attacker. If you create an unbound
application that executes under least priviledges, then there likely
won't be much penalty for using them (other than performance) or much
harm if the password is uncovered, but then there's not much benefit,
either. Using Windows authentication is more secure than SQL logins,
and if all users belong to roles that have extremely restricted
permissions that only allow them to execute parameterized stored
procedures, then that's about the best you can do. You can also
provide additional verification and validation in your stored
procedure code (for example, only allowing users to access rows that
they "own").
There is code and discussion of the techniques involved in writing an
unbound Access application in the book described in my sig. It's a lot
of work, but if security is a priority then you have to do it. Access
was designed to be easy to use, not secure.
-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446
On Mon, 01 Nov 2004 14:29:21 GMT, "Matthew Wells"
<MWells@.FirstByte.net> wrote:

>I read that article. It seems to apply only to ADO conenctions. Aren't
>linked tables DAO? Does connection pooling work the same way? This is a
>database that was converted from Access to SQL Server. We have to lock dow
n
>the data from any outside attempts to get it. I don't want to use SQL
>authentication because I don't want to maintain two sets of security logins
.
>I know that using an Access form can create multiple SPIDs on SQL Server
>(combo box rowsources et al). What is the downside of using Application
>Roles?
>Thanks.
>Matthew Wells
>MWells@.FirstFleet.com
>"Mary Chipman" <mchip@.online.microsoft.com> wrote in message
> news:vh62o051su3gi6fsaljgvmh1me12b8fu8p@.
4ax.com...
>Server
>users
>used.
>even
>the
>seen
>front
>to
>

Blocked transaction problem

Hello,

I am trying to execute next query, but when doing it, TABLE1 locks and it does not finish.

SERVER2 is a linked server.

BEGIN TRAN
INSERT INTO TABLE1
SELECT * FROM SERVER2.DATABASE2.DBO.TABLE2 WHERE TAB_F1 IS NULL
COMMIT TRAN

I have same configuration in other 2 computers and it works ok.

What is the problem?

Thank you!!

This should not lock your transaction. But depending on the server you are using it can be that all data will retrieved from the linked server first to do a filter on the calling server. That will take more time than just doing the Select on the remote server.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Hello,

there are only 3 rows on remote server.

Both servers are SQL Server 2000.

|||What about if you run the thing without any transaction ? Is it the same problem ? Do you see any locks on the table during the stale time ?

HTH, Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Hello,

if I run it without any transaction it finishes very fast.

But I want to know the reason of the problem, because I have one place that runs it this way, and I can not install a new one with same queries.

Thank you.

|||See which locks are allocated when using the command with transactions to see where the blocking is based on.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

You are essentially doing a distributed transaction because of the use of DML with linked server table. So the delay might be attributed to network issues. Check the MSDTC configuration including network configuration to make sure everything is fine. You can also see the transaction statistics in the MSDTC console (Component Services applet) to see the average response time. If that is close to your execution time then the problem is external to SQL Server and you should ask in the Windows newsgroups or search MSKB for optimizing MSDTC setup.

If the problem is not related to MSDTC, then you can determine the wait types in SQL and troubleshoot the issue. Run following:

1. DBCC SQLPERF(WAITSTATS, 'CLEAR') WITH NO_INFOMSGS

2. your current batch

3. DBCC SQLPERF(WAITSTATS) WITH NO_INFOMSGS

Now, based on the wait type you can figure out what is the cause of the delay. Search MSKB articles for more details on using DBCC SQLPERF.

|||

Hello,

I can not see anything because the execution of the query never ends.
I leave up to 30 minutes and it does not end.

I can execute the query without a transaction and it finishes in a few miliseconds. So, it is posible to query other server.

Could it be a problem with server names?
I mean, the name registered at SQL Server Enterprise Manager, the one at Linked Server and aliases (SQL Server Client Network Utility).

Currently all of them are the same, but at SQL Server installation time the server name was a diferent one.

Thanks!!

|||

Hi!

I have the same problem!!

A simple select againts a second server with a TRAN

BEGIN TRAN
SELECT * FROM SERVER2.DATABASE2.DBO.TABLE2
COMMIT TRAN

execution of the query never ends

Without TRAN it finishes in a few miliseconds

someone have any ideas to resolve this problem?

thanks in advance.