Application Role - how to implement in VB. 1

[donutman]

I've tried posting in the VB forum without much success. Does anyone know how to implement Application Roles when using VB. My connection string to SQL Server doesn't work using an Application Role name as the UserID. You can't add users to an Application Role, so how are you supposed to login with it?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[gmmastros]

donutman, here's an idea for ya. It's completely frivolous and should be immediately discarded. In fact, if you keep reading this post after such a strong caution, well... quite frankly I'd be surprised.

So here's my idea. Run right out and buy a fingerprint scanner for each workstation. Mount it to the monitor and put a big red sticker next to it. On the sticker, print the word "ON". Write your app such that several minutes of inactivity will cause the screen to go black. In order for the black screen to go away, the poor slob in front of the monitor needs to push the "on" button. I believe these fingerprint scanners output a stream of characters. You could test this against a known list of employee fingerprints in a table. If it matches an employee's fingerprint, then you swap out the connection string suitable for that employee. If it's a customer, you swap out the connection string suitable for a customer. If it's the big boss, you could re-format the drive.

Still reading? Well, I have another idea to wipe out world hunger with just a handful of donuts. Best left for another thread I suppose.

 

[donutman]

 

[donutman]

Gmmastros, here's where I'm at. Did more testing and the AR does work fine with Connection Pooling turned off. So the issue becomes what impact will that have on performance. I've tried to google Connection Pooling and have read a fair number of references, but nothing puts the performance hit into perspective. Given that I'm a small shop (40 computers with probably only 10 to 12 users hitting the server at any given moment (but each may hit it with 2 or 3 apps at a time) what would you think about it. Remember, they typically don't terminate the program and restart it. They tend to just leave it open all day long, coming back to it as needed. And I'm not running IIS.
I can deal with the 2 db issue with a hard-coded special user connection when that is necessary...and I'm not sure that I will even need to do that...can't think of an instance where an existing app does that. I do however have several SPs that do that, but they are run as a scheduled job, so they won't be running under an AR.
Regarding your above explanations, they are gellin.
Curious though why you had to actually deny access to the Error table? Was the test user a member of a Role in which it provided elevated privileges to Error table?
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[Catadmin]

You might want to Google ADO Connection Pooling and ASP Connection Pooling also. That's where I got some of my info.

As far as denying permissions to the Error table, it was a test of SQL's "most restrictive permissions apply" policy. And if a user isn't specifically denied rights that are granted to a User Group they are a part of, then they have those rights even though they weren't specifically given them.

For Example. User Catadmin has no specific permissions on the error table, but she is a member of TechPeople domain group which is mapped to a separate SQL Login which grants Insert, Update, Delete permissions on the Error table. But Boss Snoopy, mean boss that he is, doesn't want Catadmin to be able to delete from the Error table. If we don't specifically Deny the Delete permission on that table, she inherits the rights to Delete from TechPeople, which can be messy.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[Catadmin]

I forgot to mention that we're testing user permissions for HIPPA compliance. If we can restrict everything except access to stored procedures, and make the apps do all their work through stored procedures, then it'll be really really hard for hackers, ignorant users or just bad people to get info they shouldn't have access to.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[donutman]

That worked, found 2 new links that helped a bit, but here's what is still lacking: they all say that creating connections is expensive, but nobody puts that into perspective. So I used my test program for AR to open and close 200 connections (in a For Next loop). It did it in 1 second with connection pooling off. Obviously this issue is moot for me...it's for the big boys. Or the little guy running a porn site!

I did run across this in my google and got confused.

3. What happens when all the connections in the connection pool are consumed and a new connection request comes :

If the maximum pool size has been reached and no usable connection is available, the request is queued. The connection pooler satisfies these requests by reallocating connections as they are released back into the pool. Connections are released back into the pool when you call Close or Dispose on the Connection.

They can't be saying that a user is unable to connect to the server and their login request is queued, are they?
-Karl



[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[Catadmin]

I believe that is exactly what they are saying. But that is if SQL Server has a Max # of connections defined. If the Max # of Connections is set at 0, then the server continues handing out connections like candy until the server can't handle anything else. And usually it's the big boys networks that hit that point.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[gmmastros]

donutman, I tend to agree with you. The connection pooling issue is probably moot for you. Connection pooling can be quite important, depending on how you deal with the DB.

If your app creates a connection object when the app starts up, and does NOT close the connection until the app closes. You are maintaining the connection throughout the "life-time" of the app. Therefore, the connection does not get closed and it also does not go in to the connection pool.

If your app create a connection object, logs in to the DB, performs some operation, and then you close the DB connection EVERY time you touch the database, then your connections are getting pooled.

Ex.

Public Function GetDatabaseRecordset(byval SQL as string0 as ADODB.RecordSet
Dim DB As ADODB.Connection
Dim RS as ADODB.RecordSet

Set DB = new ADODB.Connection
db.connectionstring = ....
db.open

Set RS = new ADODB.Recordset
Call RS.Open(SQL, DB,.....)

Set RS.ActiveConnection = Nothing
Set GetDatabaseRecordset = RS

DB.Close
Set DB = Nothing

End Function

If you get it just right, this method will give you disconnected recordset. It also opens and closes the connection to the database every time, therefore utilizing connection pooling. BTW, I don't recommend the above method.

Ultimately, you must consider what's important. The simple answer is.... The app must do what it is supposed to do, and you can't have people standing aroung waiting for the computer to do it. Functionality first. Speed second.

So... If you turn off connection pooling and the performance is acceptable, then you should go that route.

My programmers come to me all the time. I wrote the widget like you wanted me to, but I think I can make it faster. Should I spend more time on it? I ask, how long does it take? If it's "blink of an eye", then 1/2 a blink doesn't matter.

I think your assesment that connection pooling is for the big guys is probably right. It also benefits the little sloppy guy.

Hope this helps.

 

[donutman]

Yes it helps, Catadmin, but the saga continues. I may have to go back to posting in the VB forum for this. Now that I've got the AR working, I tried to implement it in my first real program (security upgrade). I had to convert an ADO data control whose connection string was set at design time. I have to be sure to use the same connection that I opened for the Guest and for which I ran sp_setapprole, right?
Well, there's no way to do that, afik. ADO data controls want a connection string not an active connection. If I use the same connection string I'm back to square one, somehow I'm going to have to run sp_setapprole again on this new connection...how can I do that? I tried just setting the adoDataControl.recordset = adoCommand.exec. It runs but doesn't really work. I HATE SQL SECURITY!
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[Catadmin]

Strange. The guys I'm working with (all programmers) have no AppRole set up for SQL. They're using regular SQL User connections so that they can take advantage of connection pooling and the apps are working fine as far as that part of it goes.

I'll see if I can't wheedle more info out of them on how they have their connection strings set up.



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[donutman]

It's not a problem if you're not using SQL Application Roles. I think I've hit the wall on this. I've googled "using data control with application role" and came across an article about it's use in Access. It basically said, using bound data controls is not possible. I may have to add Application Roles to my axis of evil!
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]