Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
 Posted - 2005-05-18 : 17:41:11
|
| In a nutshell, we are building a number of ASP.NET applications on our corporate intranet, and thus several different SQL Server databases. We have a home-built system that hides database connection strings through encryption and obscurity. It is in need of an overhaul, and being the new guy, I questioned why we even used it, which uses SQL Authentication, instead of NT Authentication. One key reason was that while we need the users to authenticate to use the web app, we don't want to manage all the users in SQL Server. And more importantly, we don't want them to be able to access the SQL Server with any tool outside of our application. That seemed to shut down my idea to manage with NT groups.So, then I threw out the idea of using an AppRole, without having experience actually using them, and was told to research it. So, being the good SQLTeamer I am, I first read through BOL to get up to speed. Then I searched the forums here and found a dearth of information. And now I am looking for someone with experience using these.As I understand it, to use an AppRole, I have to pass in the appropriate password. This seems to counteract the main reason for using NT Authentication instead of SQL Authentication. If I have to transmit a password either way, what do I gain from using an AppRole instead of a single SQL Login?And, if I'm going to use an AppRole with NT Authentication (which BOL says it can do) then don't I have to still have my users or groups added to the SQL Server as Logins, and also each database as users?What am I missing here?---------------------------EmeraldCityDomains.com |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2005-05-18 : 17:53:23
|
| If you're using ASP.NET, why wouldn't you just use AD authentication for the app itself. You use trusted authentication, and the user the application runs under in IIS has access to the appropriate stored procedures (shouldn't be any access to tables if it can be avoided). You don't need to add other users or groups to SQL Server as logins and database users. You would have authentication in your application for that. The app can utilize AD authentication or a username/password to identify the user and reference a table structure with their appropriate system/application permissions.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2005-05-18 : 18:36:01
|
| "the user the application runs under in IIS"... But if I've turned off Anonymous Access in IIS, in order to get the user to authenticate, how to I control the user the application is running under? ASP.NET Impersonation with a specific user name? Just reading about that idea right now, but wasn't sure if that's what you had in mind.---------------------------EmeraldCityDomains.com |
|
|
|
|
|
|
|
|
AppRole vs. SQL Authentication