April 25, 2012 at 5:09 pm
Best Practice in setting up sql server access to DBA's:
Hi,
If there is DBA team with 4 DBA's, then what is the best practice to have the SQL Server access?
Method1:
1. Create an AD Group for in abc domain. example DBADMIN (abc\DBADMIN)
2. Add individual DBa account (eg: abc\scott) to DBADMIN group.
3. Add DBADMIN group to the local Adminsistrator group on each sql server.
Method2:
1. Create an AD user called DBADMIN
2. Add the DBADMIN to the local Adminsistrator group on sql server.
3.All DBA's use the same AD user DBADMIN & Password
Is there any other alternate ways to manage credential for DBA's? what is the best practice?
Thanks
April 25, 2012 at 5:16 pm
Method 1. Sharing user ids and passwords among multiple people is a security worst practice.
April 25, 2012 at 5:22 pm
Your first method is close.
The AD group should be added to the SQL server and granted the level of permissions needed. DBA team probably needs sysadmin server role. Some organizations have jr DBA's with limited access to perform specific tasks.
The local admin group on a windows server should not by default have access to the SQL server instance nor should it. Prior to SQL server 2008 the local admin group was sysadmin by default.
With SQL 2008 installation only the account the performs the install is added to the server by default.
Best practice is to only grant permissions that are needed by the user to perform their job.
April 25, 2012 at 5:25 pm
gmamata7 (4/25/2012)
Best Practice in setting up sql server access to DBA's:Hi,
If there is DBA team with 4 DBA's, then what is the best practice to have the SQL Server access?
Method1:
1. Create an AD Group for in abc domain. example DBADMIN (abc\DBADMIN)
2. Add individual DBa account (eg: abc\scott) to DBADMIN group.
3. Add DBADMIN group to the local Adminsistrator group on each sql server.
Method2:
1. Create an AD user called DBADMIN
2. Add the DBADMIN to the local Adminsistrator group on sql server.
3.All DBA's use the same AD user DBADMIN & Password
Is there any other alternate ways to manage credential for DBA's? what is the best practice?
Thanks
method 1. one its bad to share passwords. what happens when some one leaves?? you have to change the password and that can get anoying. and 2 each dba having there own login creates an audit trail (or can create and audit trail if you set things up right) so you can tell who did what when.
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 26, 2012 at 1:28 am
capn.hector (4/25/2012)
method 1. one its bad to share passwords. what happens when some one leaves?? you have to change the password and that can get anoying. and 2 each dba having there own login creates an audit trail (or can create and audit trail if you set things up right) so you can tell who did what when.
+1 Capn.
April 26, 2012 at 1:38 am
It's a good question, I will let you know my approach which is best for me still now. Particularly when you are working on a production environment and facing auditors this will be question against us often.
1. The first thing first, I will remove the sysadmin access for built-in administrator (less than 2005). This will revoke the sysadmin access to the members in local administrator group. Usually in most of the organization the Network and Hardware Team groups will also be added into local administrator group. If you are NOT performing this then you are allowing the Sysadmin access for those members also.
2. Create an AD Group and add the individual DBA accounts into the AD group and grant the Sysadmin access to that AD group (Granting lesser access than Sysadmin for those who are performing the DBA tasks is practically not going to work. For even the simpler administrative tasks they will required admin access).
3. Now comes the problem, the SQL Server is under our control but what if, someone manage to add them to the AD group, the AD is not in our control. Create a custom script to query the members of the AD group and configure an alert, if there is change in group members. I had used xp_logininfo for this purpose.
4. Strengthen your security either by DDL or Policy based managment and monitor those account.
---------------------------------------------------
Thanks,
Satheesh.
April 26, 2012 at 1:53 am
> Create a Windows Security Group; the DBA Manager can be the owner
> Add all the member Windows Account to the group
> Provide SA permission to the Windows Security Group
> Create a JustInCase SQL Account with SA permissions to be used in case the AD goes down.....:-) The password remains only with the DBA Manager
Management: People can be added or removed by the DBA Manager as and when required, without having to do this change accross 100s of DB servers
Security: Each activity has an explicit owner
Convenience: In case of issue with the AD or the account for e.g. account is locked for one user another user is not impacted.
Fool-proof DR: In case of a disaster and the AD being down, you would require a SQL user if you have disabled built-in administrator and sa (best practice), you would require using JustInCase SQL user and once things are back to normal, the DBA Manager should reset the password.
Chandrachurh Ghosh
DBA – MS SQL Server
Ericsson India Global Services Limited
Quality is not an act, it is a habit.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply