SQL Server 2000 (Application) Role accessed through ADO.NET

From: Shri (subsandnews_at_hotmail.com)
Date: 05/09/03 

Date: Fri, 9 May 2003 07:01:03 -0700

Hello World,

My goal is to use windows authentication only but stop
users from connecting to SQL Server through other MSOffice
tools (like MSAccess link tables) and manipulate data in
the database.

The database role (Application role) claims to answer my
question and give only applications access to the database.

I created a method which takes in an SQL statement as a
parameter and execute it. So inside the method....

1. I establish the connection to the database
using "Integrated Security" (for windows authentication)
through the Connection Object of ADO.NET

2. I execute sp_setapprole stored procedure (providing the
app role name and password that has already been created
on the database I am trying to access).

3. Then I execute the SQL statement passed to the method
using the command object's ExecuteReader method and store
the output in a DataReader.

4. I close the connection and return the contents of the
DataReader.

With the above approach, only one SQL Statement executes.
As soon as I call the method for the second time, the
method fails when it is trying to execute the
sp_setapprole. I get a "General network error. Check your
network documentation" error.

One other observation : When I don't close the connection
or dispose it, I am able to execute the method for only
nine times. The tenth time the method is called, I get the
same error.

My need is that I should be able to call the method any
number of times (that is to call sp_setapprole before I
execute my SQL statement).

My development environment is VB.NET on .NET Framework 1.0
and SQL Server 2000. The underlying operating system is
Windows 2000.

Any ideas somebody.... anybody.... HAAAAALP!!

In advance, I thank you for your help. Also I thank all
those intelligent grey cells for the time and energy they
spent to understand the problem and worked towards the
solution :)

Relevant Pages

  • RE: Logging in in background
    ... wold take all kinds of modifictions as I'd need to be checking that each SQL ... my database and all have connection strings associated with them. ... I suspect that there is also an issue on the SQL Server side as I keep ...
    (microsoft.public.access.modulesdaovba)
  • Re: User not associated with trusted SQL Server connection
    ... > using the osql utility. ... > associated with a trusted SQL Server connection. ... > database or is it just for that instance. ...
    (microsoft.public.sqlserver.security)
  • Re: Is it just me or are there BIG problems with SQLCE 3.0?
    ... has been done with parameterised SQL, ... bit of code into my test which closes and reopens the database ... opening and closing the DB connection is not a good idea. ... // Display all error messages ...
    (microsoft.public.sqlserver.ce)
  • Re: Business objects, subset of collection
    ... SQL only works when the statements are ... all items (invoices) are subscribing for events. ... all items and all items has to execute the criteria evaluation, ... The features of a OO database is basically the same as of a network ...
    (comp.object)
  • Re: Concurrent database access in SQL 2005 Mobile
    ... What version of SQL CE are you using? ... Are you accessing the database from an app written in C++? ... then opens his work forms and it's in those ... It wouldn't seem to me that you'd need a new connection to ...
    (microsoft.public.sqlserver.ce)