September 4, 2009 at 10:02 am
Hi ALL!!
Looking for suggestions and help in general.
My case:
- wanting to change current state to 100% Windows-driven security
- wanting to dump all the SQL logins/users in favor of GPO-driven security groups
- all the users will be assigned to several Windows security groups
- each group will be assigned their own SQL instance with their own database;
- for example: UserGroup33 will be assigned SQLInstance33 with the DB33 to work against
- when a user (member of UserGroup33) logges onto some workstation, his/her security context will be established
- when this user starts the application, the application will determine out of the user's security context his/her proper SQL instance name;
- then the app will create the connection string similar to ""Data Source=SQLInstance33;Integrated Security=True;""
- upon connecting to the SQLInstance33, my SQL security config will reroute this user to the proper default DB33, apply appropriate permissions, based no the role(s), etc.
- no hardcoding should be used anywhere; there should one single value per a user group that will control this;
My question:
- what could be a proper way to store/administer this default SQL instance name for this UserGroup33?
- ideally, if we need to reassign the SQL instance to server the UserGroup33, I would want to change a single value in a single place for this to take affect (e.g. urgent moving of the DB33 onto some other server due to a crash)
One possible way I found is fia registry: http://support.microsoft.com/kb/323639.
Please comment and/or propose some other ways. Many thanks.
September 4, 2009 at 10:09 am
Store the group to instance mapping information in a shared database. Expose a call to said database to the application. Application makes the call, gets the connection string, and then connects to the proper instance and database.
K. Brian Kelley
@kbriankelley
September 4, 2009 at 10:17 am
Well... this is an option.
However, did not really want to have this hardcoded central location pointer (be it a file or a database) if possible. This is because if we need to change the central location for any reason, then we must update hundreds of desktops/applications that still point to the old central location.
A partial goal of this project to eliminate desktop by desktop management.
And this is what happens we rely on config files placed on physical desktops.
More details:
- the security groups are usually physically remote - every group is different remote office
- network is flaky
- our AD replicates onto remote offices so that when network is down, their local server just acts as a backup DC
September 4, 2009 at 10:28 am
If the connectivity is really based on site, there's an easier way.
Create a custom administrative template (.adm) file for a GPO that specifies a registry location. That registry location specifies the connection string.
Then create site-level GPOs that use this template.
Have the application read the registry location (which should be implemented by the computer when the GPO is processed).
Connect!
K. Brian Kelley
@kbriankelley
September 4, 2009 at 10:32 am
This is kind of what the URL I pasted above is talking about.
So, yet another vote for taking that route - GPO over the registry.
Thanks, Brian!
September 4, 2009 at 11:58 am
I love SQL Server, but having stepped down from a directory services admin position less than a year ago, GPO is the right tool for the job since you're talking site level settings.
K. Brian Kelley
@kbriankelley
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply