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, I've done a little googling. Seems that in order to use an application role, you must first log in to the database with a *regular* login and password. Then you issue the SQL command sp_setapprole. Presumably, the idea behind this is... depending on the application, you may want to have different permissions to various database objects. For example, you could have one application used for ordering donuts. This application should have permissions to write to the donut_order table. You then have another application used for completing orders. This application should have read access to the donut_order table, but write access to the donut_delivery table. You may think... What's so special about that? Well, if DonutBoss logs in to the "Ordering Donut" application (using his user name and password), he will still NOT have write permissions to the donut_delivery table.

So, when you set the app role, you inherit the permissions of the app role, irregardless of the permissions originally granted or denied to the user logging in.

Hope this helps.

 

[gmmastros]

BTW, here's a good article I found. Thought it might interest you.

http://www.sqlservercentral.com/col...rversecurityprosandconsofapplicationroles.asp

 

[donutman]

Thanks gmmastros! I'll have to play with that and see how it works. I can't believe I didn't google it. I did keyword searches within TT, but didn't find anything.
So the strategy would be to have the app login as a very low level user, perhaps Guest with hard a hardcoded connection string. Then have the app execute that SP after the user enters the App Role password, right?
-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]

Yeah, that was the impression I got from my brief research. There are some serious connection pooling issues if you decide to persue this strategy. Personally, I would shy away from it altogether.

I do appreciate your security concerns, and will give it some thought.

If I understand correctly (from other threads), your concern is with the win98 boxes. This is why you don't want to use NT Authentication. I'm no expert on NT Authentication, but I'm pretty sure that Windows 98 can log in to a domain, so NT Authentication should work. I recommend that you pursue this a little further before commiting to application roles.

 

[miq]

hi,
well authenticating your application through a guest or a sysadmin doesn't make any difference. The thing to remember is as soon you logon switch to approle and only permissions set for approle will be visible to the applicaiton. No first login details are applicable anymore.


B.R,
miq

 

[donutman]

Miq, but the advantage of using a low-level login is that you don't lose security by hardcoding the password into the source code which could be seen by other programmers etc.
Gmmastros, I'm not sure I understand the connection pooling issue. Do you mean that all connections made with the initial login name will get pooled? What does that imply?
-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]

 

[miq]

hi,
Ofcourse, i fully agree about using dummy harmless account as the starting point which is also a best practices thing. When i made above post i only wanted to
state that you don't need to worry about the permissions assign to starting account because you cannot use its power anymore as soon as you switch to approl.
YOu have genuine concerns about password leakage go for look-a-like-guest account.



B.R,
miq

 

[gmmastros]

donutman, here's the way I understand connection pooling and how it relates to application roles.

First, let me explain connection pooling. It's really cool, and the best part... it's on be default when you use ADO. When you *initially* connect to a database, you must get a small piece of the servers resources to accomodate the connection. There is a time hit associated with getting the servers resource. When you close the database connection, the servers resources are NOT instantly cleared, it goes in to a 'pool'. If another database connection is requested within a short period of time, the resource comes from the 'pool' instead of creating new server resources. Using the connection pool is faster than acquiring new resources, so... connection pooling is faster. I should point out that the connection will ONLY come from the pool if the connection is identical i.e. the same connection string.

When you use application roles, you first log in to the database (getting server resources). Then you issue the sp_setapprole command. When you close your database connection, this resource gets thrown in to the pool. Now, imagine you hit the database again, the connection will get re-used. Remember, it's still using an app role, but you don't know that, so you issue the sp_setapprole command again. The second time you issue the command, you will get an error. Apparently, microsoft's solution to this problem is to disable connection pooling. I guess the philosophy is... "slow but always works is better than fast but only sometimes works".

If your app connects to the database at start up, and does not close the database until the app closes, this may not be an issue for you. If you *normally* keep your database closed, then you are already benefitting from connection pooling.

I hope this makes sense, and that I got all the particulars correct.

 

[donutman]

The second time you issue the command, you will get an error. Apparently, Microsoft's solution to this problem is to disable connection pooling.

So if the 2nd time I open a connection with the same login name it will already be in the previous App Role with only those rights? Well if sp_setapprole returns a specific error code I could catch that knowing that the rights have been set, no?
Are you saying that if I tried to use a different App Role that it would also fail? If so then would this be an acceptable strategy:
Always use a guest type login associated with only one App Role and always use that App Role with that login. If the SP fails with the appropriate error, then proceed.
-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]

 

[JamesLean]

Karl, out of interest why do you want to use an app role? From reading your comments it sounds like the app will always use the same login to connect initially (for which you will be creating a specific login with minimal rights) and then activating the app role.

If this is the case, why don't you just assign the required permissions to the account the app is logging in with?

App roles are only really useful when each user is using their own login to connect but you want to overwrite their normal permissions just through the app.

--James

 

[donutman]

Here's the reason: The AR approach adds an extra layer of security although admittedly not a very large extra layer. You cannot log into EM or QA with AR credentials. Almost all of my employees will know the AR password, but they can't do much with it outside of the application. The guest pass that the program uses to make the initial login will have very little in the way of rights, so hardcoding that password into the source code won't be a security hole either. I do admit that a programmer or hack could use the public AR credentials to write a VB program that opened up my db to destruction, but there's a limit to what I can do to make my system secure. And they would have to know the guest password, so a strong guest password that is hardcoded would make it difficult for wouldbe hacks, i.e. they would need to have gotten access to my source code and the semi-public AR password.
-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]

 

[donutman]

Gmmastros, I failed to address your point about W98. I think you're right, my posts from another thread about that issue is probably a red herring. The real problem in my particular case is that I have too many computers AND users that are floaters. It would be unrealistic to have employees (and customers) log out and log into the domain each time they use the computer. Our retail clerks often do quick searches for in-store customers.
So what I've done is made permanent logins with a 3rd party program (Fortress) to lock-down the desktop. So NT authentication would create a big hole in SQL Server security.
I hope to test your sp_setapprole today or tomorrow...I'll let you know how it goes. OMG, I just noticed the link you gave me! You must have slipped that in at the same time as I was replying to your first post.
-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]

 

[donutman]

Gmmastros, just finished reading that article...wish I could give you another star. However, that may kill my idea. The very first application that I wanted to put this on (fortunately) uses 2 databases.
Otherwise my strategy would work...provided that I can in fact still use resource pooling, unless I abandon that which may be ok since I don't have very many users. Also it's unlikely that, with the way we operate, that every connection made during the day is held open by someone at all times. Employees not only do not log out of their workstation, but they commonly leave the application running, because the next person is highly likely going to use the same one.
-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]

Have you considered having 2 seperate connection objects within the app. One for each database. Doing that, you could even have each connection object using a different application role (if you wanted to). I don't know if that will work for you, but it is something else to think about.

 

[donutman]

Did the testing. I couldn't find a way to circumvent the 2 database problem using only one connection. You solution would work to a point, but not if you need to work with both dbs at once (join, inserts from one to the other etc.).
My idea of catching the error and continuing on with the modified guest permissions doesn't work. And trying to re-apply the AR fails as the article described.
Even if I drop connection pooling, I still have the 2 db issue! The author appears to be right. AR's aren't of much value. Back to the drawing board.
-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]

Hey,

I recently did research on the whole app role thing and there is enough data on the net that indicates it does NOT put its connection back in the pool after it has been closed. In fact, my research indicated that a new connection would open up every time a new connection was established (instead of reusing stuff in the pool).

On the other hand, I discovered something else. I had created a regular domain user account with Log on as a service and Act as part of the Operating System rights for a different test. I took this account, mapped it to a regular SQL Login, granted permissions to Stored procedures ONLY on this account and was able to use it.

Of course, our app only refers to SQL SPs to do all its processing. It doesn't do any on its own for security reasons. Bonus note: When I denied all other permissions to the tables themselves, but left the Exec on the SPs, the user login still worked. And because it was using a User login, not an App Role, connection pooling seems to be working. Of course, we've only tested lightly. We're about to go with a full force test in the next week or two once we nail down which SPs go with which app.

Hope this helps.



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

 

[donutman]

I recently did research on the whole app role thing and there is enough data on the net that indicates it does NOT put its connection back in the pool after it has been closed. In fact, my research indicated that a new connection would open up every time a new connection was established (instead of reusing stuff in the pool).

I tested the Guest Login approach by opening a connection, executing sp_SetAppRole, closed the connection, re-opened the connection.
Then at that point I tried both: accessing a table with the AppRole rights, and first re-executing sp_SetAppRole then accessing the table. Neither method worked. Now that I think about it, I didn't destroy the connection object after closing it, maybe that made a difference. But in any case you are saying that the AR technique just doesn't cut it, right?

On the other hand, I discovered something else. I had created a regular domain user account with Log on as a service and Act as part of the Operating System rights for a different test. I took this account, mapped it to a regular SQL Login, granted permissions to Stored procedures ONLY on this account and was able to use it.

Sorry, that's over my head. I'm just beginning to learn about security, "log on as a service", "OS rights", etc. I know the terms but don't understand the implications. What was the point of that exercise? Is there something in it that I should consider using in my case?
-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]

Well, if your app only changes data in the DB by using SQL Server Stored Procedures, you can use a User login which will maximize your connection pooling instead of building up usless connections. When you go to Users and right click the User, All Tasks -> Manage Permissions, you can then apply Exec permissions on all the SPs your app uses and deny everything else to lock down what the Login in question can do.

Log in as a Service & Act as Part of the Operating System are Windows Domain Security Policy rights that have to be granted through your Network Admin or the Server -> Domain Security Policy -> User Rights Assignments. Or, if you're workgrouping instead of using a Domain, go to the Local Security Policy on your Server and you'll find it under Local Policies -> User Rights Assignment.

OOPS. Just found a Caveat to the SP permissions issue. If the Stored Procedure itself uses the Exec or Execute command, the Execute command runs under the security rights of the SQL Login. So if Insert, Select, etc. are denied on a table which is accessed using a SP which in turn uses the Exec command, then it will fail. On the other hand, if non of your SPs use the Exec command (just do the Insert/Select/Update...etc. without the Exec command) then they work just fine under a Login with all Table permissions denied but the SP EXEC permission granted.

...What we did to test this. Created simple table called Error with 1 text column (we already had this table with other columns. Either use one you have that is simple or create a new one).

Created stored procedure with a

Create Procedure ErrorTest_SP
Insert Error
Set TextCol = "testing"

Went to Database -> Users-> Rightclick Login name for popup -> All Tasks -> Manage Permissions. Denied all permissions on Error table. Granted EXEC permission on ErrorTest_SP.

Logged into SQL Server with User and ran Exec ErrorTest_SP.

Went back and did a Select * from Error and found our "testing" inserted.

But if your application actually does updates/inserts/deletes directly from the application instead of calling existing SPs within SQL, this probably won't work for you.

Did I confuse you even worse or did I make any sense?



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

 

[donutman]

Before I attempt to check my understanding of what you've explained, let's summarize our conclusions about the use of ARs in my situation.
Have we determined that ARs will not work for both of the following reasons:

[ul][li]Joins between two dbs won't work even with two connections.

[/li][li]The 2nd login of the Guest account will crash the program that attempts to use the AR.[/li][/ul]

Or can the 2nd problem be alleviated by turning off connection pooling? I might be able to live without the first, but the 2nd has to be fixable.
-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]