SQL Server Always Encrypted—Is It Right for My Data?

lock icon, encryptionChances are your company holds sensitive transactional data like personal, financial or health records. This data is often subject to regulations and is also coveted by cybercriminals. Unfortunately, unless encrypted it is vulnerable to compromise both at rest on the server that stores it, and in transit to/from clients that request it.

For Microsoft SQL Server users, Transparent Data Encryption (TDE) has long been available to protect data at rest in the event that database files or backups are compromised. But TDE still leaves encrypted data vulnerable to a privileged user who can access it via the database.

Further, TDE offers nothing to secure SQL Server data in transit. HTTPS can help, but still leaves data exposed to man-in-the-middle (MITM) and other attacks.

To help mitigate these security weaknesses, SQL Server 2016 introduced the Always Encrypted feature to protect sensitive data at rest, in motion and even in memory. Outside an application client’s connection, the data remains encrypted—across the network, server, storage and database.

With Always Encrypted, only users and applications with access to valid keys can decrypt the data, so MTM attacks, insider threats, etc. are nullified. In addition, Always Encrypted lets you encrypt data at the column level, whereas TDE requires encrypting the entire database.

Who should use Always Encrypted?

New privacy regulations and other compliance and data governance mandates are driving more and more SQL Server users to leverage Always Encrypted. It can be a big help with separating those who own and can view the data from those who manage the data but should otherwise not access it.

For example, Always Encrypted lets you protect Personal Information (PI) that you store in the cloud. Even when the data is outside your direct control, the cloud service provider, third-party DBAs, your on-premises admins and other privileged users can’t access it.

Because it is available with all SQL Server 2016 and newer editions (as of SP1), you can try Always Encrypted without purchasing additional licenses. However, the real investment lies in modifying the client application, which will handle the encryption/decryption outside the SQL Server or Azure SQL Database environment.

Always Encrypted cryptography

Always Encrypted is aptly named: from the standpoint of the database engine the data is literally “always encrypted.” But some queries on encrypted data are still supported, depending on the column-level encryption setting.

You can choose either of two encryption types:

  1. Deterministic encryption, which always generates the same encrypted values. This enables the database engine to perform specific operations (point lookups, grouping, equality joins, and also supports indexing.
  2. Randomized encryption, which is even more secure but makes the data write/display-only. Thus, it can’t be evaluated or used in any database operations and cannot be indexed.

The downside of deterministic encryption is that unauthorized users could potentially extrapolate information about the data in encrypted columns by examining patterns within the encrypted values, especially if the set of possible values is small (e.g., true/false). Randomized encryption generates a less predictable result, but precludes indexing, searching, grouping or joining operations on encrypted columns.

For columns of sensitive data that will be used as search or grouping parameters (e.g., US Social Security Numbers), you’ll need to use deterministic encryption. Randomized encryption can be used for data that isn’t grouped with other records and won’t be used to join tables.

History and enhancements

As noted above, Always Encrypted has been available across all SQL Server and Azure SQL Database editions and SQL Database service tiers since SQL Server 2016 (13.x) SP1. Prior to that, it was only available in the Enterprise and Developer Editions.

Beginning with SQL Server 2019 (15.x), the new Always Encrypted with secure enclaves feature extends the database operations you can perform on encrypted data to include pattern matching, other comparison operators and in-place encryption.

A secure enclave is basically a protected memory area specifically for processing Always Encrypted data within SQL Server. To the rest of the SQL Server engine, and to other processes on the host machine, a secure enclave is a “black box” that shields all code and data inside from outside processes, including debuggers.

To validate a secure enclave before sending it encryption keys, etc., both the client-side driver and SQL Server must contact an external attestation service. The process also relies on “enclave-enabled” column master and encryption keys.

While not without complexities and performance overhead, secure enclaves significantly extend the kinds of operations you can perform on Always Encrypted data.

How Always Encrypted works

Before it can process Always Encrypted data, a client application must be configured to use an approved driver, which will automatically encrypt and decrypt columns of sensitive data. The driver encrypts the data before passing it to the database engine. It also needs to automatically rewrite queries to preserve their semantics. When the database returns query results, the driver transparently decrypts these before storing them.

This works because only the client-side application, not the SQL Server environment, has access to the encryption keys. Other applications can retrieve the encrypted values but can do nothing with them.

To encrypt a column of data with Always Encrypted, you must first generate a column encryption key and a column master key. The former encrypts the data and the latter encrypts the former.

The column encryption key resides on the SQL Server instance, while the database engine stores metadata pointing to the column master key’s location. The column master key itself resides in the Windows Certificate Store, Azure Key Vault, a hardware security module or some other trusted external key store. The database engine never sees or stores either key as plaintext.

Always Encrypted use cases

What are some good reasons to try Always Encrypted? Here a few of the top use cases:

  • To improve security of data in transit beyond what SSL can provide.
  • To meet the demands of regulated industries like financial services and telecommunications around protecting Personally Identifiable Information (PII) like credit card numbers, customer names/addresses, etc.
  • To improve security when outsourcing SQL Server DBA services, Always Encrypted ensures separation of duties between third-party DBAs and in-house application administrators.
  • To improve security in scenarios where on-premises client instances need to access sensitive data stored on Microsoft Azure. Because the column master key resides on-premises in a trusted key store, Microsoft admins cannot access the cloud-based data.
  • To improve security in situations where both the client application and database environment are hosted on Azure (as SQL Database or SQL Server instances running in a virtual machine). In this case, both the data and keys are potentially exposed to Microsoft admins on the cloud platform hosting the client tier. However, the data is still always encrypted in the cloud database.

In short, Always Encrypted makes the most sense for protecting PII and other types of data that you need to store, but don’t want to search on or display to application users.

Limitations with Always Encrypted

As noted above, Always Encrypted limits the kinds of operations you can perform on data; e.g., copying data between columns using UPDATE, SELECT INTO, etc. It also restricts the data types you can encrypt. Some of the excluded types include XML, IMAGE, TEXTNTEXT and GEOGRAPHY, as well as user-defined data types.

If you need to query Always Encrypted data from multiple applications, such as reporting tools and data warehouses, you will need to give them access to the appropriate database drivers and decryption certificates. This can be more challenging if you need to replicate encrypted data to other SQL Server instances.

Performance can also be a limiting factor with using Always Encrypted. Encryption/decryption obviously involve compute and storage overhead that will impact both the duration of queries and the volume of storage your application needs. You’ll probably want to do some testing with your specific CPU, RAM and disk setup to gauge the production impact.

Next steps

Want to talk with a database security expert before you dive into Always Encrypted? Contact Buda Consulting to schedule a free consultation.

Migrating an Oracle Schema to PostgreSQL with Partitioning Table(s) in an On-Premises Data Center

Migrating an Oracle Schema to PostgreSQL with Partitioning Table(s) in an On-Premises Data Center

Many companies are looking for open source database solutions to help shrink the IT budget by minimizing or avoiding support costs. One such solution would be migrating an Oracle database to PostgreSQL. PostgreSQL is a widely used open source RDBMS, and the PostgreSQL database model is very similar to Oracle. All PostgreSQL features are available at no cost or minimal cost. Also PostgreSQL professionals are less expensive to hire than Oracle DBAs. 

However, migrating an Oracle database to PostgreSQL is not a straightforward task in an on-premises data center. In particular, it is challenging to migrate an entire Oracle schema with partitioning tables. This technical blog shares a simple method to migrate the Oracle schema to a PostgreSQL database using open source tools. It does not cover installation and configuration of PostgreSQL.

Caution: Every environment is different, so test this in a non-production environment before working with production.

Versions used: Oracle 12.2.0.1, PostgreSQL 12.3
Operating System: CentOS Linux 8.
Tools and utilities used: SQLines Data, SQLine Converter, and PGAdmin 4.x

Pre-migration considerations:

  1. There are quite a few data type differences between Oracle and PostgreSQL. Commonly used types (Oracle –> PostgreSQL) include: VARCHAR2–>VARCHAR, CHAR –>CHAR, CLOB –>TEXT, BLOB RAW \–>BYTEA (1 GB limit) or Large object, DATE–>TIMESTAMP(0), TIMESTAMP WITH LOCAL TIME ZONE –> TIMESTAMPTZ, NUMBER–> NUMERIC, NUMBER(4) –>SMALLINT, NUMBER(9) –> INT, NUMBER(18) –> BIGINT.
  2. No package stored procedure is available in PostgreSQL. Consider converting packages into procedures and functions. There are several differences in built-in functions between Oracle and PostgreSQL. Please find the equivalent function in PostgreSQL user manual.

How To Migrate Oracle To PostgreSQL

I have used the SQLines Data open source tool to do this migration. Once you create all required users (roles) and tablespaces and privileges in PostgreSQL, do the initial load using SQLines Data and exclude all partition tables.

 Synatax : sqldata -sd=oracle,<OracleUser>/<pwd>@<oralcleDBhost>:<OraclePort>/<OracleDBname> -td=pg,<postgresUser>/<pwd>@<hostname>:<PostgreSQLPort>,<PostgreSQLdbname> -t=<Oracleschema>.* -texcl=partitiontables -smap=<Oracleschema>:<PostgresSchema> 

Example: $sqldata -sd=oracle,system/welcome1@orahost1:1521/ORATEST -td=pg,PGTEST/postpwd1@pghost1:5432,TESTDB -t=payroll.* texcl=employee_pay_tab -smap=payroll:payroll  

Truncate all PostgreSQL tables (* truncates only tables in PostgreSQL). 

Get the partition table creation script without partitions from the table DDL and change the data types as appropriate to PostgreSQL.


Example: 

CREATE TABLE payroll.employee_pay_tab (

Pay_period_id numeric(6),

Pay_period  text COLLATE pg_catalog.”default”,

Emp_id numeric(6),

Base_sal numeric(10,2),

fed_tax  numeric(10,2),

state_tax  numeric(10,2),

medicare  numeric(10,2),

ssn_ded numeric(10,2),

ret_ded numeric(10,2),

tot_pay numeric(10,2)

CONSTRAINT XPK_empployee_pay_tab PRIMARY KEY (Pay_period_id,Emp_id) USING INDEX TABLESPACE PAY_INDEX)

PARTITION BY LIST (Pay_period_id);

ALTER TABLE payroll.empployee_pay_tab  OWNER to payroll;

ALTER TABLE payroll.empployee_pay_tab  SET TABLESPACE PAY_DATA;

CREATE TABLE payroll.empployee_pay_tab_P_TEMP PARTITION OF payroll.empployee_pay_tab DEFAULT TABLESPACE PAY_DATA;


Get a partition creation script from the Oracle partition table from the database.

 

set linesize 150

set pages 0

set feedback on

set serveroutput on buffer 999999999;

BEGIN

    FOR r IN (select ‘CREATE TABLE ‘||table_owner||’.’|| partition_name ||’ PARTITION OF ‘||table_owner||’.’||TABLE_NAME || ‘ FOR VALUES IN (‘ as col1 , HIGH_VALUE  , ‘) TABLESPACE PAY_DATA;’ as col2 from dba_tab_partitions where table_owner = ‘PAYROLL’ and table_name=’EMPLOYEE_PAY_TAB’ order by 1) LOOP

        IF r.HIGH_VALUE != ‘-1’ THEN

           DBMS_OUTPUT.PUT_LINE(

              r.col1|| r.HIGH_VALUE || r.col2

              );

        END IF;

     END LOOP;

  END;

 /

 

Get a partition index creation script from the Oracle partition table from the database.

 

set pages 0

set linesize 150

select ‘CREATE INDEX ‘ ||index_name||’ ON payroll.employee_pay_tab (‘||column_name||’) TABLESPACE CE_INDEX;’  from  (select  index_name ,   listagg (column_name, ‘,’) WITHIN GROUP (ORDER BY column_name) column_name FROM dba_ind_columns  where table_owner=‘PAYROLL’ and table_name=’EMPLOYEE_PAY_TAB’ GROUP BY  index_name order by index_name)
;


Create the partition table, partitions and indexes from above scripts in PostgreSQL. Then load the Oracle schema into PostgreSQL, including partition tables, using the SQLine Data tool.

 

Syntax : sqldata -sd=oracle,<OracleUser>/<pwd>@<oralcleDBhost>:<OraclePort>/<OracleDBname> -td=pg,<postgresUser>/<pwd>@<hostname>:<PostgreSQLPort>,<PostgreSQLdbname> -t=<Oracleschema>.* -smap=<Oracleschema>:<PostgresSchema> 

Example: $sqldata -sd=oracle,system/welcome1@orahost1:1521/ORATEST -td=pg,PGTEST/postpwd1@pghost1:5432,TESTDB -smap=payroll:payroll  -topt=truncate

Verify data and create functions, procedures, views and sequences. Get the Sequences scripts from Oracle:

set linesize 150

select ‘CREATE SEQUENCE ‘||SEQUENCE_NAME||’ MINVALUE ‘||MIN_VALUE||’ MAXVALUE ‘||MAX_VALUE||’ INCREMENT BY ‘||INCREMENT_BY||’ START WITH ‘||LAST_NUMBER||’;’  from dba_sequences where SEQUENCE_OWNER=’PAYROLL’;

Get the views, procedure and packages using an Oracle DDL generation command. Use the SQLine converter to convert Oracle code into PostgreSQL. Then get table and column comments scripts from Oracle and run on PostgreSQL.

 

set heading off

set linesize 300

set feedback off

set verify off

set pages 100

col table_name format a30

col column_name format a30

spool  tab_col_comments.sql

select ‘COMMENT ON COLUMN ‘||table_name||’.’||column_name||’  IS ”’||comments||”’;’ from DBA_COL_COMMENTS where owner=‘PAYROLL’ and comments is not null;

select ‘COMMENT ON TABLE ‘||table_name||’ IS ”’||comments||”’;’ 

 from DBA_TAB_COMMENTS   where owner=‘PAYROLL’ and comments is not null;

spool off

 

Now run tab_col_comments.sql in PostgreSQL.

Finally, test your application in a non-production environment.

If you’re thinking of migrating your existing database to an open source platform, contact Buda Consulting for a free consultation on best practices and “lessons learned.”

Watch Your FRA!

Watch Your FRA!

When it comes to Oracle database administration, one of the most revered parts of your database structure is the fast recovery area (FRA). This is an Oracle managed area where DBAs usually store some of the following files:

  • Redo logs
  • Archive logs
  • Flashback logs
  • Control files
  • RMAN backups

The purpose of the Oracle FRA is to simplify database recovery. The Oracle database process automatically manages items stored in the FRA and will delete items that are no longer needed. 

Oracle FRA Settings

Now the Oracle FRA may sound like a magical area that should never run into storage-related issues—but that could not be farther from the truth. Even though the Oracle database process will manage items and attempt to delete files that aren’t needed, the DBA also has to be aware of instance settings that may block Oracle from being able to remove the files. Some settings that can cause storage issues with your FRA include:

  • RMAN backup retention setting – If you set your backup retention to store two weeks’ worth of RMAN backups, but your FRA fills up to 100% before any backups can be purged, this will cause your database to halt.
  • RMAN archive log deletion policy – If you set the deletion policy to delete archive logs after they are applied to all standby databases, but haven’t noticed that your primary and standby databases have been out of sync for a long period of time, your FRA can fill to 100% and cause your database to halt.
  • RMAN archive log backup copies setting – By default, backup copies are set to 1. But what if you want to make sure your backups contain more copies of your archive logs in the event that one of your incremental backups became corrupted? When you set this setting higher than 1, you will not be able to delete any archive logs unless they have been backed up however many times this setting is set to. So if you set this option to 3, you will need to have taken at least three backups of each archive log before said log can be deleted from your system. If you opted to store archive logs in your FRA, then this can fill the FRA to 100% and cause your database to halt.
  • Db_flashback_retention_target setting – If you have enabled the flashback database option this is stored in the FRA by default. As with the archive logs, depending on the time value of the setting, it will store all flashback logs needed to guarantee that you can flashback your database as per the setting. If you set this to a high setting, this can fill the FRA to 100% and cause your database to halt.

Those are just a handful of the many ways you can accidentally fill your Oracle FRA, which is why you need to make sure that your FRA is adequately sized to store all files as per all retention settings. You should also create a script that queries the v$recovery_area_usage and have this result sent to the email of all DBAs, as this will tell you how much of your FRA is used and what in particular is taking up the space:

For remote and onsite DBA support to help keep your databases running smoothly, including 24×7 live support, contact Buda Consulting.

In-Place Oracle Database Encryption with Zero Downtime

Have you been wanting to encrypt your Oracle database “since forever,” but feel like you just can’t afford the downtime? If a lot of data is involved, taking it all offline and encrypting it could be very time-consuming. So you’ve been putting the process off, while keeping your fingers crossed that your company’s network security will somehow protect you from a data breach and associated legal, compliance and reputational impacts. 

But did you know that you can now encrypt existing tablespaces in-place, either online or offline in Oracle? In case you missed it, Oracle Enterprise Edition version 12.2 (released in 2017) added Transparent Data Encryption (TDE), a much-needed feature that enables you to encrypt an existing database while it remains online. 

If you’ve been running an earlier Oracle version and haven’t seen a compelling reason to update, TDE could be it. This capability is a game-changer for those who want to “do the right thing” and encrypt their data at rest, but haven’t wanted to incur the downtime.

At a high level, here is how TDE works:

    • First, encrypt the system tablespaces (these must be done separate from user tablespaces)
    • Next, encrypt the user tablespaces, one at a time. 
    • Finally, drop and recreate any temporary tablespaces (these cannot be converted online)

That’s basically all there is to it! There are some technical issues that your DBA and/or security group will need to work out, such as key management and disk space. (You must have enough available disk space during the conversion to duplicate your largest tablespace.)

Of course, you need to back up your entire database before you start the encryption process. If you decide to tackle encryption gradually, then just back up each tablespace before you convert it.

Taking the important step of encrypting your sensitive data at rest will significantly improve your security posture.

So what are you waiting for? Get encrypting!

To schedule a free consultation on your database security, including encryption requirements, contact Buda Consulting.

Ensure Redundant Database Coverage, During Covid-19 and Always

Ensure Redundant Database Coverage, During Covid-19 and Always

One of the principles of Buda Consulting’s redundant database coverage is that we always have at least two people who know your databases and your environment. This best practice is especially important at this time.

In normal times, this helps with responsiveness and cuts down on required knowledge transfer when a problem occurs or when an urgent change is necessary.

But the impact that Covid-19 is having on all of us illustrates another, more important reason. 

Fortunately, most people that contract the illness get minor symptoms and recover quickly.  But sadly, those that get very sick, are incapacitated very quickly.  I personally know two people, both relatively young, that died from Covid within 5 days of first showing symptoms. One within two days. The progression of the symptoms is very rapid, incapacitating the virus’ most unfortunate victims very quickly.  Leaving little time to make preparations.

While it may seem like a trivial matter when considering the magnitude of loss that many are suffering, losing your critical data when your business is struggling to adjust to the rapidly changing environment can be debilitating for your business.

If your databases are housing mission-critical data, best practice at this time and always is to have more than one person who has the knowledge and ability to manage your database systems and to respond to any problems that arise.  After your sole DBA gets sick may simply be too late. 

Don’t wait, make sure that at least two people in your organization know how to connect to your databases and are familiar with the environment today.  Or reach out to a trusted redundant database coverage service provider like Buda Consulting and engage someone else to back you up. It has never been more critical to do so.

Stay safe!

Test Your Disaster Recovery Strategies before Disaster Strikes

I’m sure you have heard—if not experienced—the following scenario. A student is working on a research paper and suddenly her PC crashes. Because she did not follow the golden rule of saving your document every sixty seconds, she lost hours of work. 

You would think by the time you are well into your career things like this would no longer happen, but unfortunately this kind of thing still happens all the time. When it comes to data, the one thing most people think about is backups. As long as the backups complete without error you feel safe, as you believe you have all of the files you need to restore your database in the event of a disaster. 

But what if I told you that is not a complete disaster recovery strategy?  

We saw this issue play out recently when we were contacted by a company that needed our assistance. The client was trying to restore a database after temporarily losing power and encountered a software bug that was requesting an old archive log that was applied to the database, which happened to be a standby database. Because the archive log requested was so old (a search of emailed backup job logs found the archive log was backed up 9 months prior) and their retention policy only saved backups for 14 days, there was no way for them to get the archive log back. This meant they were not able to restore their data. Long story short: the company lost all of the data in the database.

When one side of our disaster recovery strategy is working we often overlook the second side of the strategy, which is making sure we are able to restore our database using the files created. While the backup job may complete without errors, file corruption or erroneously deleting one of the backup files can render your recovery plan and data useless. This is why we here at Buda Consulting always recommend that our clients perform biannual disaster recovery restore tests at a minimum, with quarterly disaster recovery restore tests at a maximum. 

As the old saying goes, “It’s better to be safe than sorry,” and testing your disaster recovery data is essential to keeping you and your data safe!

Concerned (as you should be) about your disaster recovery and business continuity capability? Contact Buda Consulting to schedule a free consultation.