April 21, 2009 at 4:53 am
Good morning,
I have been tasked by my boss to re-structure the logins for our company's applications that currently access SQL2005 across the web (vb .net apps)
As it stands all of the applications use the standard 'sa' login through one individual connection manager *shiver*. I want to change this to allow better logging/auditing/control and better transparency when monitoring the DBs using SQL profiler or such like.
So, the question I have is what is the best approach? I've thought about assigning an application role for each application so that we can better manage the way they individually connect to the client DBs, as opposed to using the blanket 'sa' technique, but is there a better way to do this?
Would it be a better idea to group up the users who connect to the SQL Server via the applications and assign them to individual 'User-defined' roles? I.e. HR, Production etc?
Im looking for the best way to organise this and currently am a little lost as to the best way to approach this. If i've not supplied info or anything please say and i'll do my best to give it!
Thanks,
Dan
April 21, 2009 at 8:01 am
Consider creating individual Domain User (plain vanilla, not admin) accounts and grouping them together in a AD Security Group. This way, you can manage all the actual group policies all together and add one group only to SQL Server (or just add the individual user accounts).
It keeps the maintenance nightmare contained somewhat to do it this way. And if you need to add a specific DENY permission to an individual user account and leave the others alone, all you have to do is add the Group & the single user account, apply the correct perms and everything should shake itself out.
I hesitate to say give all applications the same user account because I'm sure different applications access different DBs and different data. However, if you need to assess your own security needs before you make a decision one way or another.
April 21, 2009 at 8:23 am
As Brandie mentioned, having AD groups in place with the individual users, which you probably have already, facilitates much more the administration at SQL level, where you only would need to initially add the corresponding AD groups with their rightful permissions. Any additions or modifications that occur at User level on your domain don't need to be replicated later at SQL level.
An using 'sa' for your application logins is a BIG NO-NO.... You really have to look also into SQL injection issues here!
April 21, 2009 at 8:39 am
Ok thank you both for this.
We have ~150 users who would be looking to connect via these applications that are external to the company (i.e. not in our domain). As these users are external to our domain would it be best to create an individual user role within the scope of the SQL Server with a set of permissions that they can all take advantage of?
Thanks
April 21, 2009 at 8:49 am
We have ~150 users who would be looking to connect via these applications that are external to the company (i.e. not in our domain). As these users are external to our domain would it be best to create an individual user role within the scope of the SQL Server with a set of permissions that they can all take advantage of?
That is correct you cannot use AD groups and account for none intranet applications because Asp.net permissions are resolved by IIS not AD and login to AD is not login to Asp.net not even for your intranet users. Your application using SA is careless programming not related to Asp.net security because Asp.net 2.0 and above comes with Membership database and other providers roles included.
When you add your intranet users in your AD group make sure you also add them manually into SQL Server and the databases used by that application or IIS may not resolve their permissions in SQL Server.
Kind regards,
Gift Peddie
April 21, 2009 at 8:50 am
Any application which were accessed through web is need secure enivonment. It s important when so many user access application thoguh web.
It is better to organise user in agrups and assign different level of accessbility to different user group, de[and on their role.
April 21, 2009 at 8:54 am
150 users is a very small user base and all roles and granular permissions are in Asp.net but somebody needs to do some actual work not just add to AD because AD does not resolve Asp.net permissions.
Kind regards,
Gift Peddie
April 21, 2009 at 9:41 am
Gift Peddie (4/21/2009)
When you add your intranet users in your AD group make sure you also add them manually into SQL Server and the databases used by that application or IIS may not resolve their permissions in SQL Server.
That kind of defeats the purpose of using AD..... An AD Group defined on your (intranet) domain that has all the member users needs only to be configured as the AD Group in SQL.
Now, if the users, as described by the OP are external, then it might be advisable to use a "generic" user in SQL with the proper permissions. You could also, depending on the role defined at application level, make the connection to SQL with different users.
April 21, 2009 at 9:56 am
Richard M (4/21/2009)
Gift Peddie (4/21/2009)
When you add your intranet users in your AD group make sure you also add them manually into SQL Server and the databases used by that application or IIS may not resolve their permissions in SQL Server.
That kind of defeats the purpose of using AD..... An AD Group defined on your (intranet) domain that has all the member users needs only to be configured as the AD Group in SQL.
Now, if the users, as described by the OP are external, then it might be advisable to use a "generic" user in SQL with the proper permissions. You could also, depending on the role defined at application level, make the connection to SQL with different users.
You and other users not doing the work needed to run AD permissions with Asp.net and other reasons is why in SQL Server 2008 AD groups can be cleanly removed from SQL Server and IIS is not a Windows component but a development tool in the developer division not Windows.
There is manual work required to use AD with Asp.net but user like you decided to skip the work and make decisions which makes Asp.net application permissions to break.
Kind regards,
Gift Peddie
April 21, 2009 at 10:10 am
Richard M (4/21/2009)
Gift Peddie (4/21/2009)
When you add your intranet users in your AD group make sure you also add them manually into SQL Server and the databases used by that application or IIS may not resolve their permissions in SQL Server.
That kind of defeats the purpose of using AD..... An AD Group defined on your (intranet) domain that has all the member users needs only to be configured as the AD Group in SQL.
Now, if the users, as described by the OP are external, then it might be advisable to use a "generic" user in SQL with the proper permissions. You could also, depending on the role defined at application level, make the connection to SQL with different users.
This was actually my understanding too. I mean it doesn't make sense that you make a group of domain users and then have to implement the same list of users in SQL too!
Re what Richard said, yes in this context the users are external, and the idea of creating a 'generic' role in my eyes seems ideal apart from the fact that it may be difficult/impossible to trace which user is using this particular login...is there any way you can do this? (again aside from either creating the users in SQL individually or incorporating them into active directory somehow)
Once again thanks,
Dan
April 21, 2009 at 10:25 am
Dan,
I'm a little confused. Are you trying to create logins on a user-by-user basis or on an application-by-application basis?
I previously thought you were talking about the later. Now it sounds like you mean the former.
One of the apps at my workplace does a combination. The client db has tables for users and user roles within the app, but the app itself uses a single login to reach the database for application work. So we have the ability to track errors & stuff by userID in the database, but it's not SQL Server security.
Does that make sense?
Of course, you can't just implement this as SQL Server security. You have to get a developer involved to write the code for this.
April 21, 2009 at 10:25 am
You could do that but some of your intranet users will error out with Login failed and Null user errors because AD does not resolve Asp.net and SQL Server permissions.
Kind regards,
Gift Peddie
April 21, 2009 at 10:30 am
One of the apps at my workplace does a combination. The client db has tables for users and user roles within the app, but the app itself uses a single login to reach the database for application work. So we have the ability to track errors & stuff by userID in the database, but it's not SQL Server security.
This is the work I am talking about thanks Brandie for posting it most DBA and System Admins decides this must be skipped so IIS is now part of a different group within Microsoft.
Kind regards,
Gift Peddie
April 21, 2009 at 10:30 am
Gift Peddie (4/21/2009)
You could do that but some of your intranet users will error out with Login failed and Null user errors because AD does not resolve Asp.net and SQL Server permissions.
I suppose it depends on what tools you're using and how you do it. We've never had that problem and we've been using this type of structure for several years.
Of course, we test security extensively before releasing changes like this to Production too.
April 21, 2009 at 10:49 am
Brandie Tarvin (4/21/2009)
Dan,I'm a little confused. Are you trying to create logins on a user-by-user basis or on an application-by-application basis?
I previously thought you were talking about the later. Now it sounds like you mean the former.
One of the apps at my workplace does a combination. The client db has tables for users and user roles within the app, but the app itself uses a single login to reach the database for application work. So we have the ability to track errors & stuff by userID in the database, but it's not SQL Server security.
Does that make sense?
Of course, you can't just implement this as SQL Server security. You have to get a developer involved to write the code for this.
Well, this was why I initially raised the post. I was trying to see what was possible in regards to user logins and application login/roles.
It now seems that the route I will have to take is similar to what you've described Brandie. I'm speaking to one of the developers tomorrow re the connection manager process that is used so once I know more about this I believe i'll be in a better position to figure out the best way to create the login structure that is needed.
Thanks
Viewing 15 posts - 1 through 15 (of 20 total)
You must be logged in to reply to this topic. Login to reply