2

As the SQL Server administrator for a server running inside a Windows Domain, I followed some rules:

  1. SQL service runs under a Domain User account (least privilege)
  2. "sa" has been disabled

The question is basically about "my" account as an administrator. To expand on this, I have two AD accounts, 1 to login to my workstation (call it ADU1), and 1 to login to the server itself (ADU2). Note that this somewhat following the MS best practices for their recommended security model.

The question is, do I use my Domain User (ADU1) which has the "sysadmin" role, or (ADU2) or basically a different AD account other than my regular domain account?

Hope this makes sense. Looking for some guidance... thanks!

0

What's the worst case scenario if e.g. malicious software, running as you, gets concomitant access to the database in question?

The answer to that should probably dictate what you need to do. Note that if you want to use another domain user for administration, you can run SSMS as another user by shift-right-clicking the shortcut (as opposed to a normal right-click). There's also a Windows 10 system setting, "show 'run as different user' in start" (under developer settings).

4
  • I suppose the worst case scenario is the same as someone learning the SA password, which is why the move to use domain accounts (we use fingerprint to sign in to workstations). We have jump boxes to which we RDP with tier 2 domain accounts to manage servers. I think the question is really to discover what other DBAs are doing when managing SQL. – Eddie Paz Feb 17 at 23:27
  • Right - but a program doesn't have to do anything to use your windows credentials (it will be running as you automatically). For what it's worth, we used RDP and definitely had different AD accounts for admin vs typical read-only access. – Mark Sowul Feb 18 at 13:53
  • You are correct. It's the reason we RDP to jump boxes to avoid bad programs that may make they're way into our day-to-day workstations. It's still not 100%, but the jump box gives another layer of protection, and we enforce this with all administrators. I suppose the same treatment should be applied to SQL, but I'm curious what others do as it is painful to RDP just to run a query. – Eddie Paz Feb 18 at 17:37
  • You can always give a limited-permission role to the day-to-day account, and then only require the separate account for "elevated" actions. As an example, read-only access to non-sensitive data can be done directly, and there is a schema for "support" / "operations" procedures that can be run as well. Anything more than that would require running as the other user account. (Separate advice: use roles and AD groups. Don't grant permissions to specific accounts - add them to AD groups and assign those groups to roles) – Mark Sowul Feb 23 at 15:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.