通过

DG0116 and DG0121: DBA Privileges Via Roles

  • 项目

The generic DoD database STIG requirement DG0116 states that the Information Assurance Officer (IAO) will ensure database privileged role assignments are restricted to IAO-authorized accounts, and DG0121 states that the DBA will ensure database privileges are assigned via roles and not directly assigned to database accounts.

These two requirements are closely related and have a common solution when it comes to SQL Server system administrator (sysadmin) privileges:

  1. Create (or have a domain admin create) a Windows group just for senior DBAs. My preferred name is SrDBAs.
  2. Add your Windows login account and the Windows login accounts of any other DBAs who need the sysadmin role to the Sr. DBA group.
  3. Create a SQL Server login based on the new Sr. DBA group.
  4. Assign the sysadmin role to that the new login.

Great, you have a new sysadmin group/login, so if there are changes in personnel and the Windows accounts of all the old DBAs have been deleted, the new folks won't be permanently locked out. All they need is for a domain admin to add the Windows accounts of the new DBAs to the Sr. DBA group, and they'll have full sysadmin privileges.

If you're wondering if a DBA only has access through a group account, will that affect auditing? After all, DG041 requires the ability to track individual users, and DG0060 requires that actions through shared accounts must be attributable to individual users. Not to worry, traces and audits will still record the individual user account, even if they're only able to login through a Windows group-based login. Want to see for yourself? Just make sure your individual account isn't used as a SQL Server login, start a T-SQL trace in Profiler, and run a query. You'll see your individual account in the results, not the group you're logged in through. There's still a reason why your SQL Server instance is likely to need individual logins, however. Can you guess why? Because SQL Server logins based on Windows groups can't be owners of objects, such as as databases. For top security, logins being used as object owners will have less permisions than sysadmin.

Now suppose you have your new sysadmin group and you're installing a new instance of SQL Server 2008. Once you get to the Database Engine Configuration screen (fig. 1), don't click the "Add Current User" button like most DBA's would. Instead you click the "Add..." button, and add the Sr. DBA domain group, and you'll avoid having to make changes later to comply with DG0116 and DG0121.

 

Figure 1.

 

Related Notes:

SQL Server 2005 vs. SQL Server 2008

SQL Server up through SS05 had a BUILTIN\Administrators group, but that group was no longer included with SS08, and for good reason. The BUILTIN\Adminstrators group meant that any operating system administrator had SQL Server sysadmin permissions, which is a bad idea for security, and violates DG0116. The way to make SS05 compliant was to create a new Windows group for DBA sysadmin's, as described above, and then to delete the BUILTIN\Administrators group. Technically, you don't have to delete the BUILTIN\Adminstrators group to be compliant with DG0116, you just have to remove the sysadmin role from it. I recommend that you delete it, however, to avoid future confusion, such as if you change employers and the new DBA has trouble figuring out why they don't have sysadmin privileges when they see the BUILTIN\Administrators group. Without the sysadmin role, you don't need that group for anything, so just get rid of it. The only precaution is that you must have your new sysadmin group working before you delete the BUILTIN\Administrators group, or remove the sysadmin role, because you'll lock yourself out if you do. Also, I don't know why, but on several systems I checked, I couldn't disable the BUILTIN\Administrators group, even though I could delete it.

Connection Failure

Suppose you're installing SQL Server 2008 in a domain where no Sr. DBA Windows groups exists. You get to the configuration screen in figure 1 and realize the problem, so you pause your installation and get the Sr. DBA group created. Then you add the new group under 'Specify SQL Server administrators' and finish the installation. Then you open SQL Server Management Studio (SSMS) and try to log in to your new instance using Windows Authentication, as in figure 2, but you get hit with a login failure, as in figure 3.

 

Figure 2.

 

 

Figure 3.

What went wrong? Not to worry, this is about the easiest problem you'll ever overcome. Just log out and log back in again. Since you were already logged in before the DBA domain group was created, your Windows credentials don't include that domain group membership until after you've logged in again.

Insufficient Permissions

If you're logged in to SQL Server Management Studio (SSMS) while your Sr. DBA Windows group is created, don't expect it to provide you with it's privileges until you've logged out and back in, similar to the problem above. You have to log in again for your Window login to pick up the new permissions, which is needed before SSMS can give you the new privileges based on it.

 

Supplemental search terms: 

"Connect to Server" "Cannot connec to <server name>" "Additional information: Login failed for user '<DOMAIN\user>'. (Microsoft SQL Server, Error: 18456).

Comments

  • Anonymous
    January 01, 2003
    Hi John - We are a product company designing our application to comply with DISA requirements...So we don't know who the DBA(s)will be as we designing it...but we know that we need a DBA account for post implementation for any production support... How should we approch creating this "shared" DBA account with the installation of our product at the customer site? Should we still create a windows group (our prouct will not be on domain) and add a shared DBA to the group with the prodcut installation? or can we create a SQL Login (shared) and add that use to sysAdmin role? Your Advice is greatly apprecaiated. Thanks Cheriyan