DBA Flashcards
Describe a DataSecOps approach to enterprise data security.
The basic premise of a DataSecOps approach, which calls for IT and data scientists to work together as the cloud infrastructure is built, includes data protection as an integral part of every decision. By weaving privacy and security into the cloud infrastructure’s DNA, companies are taking the proper measures to protect data at all times.
Sometimes the terms “security” and “privacy” are used interchangeably, but it is vital to understand the nuances between the two when building a secure cloud infrastructure. Data privacy is associated with ensuring that personally identifiable information (PII) stored in the cloud is hidden. Privacy regulations, such as the EU’s GDPR and the California Consumer Privacy Act (CCPA), dictate what data is considered private and that the data remains pseudonymized at all times.
Data security, on the other hand, pertains to specific protections that have to be built into the infrastructure to prevent data from being stolen. Building a secure cloud infrastructure is predicated upon understanding the right mix of privacy and security measures, which can vary based on an organization’s industry and the specific regulations to which it must adhere.
With much of the workforce going remote — and supported by the cloud — protection can no longer be a “secure the perimeter” strategy. Every element of the infrastructure, application, network and data store must be individually protected, leading to the concept of a data security mesh. With its any-to-any connectivity capability, data records are protected regardless of the cloud or the data store where it originates, where it is or how it is being stored or processed.
Describe some SQL Server Hardening best practices.
SQL Server Hardening Best Practices
Harden the Windows Server where SQL Server Operates: It is important to harden the Windows Server operating system before installing SQL Server on it. Otherwise, attackers who cannot defeat your SQL Server security measures can simply go around them: They will gain access to the OS and copy the data files to their own server, where they can break passwords and encryption at their leisure.
Install Only the Required SQL Database Components:
You should limit the installation to just the components needed for your database to perform its tasks. This approach reduces your attack surface area by eliminating components that could have security vulnerabilities. It also minimizes resource utilization by the database and simplifies administration by eliminating services and components needed to be managed.
Limit the Permissions of Service Accounts According to the Principle of Least Privilege: Each SQL Server service is configured to run under a specified Windows or Active Directory account. You should plan which accounts should be permitted to run which services based on the principle of least privilege, which states that each account should have the minimum permissions and system rights it needs to function.
Use Service Accounts: Here are the types of accounts you can use for SQL Server services:
• Active Directory managed service account — This is usually the best option, for two reasons. First, since you cannot use managed service accounts to log on to a server, they are more secure than domain user accounts. Second, you do not need to manually manage password resets for service accounts, as you must for regular domain user accounts.
• Domain user account — This is most common type of account used to run services. This account type is quite secure in a domain environment because it doesn’t have administrator privileges.
• Local user account — This is a good choice for non-domain environments.
• Local system account — These accounts are highly privileged so you should avoid using them to run services.
• Network service account — This type of account has fewer privileges than a system account, but it does enable a service to have access to network resources, so you should avoid using it whenever possible.
• Virtual service account — A virtual service account is similar to an AD managed service account, but it is a type of local account that you can use to manage services without a domain. Technically, it is simply an instance of the built¬in Network Service account with its own unique identifier. Virtual service accounts are great to use for SQL services.
Turn Off the SQL Server Browser Service:
The SQL Server Browser service eliminates the need to assign port numbers to the instances. It enables SQL admins and authorized users to discover database instances over the network. However, this also makes it possible for attackers to gain knowledge of the available SQL Server resources. Therefore, when running a default instance of SQL Server, you should either turn off the SQL Server Browser service or configure another port for it to use to communicate.
Use Groups and Roles to Simplify Management of Effective Permissions: The effective permissions for a given account on a specific resource result from:
• Explicit permissions granted directly to the account on the resource.
• Permissions inherited from membership in a role or group.
• Permissions inherited from a parent resource.
In order to more easily understand and manage effective permissions, the best practice is to create containers, such as groups or roles, and assign those containers permissions to access resources. Then make accounts members of those groups of roles. That way, it’s easier to understand the effective permissions for each account. Moreover, simply by putting a user in the right groups or roles, you can assign the correct permissions to new hires, modify permissions as a user’s role changes and remove a user’s permissions when they leave the organization.
Follow the Principle of Least Privilege when Assigning SQL Server Roles: Server roles provide an easy way to delegate administrative privileges, but you must assign these roles carefully. Here are all the default SQL server roles and the permissions they have:
• Sysadmin — Perform any activity on the SQL server
• Serveradmin — Configure SQL server settings and shut down the server
• Securityadmin — Manage logins, including their properties, passwords and permissions
• Processadmin — Terminate processes on the SQL Server instance
• Setupadmin — Add or remove linked servers and manage replication
• Bulkadmin — Execute the BULK INSERT statement
• Diskadmin — Manage disk files
• Dbcreator — Create, alter or drop any database
• Public — Every user is a member of this role. It does not have any permissions except to objects that are configured as public.
It is very important that you follow the principle of least privilege when assigning roles to users. For example, if a user only needs permissions to shut down the server and end processes, they should be assigned the serveradmin and processadmin roles; assigning them the sysadmin role would a big violation of principle of least privilege.
If no default server role matches your security requirements, you should create a custom role that does. You can do that using either Transact¬SQL or the Management console.
Use Strong Passwords for Database Administrators:
Strong passwords are a must for all database administrator accounts to make them resistant to brute-force attacks. At a minimum, require these passwords to contain at least 10 characters, including uppercase and lowercase letters, numbers and specific symbols; however, passphrases are the best choice.
Install SQL Server Updates Promptly: Both white-hat and malicious hackers are constantly discovering vulnerabilities and exploits in SQL Server. Microsoft releases several types of updates to fix them:
• Hotfixes (also known as QFE or Quick Fix Engineering) are released to solve customer problems ASAP. Due to the tight time constraints, hotfixes receive limited testing, so they should be applied only to systems that are known to have the specific issues they address.
• Cumulative updates (CUs) are periodic releases of a set of hotfixes that have had proper testing.
• Service packs (SPs) are a big collection of patches and fixes that have been properly tested and can easily be installed as a single package.
The simplest way to secure SQL Server is to keep it up to date. The easiest way to achieve this is to enable automatic updates from Microsoft. Larger organizations or those with strong change processes should apply updates only after testing them in test environments.
Use Appropriate Authentication Options:
SQL server offers several options for authenticating users:
• SQL Server Authentication mode — Only Windows or AD users are permitted to connect to SQL Server.
• Windows Authentication mode — Only Windows or AD users are permitted to connect to SQL Server. SQL Server does not actually authenticate Windows; rather, it allows access based on an access token that has been issued to the user who logged in.
• SQL Server and Windows Authentication mode — Both Windows and SQL logins, such as the system administrator (sa) account, can access SQL Server. This mode is often called mixed authentication.
Best practices recommend using Windows Authentication to connect to SQL Server because it can leverage the Active Directory account, group and password policies. If you have to use SQL Server Authentication Mode to connect to SQL Server, do not use an sa account; instead, disable that account because it is the first account attackers will try to compromise in a brute-force attack .
Control Password Options for Logins: In a Windows¬based environment, administrators can enable policies for Windows users that control things such as password complexity and expiration. SQL Server can enforce similar options for SQL Server logins.
When you create a SQL Server login, you can specify the following options:
• MUST_CHANGE — SQL Server will prompt the user to change their password when they log on for the first time. Use this option when you create a new user or want a user to reset their password the next time they log on.
• CHECK_POLICY —The Windows password policies of the computer on which SQL Server is running will be enforced for the user. Always enable this setting.
• CHECK_EXPIRATION — The user will be required to reset their password regularly. Always enable this setting.
Be Diligent about Disabling and Deleting Logins
If a login will not be used for a long period of time, such as a month or longer, you should disable it and then re¬enable it later if needed. In these situations, it is better to disable the login rather than deleting it from the system. However, you should review your logins periodically and delete any that were disabled more than a year ago.
Use a Strong Database Backup Strategy
You must ensure that your database is backed up properly so the data can be recovered if a failure occurs. There are two types of backups: full backups and incremental backups. A full backup, as the name suggests, backs up the full database. After a full backup has been completed, SQL Server maintains a map of extents that can be backed up, and it backs up only those extents that have changed; this is called a differential backup. SQL Server does not clear the map of modified extents after a differential backup; it clears it only after a full backup.
Differential backups are much faster and occupy less disk space than full backups, so they are very useful for medium and large databases after the initial full backup; however, full backups should still be taken periodically. For small databases, the best practice is to simply use full backups every time.
For very large databases, you can consider using a file and filegroup backup strategy, which backs up only certain files or filegroups. This strategy can reduce the time required to perform backups. It can also speed recovery times, because if a single file is lost, you need to restore only that file or the filegroup that contains that file, instead of the whole database. However, be aware that managing filegroup backups can be complex.
Monitor Activity on Your SQL Server
Effective monitoring is critical to detecting, diagnosing and resolving problems. For example, you might identify long¬running queries that could turn out to be malicious. In particular, be sure to watch for the following:
• Concurrency issues — If multiple users attempt access the same data, some requests are blocked until others complete, which can result in deadlock, in which two operations are blocking one another.
• Deviations from your baseline — Record regular workloads metrics to establish a baseline so you know what they look like when the SQL database is operating normally. Then watch for deviations from that baseline. If you experience a significant change from your baseline without a clear cause, begin a security investigation as soon as possible. Remember that your environment is constantly evolving, with more data to store and more users working with the data, so you need to reestablish your baselines on a regular basis, such as once a year. Baselining will also help you to plan infrastructure upgrades in a timely way, instead of suddenly discovering you are over capacity.
Audit Access and Changes to SQL Server and Your Databases
You should always audit failed logins to SQL Server. Once you have enabled login auditing in SQL Server, the failed and successful login information will be written to the SQL Server error logs, which should be monitored regularly for suspicious activities.
Also be sure to monitor access, changes and deletions to database objects that contain restricted data. You should also track changes to SQL Server configurations and permissions, so you can block attacks and remediate mistakes before you suffer significant damage. This tracking can be done using SQL traces or third-party software like Netwrix Auditor for SQL Server.
Protect against SQL Injection Attacks
In a SQL injection attack, hackers enter SQL commands into a form field of an application front end. The application then passes that code to the database engine, which executes it; for example, it might create logins, delete data or change permissions.
The best way to protect against these attacks is to parameterize every query sent to the database. You should use properly configured stored procedures; they are much safer than direct dynamic SQL. Never pass string values in the front-end application, and be sure that all queries to the database are sanitized before being executed against the database.
Use Encryption Wisely:
Encrypting data helps keep it secure even if unauthorized users gain access to it. There are several encryption features in SQL Server you can use to protect your data:
• Transparent data encryption (TDE) — TDE encrypts the physical files, both the data (mdf) and log (ldf) files. The encryption process is completely transparent to the applications accessing the database; the files are encrypted using either Advanced Encryption Standard (AES) or Triple DES and then decrypted as the information goes into memory.
• Always encrypted — This feature encrypts the data both at rest and in motion (keeps it encrypted in memory), so it protects the data from rogue administrators, backup thieves and man-in-the-middle attacks. Unlike TDE, it allows you to encrypt only certain columns, rather than the entire database. Enable this feature if you are running Microsoft SQL Server 2016 or later.
• Column level encryption — This feature encrypts only certain columns in a database, such as credit card details or Social Security numbers. Enable it if you have Microsoft SQL Server 2014 or earlier; in later versions, the “always encrypted” feature is superior.
What is the purpose of Update Statistics?
Ensures that queries compile with up-to-date statistics.
It updates information about the distribution of key values for one or more statistics groups (collections) in the specified table or indexed view.
Existing statistics are used by the query optimizer in order to generate the most efficient query plan for execution. The query optimizer uses the statistics to determine when an index should be used, how to access those indexes, how best to join tables, etc…
However, updating statistics causes queries to recompile. We recommend not updating statistics too frequently because there is a performance tradeoff between improving query plans and the time it takes to recompile queries.
Statistics are created in a couple of different ways. The first way is when an index is created on a tables column(s). The next way is also done automatically and this happens if you have the “AUTO CREATE STATISTICS” database option set. When this database option is enabled, anytime a column is used as a query predicate the Microsoft SQL Server engine will automatically generate statistics for the referenced column.
Just as there are two ways that statistics can be created, there are also a couple ways they can be updated, manually and automatically. To update statistics manually we can use the UPDATE STATISTICS command or the built-in stored procedure sp_updatestats. sp_updatestats will update the statistics for every user defined table in the database it is run against. The UPDATE STATISTICS command gives you the ability to use the same sampling options that were outlined above with the CREATE STATISTICS command
What steps can you take to improve the performance of a poorly performing query?
Some general areas of concern would be:
- No Indexes
- No Table Scans
- Missing or out of date Statistics
- Blocking
- Table Locks
- Excessive recompiliations of stored procedures
What are shared, Exclusive and Update Locks?
A Shared Lock Locks a row so that it can only be read.
An Exclusive Lock locks a row so that only one operation can be performed on it at a time.
An Update Lock basically has the ability to convert a Shared Lock into an Exclusive Lock.
How can you get a Deadlock in SQL?
By concurrently running the same resources that access the same information in a transaction.
What is LOCK_TIMEOUT used for?
It is used for determining the time that the system will wait for a lock to be released.
