In the previous blog of this series, we discussed how monitoring SQL Server activities helps secure databases. The database security can further be reinforced by carefully configuring the authentication and authorization mechanisms for database accesses. In this blog, we’ll briefly explain the various authorization and authentication mechanisms of SQL Server and some best practices you can follow.
How is authentication carried out in SQL?
Authentication in SQL Server is usually carried out using Active Directory (AD) credentials. When the SQL Server validates the AD credentials in the SQL database engine, it then grants users access to the database.
When a user from an untrusted domain wants to access the database, they have to authenticate themselves with SQL Server authentication credentials. This ensures that only authorized users are accessing the database. All the authentication activities are logged, and an IT admin can configure an alert whenever a login is attempted from an untrusted domain.
How is authorization carried out in SQL Server?
There are different levels of access that provide only the necessary controls to specific users over SQL Server and the database.
Three components—principals, securables, and permissions—are taken into account when configuring different access levels in the SQL database.
- Principals are the entities (users, groups, or processes) that are granted access to the SQL Server resources. For example, your AD credentials can be configured as a principal to access the SQL database. SQL Server contains three different principals: logins, users, and roles.
- Securables are SQL Server resources that you can secure by configuring different authorization levels. Securables can be at the server level (e.g. server roles), database level (e.g. application role), or schema level (e.g. tables).
- Permissions is the type of access given to a principal for a specific securable. For example, you can grant a user’s AD login (the principal) the ability to only view (permission) the data in a database (securable).
Best practices to secure SQL Server and the database:
1. Implement the principle of least privilege. Provide only the necessary permissions to users.
2. Regularly review the principals, like security groups, and remove unwanted users from the group.
3. Review the permissions to critical securables.
4. Continuously audit user accesses.
How does SQL Server auditing help ensure database security?
If a malicious insider tries to perform an unauthorized operation on a securable, they would fail due to insufficient permissions. Deviant activities like these would be spotted during SQL Server auditing, and any further attempts by the attacker could be prevented.
Tools like security information and event management (SIEM) solutions detect anomalies and add business context by correlating the inputs from threat feeds and behavioral analytics.
Want to check out how Log360, ManageEngine’s comprehensive SIEM solution, can help you audit and secure your SQL database? Ask our experts.
