Locking out MS Access Users...

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

rich - 24 Feb 2005 15:53 GMT

I have an ASP.NET web application that uses a SQL Server 2000 database. I am
forced to use Impersonation/Delegation with Windows accounts so that we can
audit activity at the database level. However, everyone in the organization
also has MS Access on their desktop. How can I prevent MS Access users from
creating a new .adp and executing any of the stored procedures created
specifically and solely for the web application? I know using SQL Server
application roles (especially for web applications) is BAD!

Thanks!
-rich

Reply to this Message

Adam Machanic - 24 Feb 2005 16:25 GMT

> specifically and solely for the web application? I know using SQL Server
> application roles (especially for web applications) is BAD!

Why are application roles bad?

Signature

Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--

Reply to this Message

rich - 24 Feb 2005 16:37 GMT

Based on MS documentation, the biggest drawback for web applications is the
loss of connection pooling.

See "Building Secure ASP.NET Applications" on the MS site.

> > specifically and solely for the web application? I know using SQL Server
> > application roles (especially for web applications) is BAD!
>
> Why are application roles bad?

Reply to this Message

Adam Machanic - 24 Feb 2005 19:25 GMT

> Based on MS documentation, the biggest drawback for web applications is the
> loss of connection pooling.
>
> See "Building Secure ASP.NET Applications" on the MS site.

Thanks; there is some great info there. I read the section on connection
pooling and application roles (can be found in this page:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/S
ecNetch12.asp )
and I agree with you, given the issues it's probably not a good solution for
a web-based application. I think the simplest solution is, assign
permissions for the web app to hit the stored procedures, and don't give the
Access users any access (no pun intended :) )...

Signature

Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--

Reply to this Message

Steve Thompson - 24 Feb 2005 19:08 GMT

> I have an ASP.NET web application that uses a SQL Server 2000 database. I am
> forced to use Impersonation/Delegation with Windows accounts so that we can
> audit activity at the database level. However, everyone in the organization
> also has MS Access on their desktop. How can I prevent MS Access users from
> creating a new .adp and executing any of the stored procedures created
> specifically and solely for the web application?

While you cannot prevent them creating a new ADP, how (or why) do the Access
users have access to store procedures that would access the SQL Server 2000
db?

Steve

Reply to this Message

rich - 24 Feb 2005 20:14 GMT

The web application uses Integrated security so their Windows accounts
(technically the Windows Groups their user accounts are members of) are SQL
logins. Database Roles have been created with permissions set for the stored
procs, etc. As a result, they can also open Access, create an adp, connect
to the SQL database using Windows authentication, and the sp's, UDF's, etc
show up. Because the database role their user account belongs to has
"execute" permissions for the web application they can then also execute the
sp from Access.

> > I have an ASP.NET web application that uses a SQL Server 2000 database. I
> am
[quoted text clipped - 12 lines]
>
> Steve

Reply to this Message

Sue Hoegemeier - 24 Feb 2005 20:30 GMT

Some places write jobs to kill spids based on application
names connected to SQL Server. That doesn't prevent much as
it works after the fact. I also worked at a couple of places
where we traced who logged in not using the corporate
applications. Sometimes if you'd tell them exactly what they
were doing at what time on what day, they'd be freaked out
enough to discontinue or not go in as much.

-Sue

>The web application uses Integrated security so their Windows accounts
>(technically the Windows Groups their user accounts are members of) are SQL
[quoted text clipped - 21 lines]
>>
>> Steve

Reply to this Message

Ross Presser - 25 Feb 2005 21:59 GMT

> The web application uses Integrated security so their Windows accounts
> (technically the Windows Groups their user accounts are members of) are SQL
[quoted text clipped - 4 lines]
> "execute" permissions for the web application they can then also execute the
> sp from Access.

It would be a pain, but you could alter every stored procedures to check
host_name() against the web server's name, or app_name() against your web
app, and bomb out if you detect unauthorized use. Then remove all
select,insert,delete,update,etc. permissions on tables from the db role,
leaving them only with execute permission on the sp's.

Reply to this Message

rich - 25 Feb 2005 22:39 GMT

Thanks for everyone's replies. All good suggestions. It's unfortunate there
isn't a simpler way to lockout particular applications.

> > The web application uses Integrated security so their Windows accounts
> > (technically the Windows Groups their user accounts are members of) are SQL
[quoted text clipped - 10 lines]
> select,insert,delete,update,etc. permissions on tables from the db role,
> leaving them only with execute permission on the sp's.

Reply to this Message