Auditing systematically records and reviews security-relevant actions and events for accountability and investigation. A common mistake is leaving administrative activity unaudited: privileged accounts then lack nonrepudiation, making suspicious changes harder to attribute or investigate. section 14.0 section 14.4
Mistake: trusting privileged accounts
SQL Server security should not rely on administrators behaving correctly after a dangerous feature such as xp_cmdshell is exposed. Use auditing alongside least privilege, disabled dangerous features, and robust alerting so privileged actions can be reviewed and attributed. section 14.4 section 14.0
Table 14.1 Security terminology (view table figure)
| Term |
Definition |
|---|---|
| Security principal |
An entity, such as a person or an application, that is granted permission to access a resource |
| Securable |
An object that can have permissions applied to it. In SQL Server, this is often an object such as a table or a stored procedure. |
| Login |
A security principal at the instance level that allows a person to access an instance |
| Database user |
A security principal at the database level that (usually) maps to a login and allows a person to access resources within a database* |
| Server-level role |
A group that contains one or more logins and can be granted permissions on instance-level securables. If a server role is “fixed,” then it refers to a built-in role that is preconfigured with a set of permissions that meets a common use case. |
| Database level role |
A group that contains one or more database users and can be granted permissions to database-level securables. If a database role is “fixed,” then it refers to a built-in role that is preconfigured with a set of permissions that meets a common use case. |
| Active directory (AD) |
A Windows-hosted directory service that allows permissions to be managed centrally. |
| Domain |
A collection of AD objects, such as users, groups, and computers. The information is stored within a database that is synchronized between domain controllers. Domain controllers are the servers running the domain controller service, which are used to manage AD. |
| AD user |
A Windows user that is created and managed in AD. |
| AD group |
A group that is created and managed in AD. It will contain AD users and can be granted permissions on securables throughout the domain. |
| Service account |
A user account, typically an AD user, that is intended for running Windows services only. These accounts are typically configured to disallow interactive logins and are often set for their passwords to not automatically expire.** |
| Kerberos authentication |
A secure authentication protocol used by AD that provides a method of mutual authentication via key exchange |
| Service principal name |
A unique identifier for an instance of a service. This is a feature of Kerberos authentication. |
| * A contained user is not mapped to a login. Therefore, access is only possible within a single database. ** If automatic password expiry is disabled for a service account, it should still have its password rotated. |
|
Review security principals, logins, users, and roles to identify which identities and groups require monitoring. section 14.0
