Protect Your SQL Servers with Always Encrypted

by Dec 2, 2020

Categories

Tags

Administration agent-based monitoring Agentless Monitoring alert responses alert thresholds alerting Alerts Amazon Aurora Amazon EC2 Amazon RDS Amazon RDS / Aurora Amazon RDS for SQL Server Amazon Redshift Amazon S3 Amazon Web Services (AWS) Analytics application monitoring Aqua Data Studio automation availability Azure Azure SQL Database azure sql managed instance Azure VM backup Backup and recovery backup and restore backup compression backup status Backup Strategy backups big data Blocking bug fixes business architecture business data objects business intelligence business process modeling business process models capacity planning change management cloud cloud database cloud database monitoring cloud infrastructure cloud migration cloud providers Cloud Readiness Cloud Services cloud storage cloud virtual machine cloud VM clusters code completion collaboration compliance compliance audit compliance audits compliance manager compliance reporting conference configuration connect to database cpu Cross Platform custom counters Custom Views customer survey customer testimonials Dark Theme dashboards data analysis Data Analytics data architect data architecture data breaches Data Collector data governance data lakes data lineage data management data model data modeler data modeling data models data privacy data protection data security data security measures data sources data visualization data warehouse database database administration database administrator database automation database backup database backups database capacity database changes database community database connection database design database developer database developers database development database diversity Database Engine Tuning Advisor database fragmentation database GUI database IDE database indexes database inventory management database locks database management database migration database monitoring database navigation database optimization database performance Database Permissions database platforms database profiling database queries database recovery database replication database restore database schema database security database support database synchronization database tools database transactions database tuning database-as-a-service databases DB Change Manager DB Optimizer DB PowerStudio DB2 DBA DBaaS DBArtisan dBase DBMS DDL Debugging defragmentation Demo diagnostic manager diagnostics dimensional modeling disaster recovery Download drills embedded database Encryption End-user Experience entity-relationship model ER/Studio ER/Studio Data Architect ER/Studio Enterprise Team Edition events execution plans free tools galera cluster GDPR Getting Started Git GitHub Google Cloud Hadoop Healthcare high availability HIPAA Hive hybrid clouds Hyper-V IDERA IDERA ACE Index Analyzer index optimization infrastructure as a service (IaaS) infrastructure monitoring installation Integrated Development Environment interbase Inventory Manager IT infrastructure Java JD Edwards JSON licensing load test load testing logical data model macOS macros managed cloud database managed cloud databases MariaDB memory memorystorage memoryusage metadata metric baselines metric thresholds Microsoft Azure Microsoft Azure SQL Database Microsoft PowerShell Microsoft SQL Server Microsoft Windows MongoDB monitoring Monitoring Tools Monyog multiple platforms MySQL news newsletter NoSQL Notifications odbc optimization Oracle PeopleSoft performance Performance Dashboards performance metrics performance monitoring performance schema performance tuning personally identifiable information physical data model Platform platform as a service (PaaS) PostgreSQL Precise Precise for Databases Precise for Oracle Precise for SQL Server Precise Management Database (PMDB) product updates Project Migration public clouds Query Analyzer query builder query monitor query optimization query performance Query Store query tool query tuning query-level waits Rapid SQL rdbms real time monitoring Real User Monitoring recovery regulations relational databases Releases Reporting Reports repository Restore reverse engineering Roadmap sample SAP Scalability Security Policy Security Practices server monitoring Server performance server-level waits Service Level Agreement SkySQL slow query SNMP snowflake source control SQL SQL Admin Toolset SQL CM SQL code SQL coding SQL Compliance Manager SQL Defrag Manager sql development SQL Diagnostic Manager SQL Diagnostic Manager for MySQL SQL Diagnostic Manager for SQL Server SQL Diagnostic Manager Pro SQL DM SQL Doctor SQL Enterprise Job Manager SQl IM SQL Inventory Manager SQL Management Suite SQL Monitoring SQL Performance SQL Quality SQL query SQL Query Tuner SQL Safe Backup SQL script SQL Secure SQL Security Suite SQL Server sql server alert SQL Server Migration SQL Server Performance SQL Server Recommendations SQL Server Security SQL statement history SQL tuning SQL Virtual Database sqlmemory sqlserver SQLyog Storage Storage Performance structured data Subversion Support tempdb tempdb data temporal data Tips and Tricks troubleshooting universal data models universal mapping unstructured data Uptime Infrastructure Monitor user experience user permissions Virtual Machine (VM) web services webinar What-if analysis WindowsPowerShell

The information stored in corporate databases needs to be protected in multiple ways. Database backups are necessary to recover from a physical or logical disaster requiring systems to be rebuilt. Firewalls and other network security measures need to be taken to keep unauthorized users from accessing data resources. Access to sensitive data must be restricted to those who need it and even then should be monitored for potential abuse.

As anyone who has been in IT for any length of time knows, things don’t always work out the way they were planned. Firewalls can fail or be compromised and an innocent oversight may expose sensitive data to numerous individuals. Backup media can be stolen, putting all the information they contain at risk. Despite best efforts, there is always a chance that enterprise data assets will fall into the wrong hands.

Encryption Provides Additional Protection

One of the most effective methods of implementing and maintaining data security is encryption. In simple terms, encryption is the process of taking information in human-readable form (plain text) and transforming it into an unreadable format (ciphertext). The encoded data has to be decrypted before it can be read or processed. Encryption blocks data from view while decryption reveals it. 

An encryption key, which is a unique and random numerical sequence used by the encryption algorithm, is necessary to decrypt the data. Encryption can be symmetric, in which a single password is used for the encoding and decoding procedures. Two keys are used for asymmetric encryption. Data is encrypted with a shared public key and decrypted using a private key that is not shared.

Data exists in three distinct states which present different challenges for those attempting to keep it secure.  

  • Data-at-rest is stored on a physical device like a disk drive. This type of data is the easiest to protect with techniques like full disk encryption. The problem is that for data to be useful, it cannot remain at rest forever. 
  • Data-in-motion is moving through a network. Examples of data-in-motion are uploading and downloading files from the cloud or sending emails. Data is vulnerable while in motion and needs to be encrypted to make it unusable if it is intercepted by malicious entities.
  • Data-in-use is currently being accessed or processed. This class of data is seen in active databases or an open file and poses challenges from an encryption perspective. The data must be in a form that can be viewed by the user or application that requested it. Keeping this type of data secure is often done through identity management and tightly controlling access.

How Always Encrypted Protects Your SQL Server Data

Microsoft’s Always Encrypted is a feature that is incorporated into SQL Server versions since SQL Server 2016 and is also available in Azure SQL Database. Its purpose is to protect sensitive data by allowing clients to encrypt data inside client applications without exposing the encryption keys to the database engine. In this way, there is a separation between the data owners who have the right to view it and the team that manages the information but should not be able to access it.

One of the weak points in providing security for sensitive data resources is the level of permission and access that DBAs and system administrators need to perform their jobs. Insider data breaches are a growing problem that can be difficult to fully address using traditional security and encryption techniques. Always Encrypted offers a method for ensuring that data remains encrypted at all times.

Two types of encryption can be implemented using Always Encrypted. Deterministic encryption can be queried and indexed. It’s good for data that encompasses large-scale value ranges. Randomized encryption makes data unidentifiable by assigning different values for similar data. This type of encryption is more secure but cannot be searched.

An IDERA webcast should be required viewing for teams interested in implementing Always Encrypted on their SQL Servers. It explains the inner workings of the encryption method and will help make the correct choices when configuring Always Encrypted for your environment. The most important factor to be considered is how the data will be used. Many of the other decisions related to implementation flow from the answer to that question.

Identifying SQL Server Security Flaws

It is impossible to effectively protect a SQL Server environment without a thorough knowledge of how permissions are granted and who has access to sensitive data. The complications involved with granting access to SQL Server make it important to have the right software tools to ensure security is fully implemented. Leaving the process up to manual methods just provides another potential point of failure.

IDERA’s SQL Secure offers teams a valuable solution that can help lock down a SQL Server environment with advanced security analysis and reporting capabilities. Identify and analyze powerful group membership to verify that the level of access is required. Predefined templates define guidelines to protect systems from common intrusion attacks. Risk assessment can be performed on database access, permissions, and configuration.

Database security is a multi-faceted undertaking that will benefit from a combination of Always Encrypted and SQL Secure. You can’t be too careful with sensitive enterprise data. It pays to use all the tools at your disposal to ensure that only the right people have access to data resources.