July 13, 2008 at 11:48 pm
Ok, i created a database in SQL Server. I also have an app i created that both selects data from the database and writes to the database.
I have a pretty basic understanding of how to add users to a database. But my book says little about application roles. Can i assign the application a ROLE and then that app can access the database as needed? Or do i have to assign users a group and grant that group access to the database as well as grant the app a role?
For example, can i just install the app on everyone's computers. Give the app a role in the db and not provide the users with a SQL Server login or db login?
From what i read, i do think i will need to do both because although users gain access through the application role everything is monitored based on their logins. But if i don't grant the users permission to access the database, then how will SQL Server know who is using the app role.
Here is my general understanding
Using Windows Authentication is best. I can grant access to Windows Groups or individual logins to SQL Server but that does not grant them access to any particular database. I can use exec sp_grantdbaccess to add a Windows Group to the database. Yet i wonder if somehow they will be able to view the information in the database which is what I DON"T WANT OUTSIDE OF THE APPLICATION. I can create an application role and give it access taht i want.
What prevents the users from viewing the SQL database without the application?
To turn the application role on do i have to add "exec sp_setapprole 'EmpTest', {Encrypt N 'password'}, 'odbc'" to my application source code and create a new executable?
When should i turn the role off or should i always leave it on?
Thanks guys!
July 14, 2008 at 8:16 am
Can you explain how the application is programmed to connect to the database. So: what is the connection string used by the application.
Are the users logging in to the application using their own seperate logins or is it not needed to provide credentials to use the application?
When the connection of the application to the SQL-server is the same for each different user, you can create one single login on the SQL server and program the application to use that login. This login needs the Database_Reader and Database_Writer access rights.
July 14, 2008 at 8:23 am
Application Role can sound secure and the way to go but it depends on the application behaviour.
So only users that are connected through a particular application, are only allowed access to specific. But overall the structure is using either SQL/Windows Authentication and granting them access based on the Database Roles.
If there is a Choice, and this is a Inhouse-Custom APP, you can try to stick with Windows Authen as a considered best practise.
Maninder
www.dbanation.com
July 15, 2008 at 4:40 am
Jacob,
Have a look at http://www.sqlservercentral.com/articles/Security/sqlserversecurityprosandconsofapplicationroles/1116/. Keep in mind though that the article was written in 2003, so it pertains to application roles as they were implemented in SQL2K. Things may have changed in SQL2K5.
Regards,
Jan
July 17, 2008 at 6:18 pm
HanShi
Here is my connection string in VB6
connString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=FAE;Data Source=Keoki"
adoConnection.Open connString
Jan Van der Eecken
Thanks for the article. I've read it. I feel it left me hanging a little. I will look over the points again and discuss them separately.
I also have to look at alternatives--the pros and cons of using something other than an app role.
Thanks guys! I'm open to any info you may have.
July 17, 2008 at 11:18 pm
Jacob Pressures (7/17/2008)
HanShiHere is my connection string in VB6
According to the connection string, the application connect to SQL using integrated security. That means the Windows-logins of the user is forwarded by the application to the SQL server.
July 17, 2008 at 11:34 pm
Jacob Pressures (7/13/2008)
Yet i wonder if somehow they will be able to view the information in the database which is what I DON"T WANT OUTSIDE OF THE APPLICATION.
For my understanding is with SQL in "Windows Authentication mode", when users can connect through your application, it is also possible to connect through another application (like MS Access or SSMS)
July 18, 2008 at 7:35 am
I didn't understand the ramifications of what you said. Please explain.
July 18, 2008 at 7:36 am
I took a job several years ago where application roles in combination with Windows authentication (Kerberos) were being used as the standard for web applications. This is my quick summary of my experience:
You must login to the database using some form of authentication. SQL Server or Windows. Note that the user you log in with must have access to the database - not just a server login but a DB login to the DB you want to invoke the approle in.
You must then invoke the Approle.
You then lose the privileges of the login user and gain those of the approle.
It sounds simple enough but there are a couple issues that I am familiar with:
The approle is DB specific, so at this point, your connection CANNOT use resources outside that one database unless you do something like turn on cross database ownership chaining AND you enable the guest account in the other databases you might want to join to. Because remember - the approle is only known in the current database - no other database has a clue who the user is.
ODBC connection pooling must be turned OFF or you'll get errors. The connection is not properly cleaned up and you will be left with connections in your pool that your 'LOGIN' will reuse, but it's state will be such that when you try to invoke the approle you get an error - the approle is already invoked. This would imply also, that you can be the approle in a connection even without knowing it's password if you can construct a connection string so as to cause connection sharing and by chance reuse a connection with the approle invoked. There is a workaround for this connection sharing issue involving storing indicators in the connections to tell whether you've set the approle or not so that if you get a reused connection, you can programmatically tell.
After using them for a while, I think they might have been a good idea, but the features weren't fully developed enough in my opinion.
July 18, 2008 at 7:51 am
Let me add a few comments to my reply.
AppRoles were chosen because of two main criteria.
1. Users were not going to be allowed to have access except through the application. They do that to a degree. The user still have to be able to login to the database to be able to invoke the approle. Maybe they don't have any privileges, but they still have public.
2. But when you combine windows authentication for that initial connection then invoke the approle, SUSER_SNAME() still tells you who the original windows user was. This can be very beneficial for auditing purposes.
But, I've been dealing with them for several years now and because of some of the security holes that inevitably get opened up because of using them (cross database, use of guest) I do not personally know the benefits of using the approle over using a SQL login to prevent direct access to the DB from the users. I read and see what others say about windows authentication is the way to go, and for some reasons it is. But you may have to give a login to everyone at your company just to get that first initial connection before you switch to an approle.
In my opinion we are still missing what we need in a login. I want to authenticate the user using windows and be able to audit activity as such, but prevent the user from connecting except through applications that I deem appropriate.
July 18, 2008 at 10:41 pm
Jane thanks for the reply! It was really good. It was exactly what the article above said. It did offer a HINT at a solution but didn't really go into details.
Ok, if my app users login using Windows Authentication, do they KNOW or KNOWINGLY login to SQL Server?
Ideally, i would like for them to login to thier computers and double click the icon on their desktop lauching the app and then using the app. Do they have to KNOWINGLY activate SS2000/2005?
What is confusing to me is that there are many commercial apps that work with SS and they don't require all this mumbo jumbo! I would think that non-commercial apps would work the same. OR JUST LIKE an app on your computer or with MS ACCESS, the user doesnt even KNOW IT IS THERE.
NOw as far as different DBs are concerned. Right now i see no reason to connect to two different databases using the app. I've never even thought of that nor do i know why i would ever need a different database for similar information. It would have to be a pretty sophisticated app to use/need two databases i suspect--using data in one and moving it or the results to another.
I want to look at each of these concerns, one by one, to make sure i get the point and I'm not missing something. So far i don't see how I'm being ADVERSELY affected. I'm only using one DB and my users are simple users so they don't need permissions outside of the app. I'm very concerned about the ERROR messages and the performance hit because of the connections issues. The article talked about reusing connections and performance hits from closing and reopening connections.
It was good, but it left me wanting. Guys THANKS SO MUCH. ANY WORK AROUNDS BY THE WAY?
July 19, 2008 at 8:54 am
Jacob:
Hopefully a few more answers for you:
0) Your users just login to Windows as they normally do, then run the app. For bad apps, they will also have to login to the app. They should NOT have to login to SQL Server. the app handles that for them.
1) Uusers do not invoke the application role, that is done by the application.
2) The application role is invoked by executing
[font="courier"]exec sp_setapprole {rolename}, {password}[/font]
3) Note that this requires the application to be written to do this, you cannot add it to a 3rd party product after the fact.
4) As Jane mentioned, (2) will cause the original Login/user to be dropped for the application role. In SQL2000 this was permanent, but in SQL2005 this can be undone with [font="courier"]sp_unsetapprole[/font].
5) Although this method (User logs into windows, the app gets into the DB using thier windows credentials, then app switches to AppRole) can be very secure, especially when combined with a "stored procedure only" access policy, hardly any 3rd party SW products use it. Consequently it is used almost exclusively by in-house (customer built) applications.
6) Two DBs: You're right, for 3rd party products, this is a rare concern as they are usually written to be self-contained and not dependent on external resource. However, once you start having multiple customer written applications, this starts to change. Each developer/team "needs" their own database for their application, but by version 2 of their app, corporate mandates will tell them to stop duplicating effort/data and to start using data from other apps(databases). Fortunately, in 2005 you can start to user certifications instead, which can help you around the cross-database issues.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
July 19, 2008 at 10:58 am
The application role and control of same has changed from 2000 in 2005.
Sample code from BOL
DECLARE @cookie varbinary(8000);
EXEC sp_setapprole 'Sales11', 'fdsd896#gfdbfdkjgh700mM'
, @fCreateCookie = true, @cookie = @cookie OUTPUT;
-- The application role is now active.
SELECT USER_NAME();
-- This will return the name of the application role, Sales11.
EXEC sp_unsetapprole @cookie;
-- The application role is no longer active.
-- The original context has now been restored.
GO
SELECT USER_NAME();
-- This will return the name of the original user.
GO
Remarks from BOL
After an application role is activated by using sp_setapprole, the role remains active until the user either disconnects from the server or executes sp_unsetapprole.
Following the above just before your application closes its connection to the database execute the sp_unsetapprole. This should negate the problem with connection pooling
July 20, 2008 at 7:58 pm
Thanks guys that is extremely helpful. So what are the alternatives to application roles? I'm thinking that app roles are still the way to go assuming i don't have problems with connection pooling and other error messages.
Here are some considerations. My app is already built without knowledge of resource pooling. Further, I'm not using stored procedures yet. I'm still using straight SQL from source code. Since I'm a beginner, I plan to change this in future versions. I just can't do everything at once.
I'm a little embarrassed to say that i'm not using stored procedures at this point but I'm not. I'm hoping that what i have will be satisfactory until i can make the necessary changes.
Thanks!
July 20, 2008 at 8:06 pm
Viewing 15 posts - 1 through 15 (of 47 total)
You must be logged in to reply to this topic. Login to reply