SQL Server on Linux

Jasmin Azemovic

• Publisher: Packt Publishing
• Year: 2017
• eBook ISBN: 9781788294720
• Edition: 1
• Topic: Computer Science
• Subtopic: Data Processing
• Index: Computer Science

Bring the performance and security of SQL Server to LinuxAbout This Book• Design and administer your SQL Server solution on the open source Linux platform• Install, configure, and fine-tune your database application for maximum performance• An easy-to-follow guide teaching you how to implement various SQL Server CTP 2.x offerings on Linux—from installation to administrationWho This Book Is ForThis book is for the Linux users who want to learn SQL Server on their favorite Linux distributions. It is not important if you are experienced database user or a beginner as we are starting from scratch. However, it is recommended that you have basic knowledge about relational models. More advanced readers can pick the chapters of their interest and study specific topics immediately. Users from Windows platform can also benefit from this book to expand their frontiers and become equally efficient on both platforms.What You Will Learn• Install and set up SQL Server CTP 2.x on Linux• Create and work with database objects using SQL Server on Linux• Configure and administer SQL Server on Linux-based systems• Create and restore database back-ups• Protect sensitive data using the built-in cryptographic features• Optimize query execution using indexes• Improve query execution time by more than 10x using in-memory OLTP• Track row-versioning using temporal tablesIn DetailMicrosoft's launch of SQL Server on Linux has made SQL Server a truly versatile platform across different operating systems and data-types, both on-premise and on-cloud.This book is your handy guide to setting up and implementing your SQL Server solution on the open source Linux platform. You will start by understanding how SQL Server can be installed on supported and unsupported Linux distributions. Then you will brush up your SQL Server skills by creating and querying database objects and implementing basic administration tasks to support business continuity, including security and performance optimization. This book will also take you beyond the basics and highlight some advanced topics such as in-memory OLTP and temporal tables.By the end of this book, you will be able to recognize and utilize the full potential of setting up an efficient SQL Server database solution in your Linux environment.Style and approachThis book follows a step-by-step approach to teach readers the concepts of SQL Server on Linux using the bash command line and SQL programming language trough examples which can easily be adapted and applied in your own solutions.

Implementing Data Protection

Security is a hot topic today. When it comes to the database level, the consequences can be a lot bigger compared to the classic attack on network resources. This is because when all security elements fail (for instance, installation, authentication, authorization, bad access policy, and so on), all that is left is the data. In other words, if database records containing critical information are not protected, then all security elements are irrelevant.

This chapter is focused on data protection using built-in encryption features in SQL Server on Linux. If you want to prevent and minimize consequences of data breach, then this is the right chapter for you. Also, this chapter will teach you how to protect your data assets from inside threats in the form of internal malicious users.

In this chapter, you will learn the following:

• Crash course in cryptography
• Transparent Data Encryption
• Backup encryption
• Symmetric encryption
• Row-level security
• Dynamic data masking

Crash course in cryptography

We use cryptography every day: on the internet, mobile devices, ATM machines, and in almost every aspect of our digital life. In a nutshell, cryptography is about data scrambling and hiding, depending on the implementation and user-specific needs.

A database is the spine of every information system and is the specific target of potential data thieves. SQL Server has one of the best cryptographic set of features that we can use to create a state of the art security and privacy-aware systems.

Cryptography has two major terms:

• Encryption: The process of creating an obfuscated message from plain text using a key
• Decryption: The process of returning plain text from an obfuscated message using a key

From the perspective of how data is encrypted/decrypted there are two types of encryption:

• Symmetric cryptography
• Asymmetric cryptography

It's important to understand how each works and the differences between the two types. Based on that understanding, you will need to make the right decision in concrete business scenarios.

Symmetric cryptography

In symmetric cryptography cases, the sender and recipient share a key that is used to perform encryption and decryption. Symmetric cryptography is the most popular way for encryption in modern IT.

Figure 9-1. Symmetric encryption schema

Some of the most common symmetric algorithms are: Rijndael (AES) and Triple DES (3DES).

Symmetric cryptography is simple because the same key is used for encryption and decryption. However, before communication can occur, the sender and the recipient must exchange a secret key.

The exchange of the shared secret key is the only weakness of symmetric cryptography.

Asymmetric cryptography

With asymmetric cryptography (also known as public key cryptography), the sender encrypts data with one key, and the recipient uses another key for decryption. The encryption and decryption key are known to us as a public/private key pair.

Figure 9-2. Asymmetric encryption schema

The most commonly used asymmetric algorithm is the RSA algorithm.

Asymmetric encryption requires more processing power than symmetric encryption. Because of this, asymmetric encryption is usually optimized by adding a symmetric key to encrypt a message and then asymmetrically encrypt the shared key. This can reduce the amount of data that is asymmetrically encrypted and also improves performance.

What is a key?

A key is used to configure a cryptosystem for encryption and decryption. A fundamental principle of cryptography is that the inner workings of a cryptosystem are completely known to everyone. However, the key is the only secret. This principle is known as the Kerckhoffs' principle (http://www.crypto-it.net/eng/theory/kerckhoffs.html)

From the technical perspective, a key is the product of a specific cryptosystem and is based on randomly collected information, such as random numbers, the temperature of the CPU, sample data in RAM, and so on. The randomly collected information is entered into a cryptosystem which then generates a key.

A key is hard to handle by users because it is long and contains hard readable data. Due to its complexity, a cryptosystem will associate a key with a password. In most cases, the password will trigger the key to start the encryption/decryption process.

In cryptography, the key size or length is measured in bits. A longer key means a more secure system. However, a longer key will affect performance because the encryption process takes longer. Therefore, it is important to choose an appropriate type of encryption and key length.

Both symmetric and asymmetric keys are measured in bits. Despite this similarity, symmetric and asymmetric keys are different. For example, a symmetric key using AES can be 256-bits long, while an asymmetric key using RSA can be as long as 4096 bits. Although 4096 bits may appear more secure than 256 bits, it does not mean that RSA is more secure than AES. Both RSA and AES are different and not comparable.

For example, the security available with a 1024-bit key using asymmetric RSA is considered approximately equal in security to an 80-bit key using a symmetric algorithm.

SQL Server cryptographic elements

As we've defined previously, encryption is the process of obfuscating data by the use of a key or password. This can make the data useless without the corre...

Table of contents

• Title Page
• Copyright
• Credits
• About the Author
• About the Reviewer
• www.PacktPub.com
• Customer Feedback
• Preface
• Linux Distributions
• Installation and Configuration
• SQL Server Basics
• Database in the Sandbox
• Sample Databases
• A Crash Course in Querying
• Backup and Recovery Operations
• User Management
• Implementing Data Protection
• Indexing
• In-Memory OLTP
• Beyond SQL Server