March 12, 2010 at 7:07 am
With SQL 2008 we have to choose what windows account(s) will be members of the sysadmin role.
Recommendations are to not use Builtin\Administrators as the LAN Domain Admin team members really don't need to be sysadmins to all of the SQL database server instances in the environment.
Now the question - what is the best way to implement?
Unique domain account for each SQL Instance that is member of sysadmin?
Domain Group consisting of just DBA team that is member of sysadmin?
Local account(s) for each SQL Instance that is member of sysadmin?
Other?
Anybody out there with opinions? We are in the infancy stage of implementing SQL 2008 and I would like to try and start out with a secure and reliable environment.
Thanks, Phil
March 12, 2010 at 7:12 am
HAve a domain user account to run SQL services under. I prefer one per server (or pair of servers if using HA such as logshipping/mirroring) That will have sysadmin rights but you would NEVER use it to log on with.
Create a windows group with just the DBAs as members. give that group sysadmin.
....er, thats it
---------------------------------------------------------------------
March 12, 2010 at 7:20 am
I apologize if I was not clear in my question
Each instance of sql has dedicated unique domain accounts to run the services - these accounts are not used for anything else and are limited as to what else in the domain they have access to (only what they need if anything)
What I am referring to is the daily adminstration of the database instance - who is authorized to access SSMS with sysadmin rights.
March 12, 2010 at 7:35 am
then only this:
Create a windows group with just the DBAs as members. give that group sysadmin.
---------------------------------------------------------------------
March 17, 2010 at 12:06 pm
I agree with George here. Create a Windows group strictly for the DBAs. Use that group to manage the SQL Servers. That prevents anyone from having to know the sa account password. It also ensure that as people come and go, all the cleanup happens in Active Directory, meaning you don't have to keep up with old Windows user accounts on each of your SQL Server.
With that said, someone will need to audit membership changes to this group. There's nothing stopping a LAN administrator from temporarily putting himself/herself in and gaining access as a DBA. The audit won't prevent them, but it will reveal who is breaking your organization's procedures and is often enough a strong enough deterrent to keep them from doing so.
K. Brian Kelley
@kbriankelley
March 22, 2010 at 5:32 am
Many security practitioners are close to paranoid about product administrators doing their work without needing Windows superuser or product superuser rights. IMHO the documentation from Microsoft needed to achieve this is fragmented and hard to collate.
The FindBuild product has a list of Windows rights a DBA group needs in order to manage SQL Server without being a member of the local Administrators group. This includes rights needed for using Performance Monitor, reading Event Logs, etc. Look in the Reference Manual in section 'GPO Preparation' for details of how to implement this outside of FineBuild.
FineBuild also includes the concept of separate Sysadmin and Non-sysadmin groups, with SQL rights granted to the Non-sysadmin group that allow the DBA to perform the majority of their tasks without needing sysadmin access. See 'Setup DBA Non-Admin Group' for the rights needed.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply