General Guidelines:
- Use Windows authentication whenever possible for all individual user connections and application accounts
- Individual Windows accounts should be granted database access via AD group rather than as a stand-alone account (see application AD groups section below)
- Object changes (DDL) should be executed using a Windows administrative account (Admin ID) which is separate from HawkID or application service account
- Each developer should have an individual Admin ID that will be added to the db_admin database role via AD group
Application Service Accounts:
- Native SQL Server accounts should only be used in applications where Windows authentication is not supported
- Accounts should be unique to each application or solution
- Separate application accounts on production and non-production are suggested for data sources classified as critical or restricted
- Do not include server name in account name
- Append environment type to end of account name (e.g. -Test or _Test for test environment)
- Application service accounts should NOT have full administrative privileges (i.e. the ability to modify all objects or permissions)
- If limited DDL changes are required for application functionality, grant elevated access only to the objects that require it
- Use complex passwords or passphrases
- Update passwords or passphrases when a staff member leaves the workgroup
Application AD Groups:
Each application should have at least two AD groups
- Elevated privileges AD group
- Usually given the db_admin database role with the following permissions
- DML: read, write, and execute
- DDL: create, alter, and drop objects
- Security: grant and remove object permissions and role membership
- Should be restricted to individual Admin IDs
- NO HawkIDs and NO application service accounts should be given membership
- Usually given the db_admin database role with the following permissions
- Standard access group(s)
- Create a unique group for each application or solution
- Usually read-only or read-write, may also include execute
- Contains application service accounts
- May also contain HawkIDs
- Only create AD groups needed for application functionality
- Group best practices
- DO NOT nest groups inside these groups
- DO NOT include server network name in group name
- To differentiate between production and non-production environments, append environment to the end of the group name (e.g. -Prod -Test -Dev -QA)
SQL Server Agent Accounts:
- Windows service account to manage SQL Server Agent jobs and schedules – usually one per workgroup
- This account must be added to SQLAgentUserRole on a server
- The account may also be added to DatabaseMailUserRole as needed