July 20, 2007 at 2:04 pm
FYI - If you are using app role security with reporting services, you need to use a custom data extension on the reporting server. I've got some sample C# code on my blog about how to do it. (Although fairly simple to do, it takes an enormous amount of boiler plate code to do. If you download the boiler plate from my blog and start with that, it is a pretty quick process.)
I am one of the few guys who absolutely LOVES SQL app role security, and I'm planning a few more blog posts about App Role Security and connection pooling. The first about code patterns needed to get basic .NET connection pooling working, and the second on using custom connection pooling (where you keep the connection open without issuing an sp_unsetapprole and returning it to the pool). Be patient with me, I'm a little behind in my blog because I have a < 3wks old infant at home that is taking up a lot of my energy.
If anyone is having trouble with app roles, they are more than welcome to contact me.
Warm Regards,Greg Wilsonsolidrockstable.com
July 23, 2007 at 7:58 am
"Loosing your server role rights, such as sysadmin, after activating an activation role seems like a non-issue. "
This is absolutely true. I can't think of a reason why anyone should be accessing an application with an ID that has sysadmin rights in SQL Server. Any ID , and by extension the user of that ID, that has sysadmin rights in SQL Server should have minimal access to data via the application. The sysadmin ID's, particularly on production servers, should only be used to administer SQL, not do anything else. All of our DBA's have separate ID's for sysadmin access, and they use either Citrix or RPC to get to the servers.
July 25, 2008 at 4:35 am
Brian,
Awesome article!
Most of the apps I've worked with use an intenal security mechanism and connect to the DB via 1 application login...
I think application roles have thier place, just have not come across too many instances where I've seen them used.
Mark
July 25, 2008 at 7:52 am
Creio que nenhuma novidade foi acrescentada aqui.
Realmente Application Roles é muito pouco utilizada e difundida.
July 25, 2008 at 8:41 am
I don’t think there is much use for Application roles except for client server applications.
Most of our applications are web based and end-users do not have logins in the database. Access to the database is only given to the service account that the web site is running under, and this account does not have any elevated privileges. Security is handled by limiting access to the web app to specific Windows groups. Finer grained security is handled within the application by letting an application administrator grant or revoke access to specific application functions.
July 25, 2008 at 9:38 am
I've used application roles in the past to allow users read only access via their login (actually I use AD groups so only need to create them once) for reporting purposes, and restrict actual data updates to be via the application only. This pays dividends when installing a system at a site that allows users create their own reports using Access for instance. Unless an organisation is extremely strict about what people can and can't install on their desktop, then it is incredibly easy to download an app and poke around a database which grants access to your windows login. It is still possible to find out who the user is on the end of an approle, so tracking down deadlocks etc is not a great problem either.
The ability to lock a database down in this manner, and still allow reporting access for power users makes approles in my opinion extremely useful.
July 25, 2008 at 10:00 am
Yes, Web applications that I see these days all seem to use a single generic login which is stored, hopefully encrypted, into web.config.
Shouldn't the SQL 2005 implementation of application roles permit these web app developers to use integrated authentication? I guess that depends on the app as well - guest users coming from www won't have domain accounts.
July 25, 2008 at 11:01 am
Nice article, Brian!
I share your opinion about application roles and would like to give you one workaround how users can not access the database outside the application without using application roles.
You say: "If I grant INSERT and UPDATE rights to the users via a normal database role, that means these particular users could make changes in TroubleTickets through Microsoft Access, Query Analyzer, Enterprise Manager, or some other tool. "
To work around this problem, one of the major CRMs uses this: they change the user password on the way to the database. The user uses his SQL Server standard login to login the the application and the application submits the modified password to the database. The passwords get created and reset through the application, so the user never even knows his real database password.
One catch here: when the password gets created or modified the real database password could be traced with the Profiler. To run SQL Server Profiler, users must be granted the ALTER TRACE permission per BOL. They can also get the changed passwords from the trace file if the traces are set up and the user has access to the folder where the trace files are stored and has knowledge how to read them etc. But: if the user is that smart maybe he has to be the admin on this server for the first place. 🙂
Regards,Yelena Varsha
July 25, 2008 at 11:04 am
reuben.anderson (7/25/2008)
Yes, Web applications that I see these days all seem to use a single generic login which is stored, hopefully encrypted, into web.config.Shouldn't the SQL 2005 implementation of application roles permit these web app developers to use integrated authentication? I guess that depends on the app as well - guest users coming from www won't have domain accounts.
It depends on the location of the web server. If the web server is in the DMZ, it shouldn't be on a trusted domain. That means no Windows authentication. With that said, application roles can be used with either.
K. Brian Kelley
@kbriankelley
July 25, 2008 at 11:05 am
Yelena Varshal (7/25/2008)
One catch here: when the password gets created or modified the real database password could be traced with the Profiler. To run SQL Server Profiler, users must be granted the ALTER TRACE permission per BOL. They can also get the changed passwords from the trace file if the traces are set up and the user has access to the folder where the trace files are stored and has knowledge how to read them etc. But: if the user is that smart maybe he has to be the admin on this server for the first place. 🙂
Profiler or a server side trace should hide anything using ALTER LOGIN or sp_password, though. Are you running with a particular trace flag or something like that?
K. Brian Kelley
@kbriankelley
July 25, 2008 at 11:09 am
Michael Valentine Jones (7/25/2008)
I don’t think there is much use for Application roles except for client server applications.Most of our applications are web based and end-users do not have logins in the database. Access to the database is only given to the service account that the web site is running under, and this account does not have any elevated privileges. Security is handled by limiting access to the web app to specific Windows groups. Finer grained security is handled within the application by letting an application administrator grant or revoke access to specific application functions.
I agree with you for the most part. Using a service account means you can take advantage of connection pooling, etc. There may be cases where it's important to record what user actually logged in using tracing (and not relying on the application). Other than that, I can't think of a realistic situation where you'd want to use the credentials of a user and switch to an app role unless you're running some web application that can't use Windows auth (for instance, hosted on Apache or Tomcat) and there are corresponding SQL Server based logins for access, which you're also relying on to permit the security check for the web application. I don't like those kinds of designs, but I have seen them.
K. Brian Kelley
@kbriankelley
July 25, 2008 at 11:13 am
Brian,
The app uses sp_executesql, this is from the profiler that I just ran for the PW change. The password I submitted to the app was completely different.
execute sp_executesql N'sp_password NULL, ''milm434567'', ''test'''
Disclamer # 1: I create cases with the vendor for a number of years to improve handling logins.
Disclamer # 2 I know that dynamic statements are not safe.
Regards,Yelena Varsha
July 25, 2008 at 11:32 am
Yelena Varshal (7/25/2008)
Brian,The app uses sp_executesql, this is from the profiler that I just ran for the PW change. The password I submitted to the app was completely different.
execute sp_executesql N'sp_password NULL, ''milm434567'', ''test'''
Disclamer # 1: I create cases with the vendor for a number of years to improve handling logins.
Disclamer # 2 I know that dynamic statements are not safe.
Argh. You're right. If you call it through dynamic SQL it doesn't hide it. Not good.
K. Brian Kelley
@kbriankelley
July 28, 2008 at 2:30 am
Nice article and good explanation in the article as well as in the discussion. 🙂
Viewing 14 posts - 16 through 28 (of 28 total)
You must be logged in to reply to this topic. Login to reply