Post reply

How should a DBA access SQL Server?

  • January 5, 2004 at 9:04 am

    #160328

    Hi,

     

    I have several SQL 7/2000 database servers. Each server contains several databases. Currently there is a myriad of setups for DBA access mapped to the sysadmin server role ie: the sa user; a generic windows domain “admin” account; several individual window domain user accounts; and a couple of SQL Server authentication accounts. A lacks a consistent method on how databases and DTS/alerts are created/owned.

     

    Aside from locking down the sa account, I want to implement a strategy to clean up and strengthen how a DBA accesses the database and creates/owns the databases. Another goal is to make it a little more generic vs tied into a specific DBA user account. For example, we had a situation where a former DBA, who has since left the company, has a number of automated features (mail; alerts; DTS) still being generated by his domain user account running against the databases. Therefore, his windows domain user account cannot be retired …

     

    I was reading a couple of related docs (SQL Server “Operation Guide” and “SQL Server 2000 Security” whitepaper), and they suggest setting up a windows domain group account and adding user domain accounts to this. I’ve also read that this could be a bad thing and that you should only use a SQL Server authentication account for DBA access.

     

    Any thoughts, suggestions, examples by trial or articles that you can point me towards would be greatly appreciated. 

     

    Many thanks.

    Jeff

    Many thanks. Jeff

  • January 5, 2004 at 11:10 am

    #488674

    When the possibility exists, use Windows authentication to assign security. The best way to handle this is:

    1. Create a Windows group for the DBAs
    2. Put all of the DBA Windows accounts in that group
    3. Grant the group the ability to logon to SQL Server
    4. Make the logon a member of the sysadmin role
    5. Remove all SQL Server logins with sysadmin rights other than SA wherever possible
    6. Assign a strong password to the SA account
    7. Change the SA password whenever a member leaves the DBA group and/or the company

    This ensures security is being handled in one place, at the domain level, making management, especially clean-up) easier. For the alerts, create a mailing list which mails to the DBAs.

     

    K. Brian Kelley
    @kbriankelley

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply