Application Role Losing Connection? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

Application Role Losing Connection?

Author

Topic

adriek
Starting Member

2 Posts

Posted - 2007-09-06 : 05:59:22

Hi,

I'm testing the use of application roles for security. The customer I work for has still a lot of ASP intranet applications running. We're migrating the databases to a SQL Server 2005 server.

I've changed the connection string to a user without any permissions but to log on. After that I use an application role for permission to select different tables and to execute Stored Procedures.

The first queries do execute but after that I get "Permission denied", like I haven't got the application role anymore.

Any ideas?

Adrian

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-06 : 14:42:20

how did you invoke the application role to set in? in 2000 you need to do it via application, did you check the equivalent in 2005?

--------------------
keeping it simple...

adriek
Starting Member

2 Posts

Posted - 2007-09-06 : 16:28:38

I changed the original connection string in the application to a user with no permissions. After connecting a stored procedure is called: sp_setapprole(@xx=name,@yy=password). (Of course I defined the applicationrole with its name and password in the database beforehand)

It works but just a few times. After that....permission denied. The same connection is used for several queries and stored procedures. It always worked. I think a new (second) connection to the database is established (I somewhere read about that) and this connection is just with the user without permissions.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2007-09-07 : 14:14:18

in your application:
the connection gets disconnected, but I guess you won't be that too sloppy

I had that problem before with a developer who complained that the approle doesn't just work, same thing like what you described, then we searched the code, there was this one procedure that disconnects existing connections and reconnects again and this time no setting of the approle

HTH

--------------------
keeping it simple...

vishualsoft
Starting Member

6 Posts

Posted - 2012-05-14 : 09:36:04

Hi I am using Delphi7 as a front end and SQL Server2008 as a back end.

I am forced to use DSN data source name in my connection name, coz we are using SQL Server 2008 and Oracle 11g as database.

The following problem I am facing is in SQL Server 2008.

So, I would be describing following problem in SQL Server 2008 point of view only.

I am facing the same issue. I am first assigning the TADOConnection component to the TADOQuery component and then using sp_setapprole(@xx=name,@yy=password). (Of course I defined the applicationrole with its name and password in the database beforehand)

For the first time, TADOQuery executes the sql, it would continue executing any sql so lond as I am using the first TADOQuery componnt, but the moment I am using another TADOQuery component it starts giving an error "Permission denied", like I haven't got the application role anymore.

But here one interesting thing is if I disconnect the TADOConnection component and just connect once again without using connection string once again, anoter TADOQuery component starts working, off course, I have used following connection string:

ADOConnection1.ConnectionString := 'Provider = MSDASQL.1; Password = My Password; User ID = My User; Data Source = My DSN Data Source Name';

This connection string I am trying in the sample project, where I am not disconnecting the connection set using TADOConnection component any time once I have connected to the database.

Could any body pl tell me what could be done, it's a so lond time I am trying to sole this problem but not even bit of success.

I need to use the ODBC DSN to connect to the database.

So, pl keep this constraint in mind while helping me.

Thanking You And With Best Regards.

Vishal

vishualsoft
Starting Member

6 Posts

Posted - 2012-05-14 : 09:45:06

Hi HTH,

As you said in the following statement:


there was this one procedure that disconnects existing connections and reconnects again and this time no setting of the approle

You said, "there was this one procedure", you mean to say that "sp_setapprole" procedure. If yes, then this procedure disconnects and reconnects once again.

Then what could be done, I still didn't get it. Could you pl explain me what should I need to do.

Pl explain because I am facing this issue from long time and I am forced to solve this issue.

Expecting To Hear From You Here And With Best Regards.

Vishal

vishualsoft
Starting Member

6 Posts

Posted - 2012-05-21 : 01:10:52

Hi All,

I have got the starnge solutions for the strange problem. Till now it is working perfectly.

As I said, the SQL execution would not give error untill I use any second TADOQuery component. But the moment I use any other TADOQuery rather than first one, I get an

error like, "SELECT permision was denied on object <'Table Name'>......".

I have found two solutions:

I am using DSN as Datya Source, so even if I use SQL Sever or Oracle as a database, so there is no need to make much changes.

Solution1:

I have just assigned the TADOQuery No. 1 to any other TaDOQuery which I am going to use for SQL execution as below:

ADOConnection1.ConnectionString := 'Provider=MSDASQL.1;Persist Security Info=True;'
+ 'User ID= My User ID;Password= My Password;'
+ 'Data Source= My DSN Data Source Name';

ADOConnection1.Connected := True;

ADOQuery1.Connection := ADOConnection1;

ADOQuery2.Close;

ADOQuery2 := ADOQuery1; //Assigning ADOQuery1 to ADOQuery2

ADOQuery2.SQL.Clear;
ADOQuery2.SQL.Add('Select * from Table1');
ADOQuery2.Open; // OR ADOQuery2.Active := True;

In above sample code, there is no effect whether I use ADOQuery1 for execution for any time or not, i.e. for the first time or never, everything works fine.

So, whenever I need to use any other ADOQuery rather than ADOQuery1, I just assign the ADOQuery1 to the respective ADOQuery component before SQL execution i.e. before

assigning SQL to the respective TADOQuery component as below.

ADOQuery_N := ADOQuery1;

Solution 2:

I have used TClientDataSet and TDataSetProvider component with TADOQuery component as follows:

ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('Select * from Table1');

DataSetProvider2.DataSet := ADOQuery1; //As It Is Required, Assigning ADOQuery1 to TDataSetProvider2

ClientDataSet2.SetProvider(DataSetProvider2);
ClientDataSet2.Active := True;

ClientDataSet2.First;

for iCount := 0 to ClientDataSet2.RecordCount - 1 do
begin
ADOQuery1.Close;
ADOQuery1.SQL.Clear;

ADOQuery1.SQL.Add('Select * from Table2 where Column_Name = ' + QuotedStr(ClientDataSet2.FieldByName('Column_N_Name').AsString));

DataSetProvider3.DataSet := ADOQuery1; //As It Is Required, Assigning ADOQuery1 to TDataSetProvider3

ClientDataSet3.SetProvider(DataSetProvider3);
ClientDataSet3.Active := True;

//Workout with ClientDataset3 in your code, then clear the data in it as shown below.
//So, in the loop we could workout freshly with ClientDataSet3.

ClientDataSet3.DisableControls;
ClientDataSet3.EmptyDataSet;
ClientDataSet3.EnableControls;

ClientDataSet2.Next;
end;

Kindly Let me know, if there is anything to say from your side.

With Best Regards.

Vishal

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources