Security features in SQL Server 2019

Microsoft SQL Server provides several built in features that enable security, including encrypted communication over SSL/TLS, the Windows Data Protection API (DPAPI) used to encrypt data at rest, authentication and authorization. It is up to each database administrator to configure these features, or use additional security measures as needed, to address the security and compliance requirements of their data and applications.

SQL Server security is based on several hierarchical entities:

• Server—representing the entire SQL Server instance.
• Database—each server can have multiple databases. A database is a collection of securable objects.
• Securable object—data stored in a database, which requires associated permissions.
• Principal—a person, group, or process that needs to access data. Privileges granted to principals are managed by the SQL Server security framework.

SQL Server Security Threats

Here are three common security threats that affect SQL Server databases:

• SQL server authentication—SQL Server login can be vulnerable to connection string injection attacks. When a connection string is constructed at run time, unless the string is checked for valid keyword pairs, an attacker can add extra characters that can perform unauthorized actions on the server. A better option is to use Windows authentication, which is more secure.
• Privilege escalation—SQL Server databases typically contain sensitive data, and there is always a risk that users will assume privileges of more trusted roles, gaining unauthorized access to data and database functions. To mitigate this, prefer to run users as least-privileged accounts, and block the ability to execute code from administrative or owner accounts. If you need to grant extra permissions, use certificates to sign stored procedures or use impersonation, only for the duration of the task.
• SQL injection—SQL Server, like other databases, is vulnerable to attacks in which malicious users inject commands into query strings. These commands can damage or corrupt the database or be used to exfiltrate sensitive data. To prevent SQL injection, ensure you sanitize all database inputs to ensure they do not contain characters that can be used to execute code.
• Verbose errors—in many cases, SQL Server databases return error messages that include internal information, which can help attackers identify a vulnerability or plan an attack. Ensure that all procedural code uses error handling, to prevent default error messages from reaching the user.
• Denial of service attacks—production SQL Server databases may be subject to application-layer denial of service (DoS) attacks, in which attackers flood the database with fake queries, slowing performance for legitimate users, and eventually resulting in downtime. If you run SQL Server in the cloud, you can leverage DDoS protection services, which can capture and divert malicious traffic away from your database.
• Lack of security expertise and awareness—many database administrators are not sufficiently aware or trained on security issues, and may not know how to use the security capabilities of SQL Server. Any team that works on sensitive databases must have regular security training, and should work together with security experts to ensure SQL Server is securely configured.

SQL Server Security Configuration

• Server level Security: server level security includes creating login for users who can access the SQL Server Instance. The SQL authentication with username and password gives access to the server

• Database level security: Database security can be created by adding users to the specific database who can access the database where the permission is provided.
• Column level security: this kind of security ensures that only specific columns in the table are provided to the users based on their access level.
• Row level security: Row-Level Security as the name suggests is a security mechanism that restricts the records from a SQL Server table based on the authorization context of the current user that is logged in. This means the records from the tables are displayed based on who the user is and to which records do the user has access to. 
• Dynamic Data Masking: dynamic data masking enables the users to limited data in the column and masks the data with xxxx-xxx-897 for data which is highly confidential.
• TDE (Transparent Data encryption): TDE provides encryption for data which is at rest. The database files and log files are encrypted using TDE. This technology was designed to have the entire encryption process be completely transparent to the applications accessing the database. 
• Data classification: This helps to discover sensitive data such as SSN number, credit card number, bank account details, personal records etc. It is very critical for the data to be secure and compliant under SOX, PCI, and GDPR for each organization. The Classify data features adds extended properties to the columns to specify the label and the information type.
• Auditing: Auditing an instance of the SQL Server Database Engine or an individual database involves tracking and logging events that occur on the Database Engine. SQL Server audit lets you create server audits, which can contain server audit specifications for server level events, and database audit specifications for database level events. Audited events can be written to the event logs or to audit files.
• Always encrypted: Always Encrypted is a new feature in SQL Server 2016, which encrypts the data both at rest *and* in motion (and keeps it encrypted in memory). So this protects the data from rogue administrators, backup thieves, and man-in-the-middle attacks. Unlike TDE, as well, Always Encrypted allows you to encrypt only certain columns, rather than the entire database.

The client library ensures that plaintext is only revealed within the application or middle tier, and nowhere in between the application and the database

• Windows firewall security: Windows firewall for Database Engine access in SQL Server by using SQL Server Configuration Manager. Firewall systems help prevent unauthorized access to computer resources. To access an instance of the SQL Server Database Engine through a firewall, you must configure the firewall on the computer running SQL Server to allow access.

pooja.k