|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Application Role vs One SQL LoginWhat are the advantages of using an Application Role instead of a
single SQL Server login account? I'm new to the concept of Application Roles, but it seems to me like they would be functionally equivalent to having a single SQL login that my application uses to connect to the database. With an application role I would still have to initially establish the connection using either NT or SQL authentication, and also go through the extra step of calling sp_setapprole every time I open a connection. This is in reference to .Net 2.0 & SQL Server 2005, but could also apply to 1.x and 2000. Any thoughts or advice? Thanks! -MJ
Show quote
"Mike" <mj2***@yahoo.com> wrote in message An Application Role allows your users to connect to the database with a news:1152739109.558844.90100@h48g2000cwc.googlegroups.com... > What are the advantages of using an Application Role instead of a > single SQL Server login account? I'm new to the concept of Application > Roles, but it seems to me like they would be functionally equivalent to > having a single SQL login that my application uses to connect to the > database. With an application role I would still have to initially > establish the connection using either NT or SQL authentication, and > also go through the extra step of calling sp_setapprole every time I > open a connection. > > This is in reference to .Net 2.0 & SQL Server 2005, but could also > apply to 1.x and 2000. > login that does not have enough permissions to run the application, and then switch to a security context that can. This can be used to prevent users from connecting directly to SQL Server and accessing the tables and stored procedures that the application uses. For web applications, it's not too compelling since you're often connecting with a special application account anyway. David Thanks David for your reply!
I agree with you there is not much reason to use an application role in a web app. Even in a Windows client app, which happens to be what I'm currently working on and what prompted my question, it still has not clicked for me why I would want to use an application role over a SQL Server login. As long as the connection string containing my SQL Server login is properly encrypted in app.config, and permissions on the database objects are configured such that only my SQL Serer login account has access to them, then I don't see how a user could gain direct access to the tables, procedures, etc. any more easily than when using an application role. After all, couldn't a user who (1) has the limited privileges needed to make the initial connection to the database, and (2) knows the application role name and associated password, directly access the database, issue sp_setapprole and then proceed to do just as much damage as one who knows the SQL Server login name and password? Also, since the role name and its password would have to be encrypted and stored in app.config, how is that any better/more secure than a SQL Server login and password encrypted and stored in app.config? Thanks again! Any more explanation on this subject would be greatly appreciated! Thanks David for your reply!
I agree with you there is not much reason to use an application role in a web app. Even in a Windows client app, which happens to be what I'm currently working on and what prompted my question, it still has not clicked for me why I would want to use an application role over a SQL Server login. As long as the connection string containing my SQL Server login is properly encrypted in app.config, and permissions on the database objects are configured such that only my SQL Serer login account has access to them, then I don't see how a user could gain direct access to the tables, procedures, etc. any more easily than when using an application role. After all, couldn't a user who (1) has the limited privileges needed to make the initial connection to the database, and (2) knows the application role name and associated password, directly access the database, issue sp_setapprole and then proceed to do just as much damage as one who knows the SQL Server login name and password? Also, since the role name and its password would have to be encrypted and stored in app.config, how is that any better/more secure than a SQL Server login and password encrypted and stored in app.config? Thanks again! Any more explanation on this subject would be greatly appreciated! Thanks David for your reply!
I agree with you there is not much reason to use an application role in a web app. Even in a Windows client app, which happens to be what I'm currently working on and what prompted my question, it still has not clicked for me why I would want to use an application role over a SQL Server login. As long as the connection string containing my SQL Server login is properly encrypted in app.config, and permissions on the database objects are configured such that only my SQL Serer login account has access to them, then I don't see how a user could gain direct access to the tables, procedures, etc. any more easily than when using an application role. After all, couldn't a user who (1) has the limited privileges needed to make the initial connection to the database, and (2) knows the application role name and associated password, directly access the database, issue sp_setapprole and then proceed to do just as much damage as one who knows the SQL Server login name and password? Also, since the role name and its password would have to be encrypted and stored in app.config, how is that any better/more secure than a SQL Server login and password encrypted and stored in app.config? Thanks again! Any more explanation on this subject would be greatly appreciated!
Show quote
"Mike" <mj2***@yahoo.com> wrote in message It's like putting two locks on a box. Your user has one key (their news:1152753803.453779.296760@h48g2000cwc.googlegroups.com... > Thanks David for your reply! > > I agree with you there is not much reason to use an application role in > a web app. Even in a Windows client app, which happens to be what I'm > currently working on and what prompted my question, it still has not > clicked for me why I would want to use an application role over a SQL > Server login. > > As long as the connection string containing my SQL Server login is > properly encrypted in app.config, and permissions on the database > objects are configured such that only my SQL Serer login account has > access to them, then I don't see how a user could gain direct access > to the tables, procedures, etc. any more easily than when using an > application role. > > After all, couldn't a user who (1) has the limited privileges needed > to make the initial connection to the database, and (2) knows the > application role name and associated password, directly access the > database, issue sp_setapprole and then proceed to do just as much > damage as one who knows the SQL Server login name and password? Also, > since the role name and its password would have to be encrypted and > stored in app.config, how is that any better/more secure than a SQL > Server login and password encrypted and stored in app.config? > identity), and you have the other (the app role password). If the user finds the other key, then they can open the box. The scenario where this is pretty compelling is when you use (as you should) windows integrated authentication for your users. You create an AD group for your applciation users, and users get access to the application through the normal network security administration. This gives the users single sign-on, eases and centralizes security administration for your applicaiton, and prevents a lost SQL password or a terminated employee from being able to access the application. However these applciation users would be able to connect to the database with other tools and do whatever the applciation does. Application Roles prevents this by placing a second level of security around the application's transactions. David
Show quote
"Mike" <mj2***@yahoo.com> wrote in message It's like putting two locks on a box. Your user has one key (their news:1152753803.453779.296760@h48g2000cwc.googlegroups.com... > Thanks David for your reply! > > I agree with you there is not much reason to use an application role in > a web app. Even in a Windows client app, which happens to be what I'm > currently working on and what prompted my question, it still has not > clicked for me why I would want to use an application role over a SQL > Server login. > > As long as the connection string containing my SQL Server login is > properly encrypted in app.config, and permissions on the database > objects are configured such that only my SQL Serer login account has > access to them, then I don't see how a user could gain direct access > to the tables, procedures, etc. any more easily than when using an > application role. > > After all, couldn't a user who (1) has the limited privileges needed > to make the initial connection to the database, and (2) knows the > application role name and associated password, directly access the > database, issue sp_setapprole and then proceed to do just as much > damage as one who knows the SQL Server login name and password? Also, > since the role name and its password would have to be encrypted and > stored in app.config, how is that any better/more secure than a SQL > Server login and password encrypted and stored in app.config? > identity), and you have the other (the app role password). If the user finds the other key, then they can open the box. The scenario where this is pretty compelling is when you use (as you should) windows integrated authentication for your users. You create an AD group for your applciation users, and users get access to the application through the normal network security administration. This gives the users single sign-on, eases and centralizes security administration for your applicaiton, and prevents a lost SQL password or a terminated employee from being able to access the application. However these applciation users would be able to connect to the database with other tools and do whatever the applciation does. Application Roles prevents this by placing a second level of security around the application's transactions. David
Show quote
"Mike" <mj2***@yahoo.com> wrote in message It's like putting two locks on a box. Your user has one key (their news:1152753803.453779.296760@h48g2000cwc.googlegroups.com... > Thanks David for your reply! > > I agree with you there is not much reason to use an application role in > a web app. Even in a Windows client app, which happens to be what I'm > currently working on and what prompted my question, it still has not > clicked for me why I would want to use an application role over a SQL > Server login. > > As long as the connection string containing my SQL Server login is > properly encrypted in app.config, and permissions on the database > objects are configured such that only my SQL Serer login account has > access to them, then I don't see how a user could gain direct access > to the tables, procedures, etc. any more easily than when using an > application role. > > After all, couldn't a user who (1) has the limited privileges needed > to make the initial connection to the database, and (2) knows the > application role name and associated password, directly access the > database, issue sp_setapprole and then proceed to do just as much > damage as one who knows the SQL Server login name and password? Also, > since the role name and its password would have to be encrypted and > stored in app.config, how is that any better/more secure than a SQL > Server login and password encrypted and stored in app.config? > identity), and you have the other (the app role password). If the user finds the other key, then they can open the box. The scenario where this is pretty compelling is when you use (as you should) windows integrated authentication for your users. You create an AD group for your applciation users, and users get access to the application through the normal network security administration. This gives the users single sign-on, eases and centralizes security administration for your applicaiton, and prevents a lost SQL password or a terminated employee from being able to access the application. However these applciation users would be able to connect to the database with other tools and do whatever the applciation does. Application Roles prevents this by placing a second level of security around the application's transactions. David  |
|||||||||||||||||||||||
Other interesting topics