My articles published on SearchSQLServer.com from January 2014 to September 2014

The following is the list of my articles published on SearchSQLServer.com from January 2014 to September 2014. To read these articles, you must be a member of SearchSQLServer.com.

SearchSQLServer.com


Learn how to use SQL Server Import and Export Wizard, Published on September 3, 2014

SQL Server 2014 provides a variety of methods to transfer data between SQL Server databases and Microsoft Excel documents. One method is the SQL Server Import and Export Wizard, and this article provides a step-by-step guide for using this wizard to transfer data between SQL Server databases and Microsoft Excel worksheets.


The key to boosting OLTP with SQL Server 2014 In-Memory, Published on August 8, 2014

The SQL Server 2014 in-memory OLTP engine — project name Hekaton — is one of the key new performance-related architectural enhancements to the SQL Server database engine. It is designed to boost performance of OLTP and data-warehouse workloads and reduce processing times. The SQL Server 2014 in-memory OLTP engine is supported on 64-bit Enterprise, Developer and Evaluation editions of SQL Server 2014.

This article explains how to improve the performance of OLTP applications using SQL Server 2014 in-memory OLTP engine. This article also details what makes SQL Server 2014 in-memory OLTP engine different from other vendors in-memory OLTP engines and how to use that to your advantage.


Top four tips for virtualizing your SQL Server instance, Published on July 28, 2014

The increasing need for SQL Server instances is raising costs. Therefore, controlling the cost of databases and related hardware resources and improving and expanding them as necessary have become increasingly important. An ideal solution to this problem is virtualization.

This article shows you how to control the costs through effective virtualization.


The top three SQL Server third-party tools, Published on June 30, 2014

Monitoring and optimizing SQL Server performance, as well as identifying and fixing performance bottlenecks in SQL Server databases, can be a daunting task for database administrators (DBAs), developers and application support teams. SQL Server performance is influenced by factors like server hardware, operating system setup and database configuration. SQL Server provides a comprehensive set of tools for monitoring its own performance, but many SQL Server DBAs, developers and application support teams choose to use third-party performance monitoring tools.

SQL Server third-party performance monitoring tools are more flexible than the tools that ship with the product, and they provide a centralized repository for performance-related data. Moreover, they are easy to use and provide real-time visibility into all SQL Server performance. Third-party monitoring tools can help monitor SQL Server and database performance, resolve potential problems with threshold-based and trend-based alerts, and isolate the root cause of application performance issues.

This article explores three of the top SQL Server third-party tools for quickly identifying and resolving performance bottlenecks.


Tips to escape a SQL Server pitfall or performance bottleneck, Published on April 29, 2014

Over the last decade, many complex enterprise applications have been developed and deployed using Microsoft SQL Server. Today, SQL Server is a cornerstone of modern business applications and is at the center of the business processes of many leading companies. SQL Server applications range from line-of-business applications in production, to internal customer relationship management and decision support systems, to customer-facing e-commerce and Web self-service applications. Consequently, SQL Server performance and scalability are high on IT priority lists, and delivering optimal SQL Server performance and scalability is one of the key tasks of all SQL Server DBAs.

However, many SQL Server systems suffer from poor performance and scalability, often caused by poor database design, index design and a SQL Server system improperly configured for the workload. The reason for this is that the main goal of the development process of any large-scale SQL Server project is functionality, with performance and scalability frequently treated as an afterthought.

While troubleshooting SQL Server database system performance problems is a difficult task, significant performance improvements can be achieved with a relatively small time investment. This article provides five tips for SQL Server users, from avoiding a hardware performance bottleneck to dealing with database design.


Dive deep into SQL Server 2014 in-memory OLTP, Published on March 14, 2014

The SQL Server 2014 in-memory online transaction processing (OLTP) engine, previously code-named “Hekaton“, allows you to create in-memory optimized OLTP tables within a conventional relational database. SQL Server 2014 in-memory OLTP engine enables customers to build mission-critical applications and Big Data solutions using high-performance, in-memory technology across OLTP. It is one of the key new performance-related architectural enhancements to SQL Server 2014, although it is only supported on 64-bit Enterprise, Developer and Evaluation editions.

Checkout this article to learn how to set it up.


Defining half a dozen daily SQL Server DBA responsibilities, Published on February 20, 2014

SQL Server database administration can be a complex and stressful job. Database administrators’ responsibilities cover the performance, integrity and security of business data and SQL Server databases. To fulfil their duties and to make business data available to its users, database administrators have to perform routine DBA checks on their SQL Servers to monitor their status.

So, what critical aspects of SQL Server should all DBAs include in their daily checklist? This article highlights six daily DBA responsibilities that every SQL Server manager should perform.


SQL Server encryption features in SQL Server 2014, Published on February 6, 2014

Data security is a vital and growing concern for many organizations due to the increasing loss and unauthorized disclosure of confidential data. Regulatory requirements often require robust encryption solution for data such as credit card and Social Security numbers. SQL Server 2014 provides database administrators with several options to encrypt data when transmitted through the network, while creating a backup, or when stored on the server or network.

This article guides you through the different encryption options that are available in SQL Server 2014 to encrypt confidential data in the SQL Server database.


Digging through SQL Server OLAP storage models, Published on January 28, 2014

The physical storage design for your multidimensional applications affects the latency, size and performance of your project. Therefore, when designing the physical storage for it, you must determine how to store the dimensions and measures, and how to design an effective physical storage strategy for multidimensional applications.

This article shows how to distinguish among the three SQL Server OLAP storage models and decide which one is best in different use cases.


Eight key SQL Server 2014 features, Published on January 23, 2014

Microsoft SQL Server is an enterprise database server that is the cornerstone of modern business applications and is in the center of the business processes of many leading organizations. The latest release of Microsoft SQL Server, SQL Server 2014, has many new features that let you design, build, and deploy high-performance mission-critical OLTP applications and Big Data solutions.

This article reviews the eight key new SQL Server 2014 Database Engine features.


I hope you will find these articles useful. 🙂

Have a good week!

Basit

Advertisements

SQL Server Encryption Options

Many database servers store confidential data, which must be protected from unauthorized access when it’s transmitted across the network and stored on the server. SQL Server provides support for encrypted connections, encrypting data, encrypting database and for encrypted storage.

Click here to read full article on SSWUG.org

SQL Server Encryption

Today, I am very happy because my first article (SQL Server Encryption) was published in SQL Server Pro. In this article, after providing an overview of the SQL Server encryption model, I described the encryption options available in SQL Server and how you can use them to encrypt sensitive information stored inside your SQL Server databases.

Using Cell-Level Encryption in SQL Server

Industry guidance such as the Payment Card Industry Data Security Standard (PCI-DSS), Healthcare Insurance Portability and Accountability Act (HIPAA) and numerous state privacy breach notification laws require the use of encryption for sensitive data such as credit card numbers, security related data etc. To meet these compliance and regulatory requirements, SQL Server provides users the choice between transport-level encryption, cell-level encryption, full database-level encryption by using TDE, or the file-level encryption options provided by Windows. I’ve already discussed the database-level and transport-level encryption in my previous blog posts here and here respectively. In this blog post, I’m going to show you the implementation of cell-level encryption.

What is Cell-level encryption?

The Cell-level encryption is a series of built-ins and the key management hierarchy. This option follows a manual process that requires a redesign of the application to call the encryption and decryption functions. In addition, you also must modify the schema to store the data as varbinary and then recast back to appropriate data type when read.

The SQL Server encryption key hierarchy

Microsoft Reference: http://technet.microsoft.com/en-us/library/cc966395.aspx

Comparing the Cell-level encryption options

When encrypting data, you need to choose the most appropriate encryption options. The options available are described as follow:

  • Passphrase – A password that can contain spaces. A passphrase is the least secure option. It requires you to use the same passphrase when encrypting and decrypting the data. If stored procedures and functions aren’t encrypted, the passphrase is accessible through metadata.
  • Asymmetric key – Offers strong protection by using a different key to encrypt and decrypt the data. However, it provides poor performance and shouldn’t be used to encrypt large values. Can be signed by the database master key or created using a password.
  • Symmetric key – Offers good performance and is strong enough for most requirements. Uses the same key to encrypt and decrypt the data.
  • Certificate – Offers strong protection and good performance. Can be associated with a user. A certificate must be signed by the database master key.

Important Built-In functions for Cell-level encryption

Encryption

Decryption

Demo: Cell-level Encryption

For the purpose of this demo, I’m going to use AdventureWork2012 database.

Create Database Master Key (DMK):

USE AdventureWorks2012
GO

-- Creates a database master key encrypted by password $Str0nGPa$$w0rd
CREATE MASTER KEY ENCRYPTION BY PASSWORD  = '$tr0nGPa$$w0rd'
GO

Encrypting data using Symmetric Key encrypted by Asymmetric Key – The purpose of creating is the Asymmetric key is to encrypt our Symmetric Key.

Create Asymmetric Key:

USE AdventureWorks2012
GO

-- Creates an asymmetric key encrypted by password '$e1ectPa$$w0rd'
CREATE ASYMMETRIC KEY MyAsymmetricKey
	WITH ALGORITHM = RSA_2048 ENCRYPTION BY PASSWORD = '$e1ectPa$$w0rd'
GO

Execute the query below, to view the information about asymmetric key:

USE [AdventureWorks2012]
GO

SELECT * FROM [sys].[asymmetric_keys]
GO

Create Symmetric Key:

USE [AdventureWorks2012]
GO

-- Creates an symmetric key encrypted by asymmetric key
CREATE SYMMETRIC KEY MySymmetricKey
    WITH ALGORITHM = AES_256
    ENCRYPTION BY ASYMMETRIC KEY MyAsymmetricKey
GO

Execute the query below, to view the information about symmetric key:

USE [AdventureWorks2012]
GO

SELECT * FROM [sys].[symmetric_keys]
GO

For demonstration purposes, I’m going to create a table called TestEncryption. This table has three columns Name, CreditCardNumber and EncryptedCreditCardNumnber. The EncryptedCreatedCardNumber stores the encrypted credit card number stored in CreditCardNumber column. Also insert some dummy data into this table for demonstration purposes. Execute the following code to create this table and insert dummy data:

USE [AdventureWorks2012]
GO

CREATE TABLE TestEncryption (
	[Name] [varchar](256)
	,[CreditCardNumber] [varchar](16)
	,[EncryptedCreditCardNumber] [varbinary](max)
	)
GO

INSERT INTO TestEncryption (
	[Name]
	,[CreditCardNumber]
	)
SELECT 'Simon Jones'
	,'9876123456782378'

UNION ALL

SELECT 'Kim Brian'
	,'1234567898765432'
GO

SELECT *
FROM TestEncryption
GO

ResultSet:

USE [AdventureWorks2012]
GO

-- Opening the symmetric key
OPEN SYMMETRIC KEY MySymmetricKey
DECRYPTION BY ASYMMETRIC KEY MyAsymmetricKey
WITH PASSWORD  = '$e1ectPa$$w0rd'
GO

Execute the following query returns the list of opened key:

USE [AdventureWorks2012]
GO

SELECT * FROM [sys].[openkeys]
GO

Now execute the following script update the TestEncryption table to insert the values in EncryptedCreditCardNumbers column from CreditCardNumbers column:

USE [AdventureWorks2012]
GO

--As you can see we are using ENCRYPTBYKEY function to encrypt the column values
UPDATE TestEncryption
SET [EncryptedCreditCardNumber] = ENCRYPTBYKEY(KEY_GUID('MySymmetricKey'), CreditCardNumber)
GO

Once successfully executed, Verify the value inside EncryptedCreditCardNumber column by running the following query:

USE [AdventureWorks2012]
GO

SELECT * FROM [TestEncryption]
GO

ResultSet:

Executing the following query to retrieve the data inside EncryptedCreditCardNumber column using DECRYPTBYKEY encryption function:

USE [AdventureWorks2012]
GO

SELECT CONVERT([varchar](16), DECRYPTBYKEY([EncryptedCreditCardNumber]))
 FROM [TestEncryption]
GO

ResultSet:

Encrypting data using symmetric key encrypted using Passphrase – In this script, The data is encrypted using symmetric key using Phaseprase (see below):

USE [AdventureWorks2012]
GO

-- Creating symmetric key encrypted by password
CREATE SYMMETRIC KEY MySymmetricKeyPwd
	WITH ALGORITHM = AES_256 ENCRYPTION BY PASSWORD = 'RememberMe!'
GO

-- Opening the symmetric key
OPEN SYMMETRIC KEY MySymmetricKeyPwd DECRYPTION BY PASSWORD = 'RememberMe!'
GO

-- Add EncryptedCreditCardNumber2 column in the Test encryption table.
-- This column stores the data encrypted using key encrypted by Passphrase.
ALTER TABLE TestEncryption ADD [EncryptedCreditCardNumber2] [varbinary] (max)
GO

--As you can see we are using ENCRYPTBYKEY function to encrypt the column values
UPDATE [TestEncryption]
SET [EncryptedCreditCardNumber2] = ENCRYPTBYKEY(KEY_GUID('MySymmetricKeyPwd'), CreditCardNumber)
GO

SELECT *
FROM [TestEncryption]
GO

ResultSet:

Encrypting data using Certificate – You can also encrypt the data using Certificate as follow:

USE [AdventureWorks2012]
GO

-- Creates a certificate
CREATE CERTIFICATE MyCertificate
	WITH SUBJECT = 'Demo Cert'
		,EXPIRY_DATE = '10/31/2050'
GO

-- Creating symmetric key encrypted by password
CREATE SYMMETRIC KEY MySymmetricKeyCert
	WITH ALGORITHM = AES_256 ENCRYPTION BY CERTIFICATE MyCertificate
GO

-- Opening the symmetric key
OPEN SYMMETRIC KEY MySymmetricKeyCert DECRYPTION BY CERTIFICATE MyCertificate
GO

-- Add two more columns in the Test encryption table.
-- EncryptedCreditCardNumber3 column stores the data encrypted directly using certificate.
-- EncryptedCreditCardNumber4 column stores the data encrypted key encrypted using certificate.
ALTER TABLE TestEncryption ADD [EncryptedCreditCardNumber3] [varbinary] (max)
	,[EncryptedCreditCardNumber4] [varbinary] (max)
GO

--You either encrypt the data directly using certificate
UPDATE [TestEncryption]
SET [EncryptedCreditCardNumber3] = ENCRYPTBYCERT(CERT_ID('MyCertificate'), CreditCardNumber)
GO

SELECT *
FROM sys.certificates

--You either encrypt the data directly using certificate
UPDATE [TestEncryption]
SET [EncryptedCreditCardNumber4] = ENCRYPTBYKEY(KEY_GUID('MySymmetricKeyCert'), CreditCardNumber)
GO

-- Examine the encrypted columns
SELECT [EncryptedCreditCardNumber3]
	,[EncryptedCreditCardNumber4]
FROM [TestEncryption]
GO

-- Reading data by decrypting data of [EncryptedCreditCardNumber3] using DECRYPTBYCERT function
SELECT CONVERT([varchar](16), DECRYPTBYCERT(CERT_ID('MyCertificate'), [EncryptedCreditCardNumber3])) AS [CreditCardNumber]
FROM [TestEncryption]
GO

-- Reading data by decrypting data of [EncryptedCreditCardNumber4] using DECRYPTBYKEY function
SELECT CONVERT([varchar](16), DECRYPTBYKEY([EncryptedCreditCardNumber4])) AS [CreditCardNumber]
FROM [TestEncryption]
GO

ResultSet:

Benefits of using Cell-level encryption

  • Cell-level encryption offers more granular level of encryption.
  • Data is not decrypted until it is used so that even the page is loaded into memory, sensitive data is not in clear text.
  • Key can be assigned to users and protect by password to prevent automatic decryption.

Drawbacks of using Cell-level encryption

  • Administrator is burdened with maintaining the keys.
  • For cell-level encryption, the performance impact on SQL Server optimization is 20% more than it is for Transparent Data Encryption (TDE).

Conclusion

Encryption is the process of hiding data using a key or password. This can make the data useless without the corresponding decryption key or password. Encryption does not solve the problems of access control. However, it improves security by limiting data loss even if access controls are omitted.

References