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

From: Mandar Naik[MSFT] (mandarn_at_online.microsoft.com)
Date: 05/09/03 

Date: Fri, 9 May 2003 19:48:03 +0530

Hi Shri,

There is a known issue pertaining to this that I am aware of. You can check
the following Knowledge Base article,

http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q229564

The resolution to this is also mentioned in the article.

Hope this helps. 

--
Regards,
Mandar Naik
This posting is provided AS IS with no warranties, and confers no rights.
"Shri" <subsandnews@hotmail.com> wrote in message
news:077e01c31633$6dd2caa0$a301280a@phx.gbl...
> 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: Query Oracle, show results (need help!!)
    ... After the DB connection, put this... ... I am trying to log into an Oracle database, ... Prepare the SQL statement for running and it'll be stored in Oracle buffer ...
    (perl.beginners)
  • Re: Set Restricted_User
    ... to the database, the connection may drop as the database ... the other users but for the app that executes the command to ... Then execute the command and then try ...
    (microsoft.public.sqlserver.security)
  • Re: SQL Server 2000 (Application) Role accessed through ADO.NET
    ... >> The database role claims to answer my ... >> through the Connection Object of ADO.NET ... Then I execute the SQL statement passed to the method ...
    (microsoft.public.sqlserver.security)
  • Re: How to get ODBC the status of connection?
    ... connection is still established when I execute a sql statement. ... For ODBC ver. ... error which occurs when the SQL statement is prepared or executed. ...
    (microsoft.public.data.odbc)
  • Re: update notification
    ... > If I have a change in a row in a table in a database, ... SQL Server, as long as all DBMS expects you to do your required tasks ... connection) will actually sleep until you provide further workloads to be ... and execute it.. ...
    (microsoft.public.sqlserver.msde)