SQL Server 2016 Exploring Contained Databases

Overview

Microsoft SQL Server is an enterprise class relational database management platform and is an integral and indispensable component in most computing environment today with a significant application ecosystem. With the advent of hosted cloud computing and storage, the opportunity to offer a Microsoft SQL Server as an outsourced service is gaining momentum.

This lab guide facilitates step by step guidance for deploying and configuring SQL Server 2016.

Setting up the Lab Environment

Prerequisites

Scope

Requirements

Suggested Hardware Configuration

SQL Server Nodes

Two Servers

  • 4-8 processors
  • 8GB or more RAM

Others

  • This lab assumes that the SQL Server names are 'SQL-SA-01' and 'SQL-SA-02'
  • This lab assumes the file "CD#1instawdbdw.sql" is copied to C:\SQLLabs\SQLScripts folder. The file is attached in the Appendix section at the end of this lab document.

  • This lab assumes that you have enabled mixed mode authentication
  • This lab assumes that you have Enhanced Security Configuration turned off.
  • This lab assumes that you have Java Script execution enabled.
  • This lab assumes that Names Pipes Network Protocol has been enabled in SQL Server.

Contained Databases

SQL Server 2012 introduced the concept of contained databases. A contained database is a database that includes all of the settings and metadata needed for its operation with dependencies on the instance. From a security perspective, a contained database makes it easier to have user accounts that are limited to a single database. SQL Server 2016 supports partially contained databases; it does not yet support full containment. Although contained databases allow more control by the "application administrator" they do have security repercussions.

Enabling Contained Database Roles

By default, creating contained databases is disabled in SQL Server, so you must begin by enabling contained databases.

  1. Connect to SQL-SA-01 computer as a local administrator
  2. Open SQL Server 2016 Management Studio and enter the following details. Server Type: Database Engine Server Name: SQL-SA-01 Authentication Type: Windows Authentication. Click Connect
  3. From the Object Explorer right-click the SQL-SA-01 (SQL Server 13.0.1200 – SQL-SA-01\labuser) instance and click Properties

  1. From the Server Properties – SQL-SA-01 dialog click on the Advanced page.

  1. Set the Enable Contained Databases property to True
  2. Click OK

NOTE: contained databases can also be enabled by executing the following SQL script.

sp_configure 'contained database authentication', 1;
go

Creating a Contained Database from Object Explorer

  1. From the Object Explorer right-click the Databases folder instance and click New Database

  1. Enter SQL-SA-01-ContainedDb for the Database name

  1. Click on Options and set the Containment type to Partial

  1. Click OK

Creating the AdventureWorks Contained Database using a SQL script

  1. From the SQL Server 2016 Management Studio click the File menu then click Open then
    click File

  1. Navigate to the C:\SQLLabs\SQLScripts folder

  1. Open the CD#1instawdbdw.sql script file (snippet below). The CONTAINMENT=PARTIAL highlighted below sets this database to be a partially contained database.

  1. Click the Query menu and set query execution to SQLCMD Mode

  1. Click Execute to create the AdventureWorksDW2016 contained database

Note: successful execution of the script should yield the following results.

  1. Click the refresh button.
  1. Right-click the AdventureWorksDW2016 database and click Properties

  1. Click Options and verify the Containment type is set to Partial


Creating Contained Database Roles

  1. From the Object Explorer open the AdventureWorksDW2016
    Security folder. Right-click Roles then click New then click New Database Role…

  1. Enter awAdmin for the Role name

  1. Click the Securables page and click the Search button

  1. From the Add Objects dialog click the All objects belonging to the schema: radio button and select db_owner in the Schema name: drop down list

  1. Click OK on the Add Objects dialog
  2. Click OK on the Database Role – New dialog

NOTE: the contained databases awAdmin role can also be created by executing the following SQL script.

USE [AdventureworksDW2016CTP3]
GO
CREATE ROLE [awAdmin ]
GO

Creating Contained Database Users

  1. From the Object Explorer open the AdventureWorksDW2016 Security folder. Right-click Users and click New User…

  1. Enter awAdmin1 for the User name and Password@123for the Password and Confirm password

  1. Click Membership and select the awAdmin role and click OK

NOTE: the contained databases users can also be created by executing scripts similar to the following SQL script.

USE [AdventureWorksDW2016CTP3]
GO
CREATE USER [awAdmin1] WITH PASSWORD=N'Password@123', DEFAULT_SCHEMA=[dbo]
GO

Moving a Contained Database

Moving the contained database to a new server (Backup / Restore)

Backup AdventureWorksDW2016 database

  1. From the Object Explorer right-click the AdventureWorksDW2016 database click Tasks then click Backup…

  1. Click Add… in the Destination section of the dialog

  1. Click the ellipses button

  1. Open C: and select the C:\SQLLabs folder and enter AdventureWorksDW2016.bak as the File name

  1. Click OK on the Locate Database Files – SQL-SA-01 dialog
  2. Click OK on the Select Backup Destination dialog
  3. Click OK on the Back Up Database – AdventureWorksDW2016 dialog

  1. Click OK on the Microsoft SQL Server Management Studio message box dialog

Copy the AdventureWorksDW2016 database backup to SQL-SA-02

  1. Copy the AdventureWorksDW2016.bak file

  1. Logon to SQL-SA-02 using SQL-SA-02.rdb (see section 1 above)
  2. Open C:\SQLLabs\ in explorer.
  3. Paste the file on SQL-SA-02 in the C:\SQLLabs\ folder

Note: a progress dialog will be displayed during the transfer

  1. Open SQL Server 2016 Management Studio and enter the following details.

    Server Name: SQL-SA-02

    Authentication Type: Windows Authentication

  2. Click Connect

Restore the AdventureWorksDW2016 database backup to SQL-SA-02

  1. Right-click on Databases and click Restore Database…

  1. Select the Device: radio button and click the ellipsis button

  1. Click the Add button

  1. Open C: and select the SQLLabs folder and select the AdventureWorksDW2016.bak file

  1. Click OK on the Locate Backup File – SQL-SA-02 dialog
  2. Click OK on the Select backup devices dialog

  1. Click OK on the Restore Database – AdventureWorksDW2016 dialog

Note: progress will be displayed during the restore

  1. Click OK on the Microsoft SQL Server Management Studio message box dialog

Moving the contained database to a new server using Deploy Database to a Microsoft Azure VM

NOTE: The following section describes the process to move a contained database using the Deploy Database to a Microsoft Azure VM. This information is provided as a reference and is not part of this lab.

  1. Return to VM SQL-SA-01.
  2. From the Object Explorer right-click the AdventureWorksDW2016 database click Tasks then click Deploy Database to a Microsoft Azure VM…

  1. Click the Next button on the Deploy Database to a Microsoft Azure VM
    Introduction dialog page

  1. Click the Connect button on the Deploy Database to a Microsoft Azure VM Source Settings dialog page

  1. Set the Server name to SQL-SA-01 set the Authentication to Windows Authentication click the Connect button

  1. Click the Next button on the Deploy Database to a Microsoft Azure VM Source Settings dialog page

  1. Click the sign In button on the Deploy Database to a Microsoft Azure VM Sign-in dialog page

  1. Click the Next button on the Deploy Database to a Microsoft Azure VM Sign-in dialog page after choosing the active subscription you have. The screen below shows just sample names of such subscriptions, the real name will depend on what you have.

  1. From the Deploy Database to a Microsoft Azure VM Deployment Settings page set the Cloud Service name to SQL-SA-02 set the Virtual Machine name to SQL-SA-02 set the Storage account to kmportalvhds0wmryld2qbsc click the Settings… button

From the Microsoft Azure Virtual Machine Settings dialog set the Virtual Machine to SQL-SA-02 set the Username to labuser set the Password to Password@123

  1. Click the Next button on the Deploy Database to a Microsoft Azure VM Deployment Settings dialog page

  1. Click the Next button on the Deploy Database to a Microsoft Azure VM Summary dialog page

  1. Verify all tasks succeeded and click the Close button on the Deploy Database to a Windows Azure VM Results dialog page

Additional Information

For additional methods of moving a database see: http://msdn.microsoft.com/en-us/library/ms189624.aspx

Verifying access from the new server

  1. Open SQL Server 2016 Management Studio and enter the following details.

    Server Name: SQL-SA-02

    Authentication Type: Windows Authentication

    Click Connect

Check for moved user and role

  1. From the Object Explorer click the AdventureWorksDW2016 database and open the Security Users folders and verify the user awAdmin1 was moved with the database
  2. Open the Roles Database Roles folder and verify role awAdmin1 was moved with the database

Ensure moved database user can access the database

  1. From the Object Explorer click Connect
    Database Engine…

  1. From the Connect to Server dialog set Authentication to SQL Server Authentication set Login to awAdmin1 set the Password to Password@123then click the Connect button

  1. Notice this results in an error dialog because connected database users must specify a database to connect to.

  1. Click the OK button on the Connect to Server error dialog
  1. Click the Options button on the Connect to Server dialog

  1. Set Connect to database to AdventureWorksDW2016 click the Connect button

  1. Navigate through the AdventureWorksDW2016 objects to verify access.

Using Database Backup Encryption

Create a Backup Certificate

  1. From the SQL Server 2016 Management Studio click the New Query button

  1. Copy the script below into the query window and click the Execute button

use [AdventureWorksDW2016CTP3]
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'p@ssword#1'
go
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'p@ssword#1'
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
go
CREATE CERTIFICATE BackupCertificate WITH SUBJECT = 'backupcertificate'
go

Backup the AdventureWorksDW2016 Database using Encryption

  1. From the Object Explorer right-click the AdventureWorksDW2016 database click Tasks and click Backup…

  1. From the Back Up Database – AdventureWorksDW2016 dialog General page, select Full for the Backup Type: and click the Add… button

  1. From the LocateDatabaseFiles – SQL-SA-01 dialog, enter C:\SQLLabs for the Selected path: and AdventureWorksDW2016_EncryptedBackup.bak for the File name: and click the OK button

  1. Click OK on the Select Backup Destination dialog

  1. From the Back Up Database – AdventureWorksDW2016 dialog Media Options page, select Back up to a new media set, and erase all existing backup sets and enter AdventureWorksDW2016MediaSet for the New media set name:

  1. From the Back Up Database – AdventureWorksDW2016 dialog Backup Options page, click the Encrypt backup check box and specify AES 128 for the Algorithm: and BackupCertificate (Certificate) for the Certificate or Asymmetric key: and click the OK button

Note: progress will be displayed as the backup is generated.

  1. Click OK on the Microsoft SQL Server Management Studio dialog

Restore the AdventureWorksDW2016 Database using Encryption

  1. From the Object Explorer right-click the AdventureWorksDW2016 database click Tasks and click Restore click Database…

  1. From the Restore Database – AdventureWorksDW2016 dialog click the OK button

Note: progress will be displayed as the backup is generated.

Enabling/Disabling the Guest account (Reference only)

Enabling connect access to the guest account

The SQL guest account can be enabled using the following SQL script.

use [AdventureWorksDW2016CTP3];
go
grant connect to GUEST
go

Disabling connect access to the guest account

The SQL guest account can be disabled using the following script.

use [AdventureWorksDW2016CTP3];
go
revoke connect from GUEST
go

Appendix: Contained DB Creation Script

USE [master]
GO


CREATE DATABASE [AdventureworksDW2016CTP3]
 CONTAINMENT = PARTIAL
 ON  PRIMARY 
( NAME = N'AdventureWorksDW2014_Data', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\AdventureWorksDW2016CTP3_Data.mdf' , SIZE = 1501184KB , MAXSIZE = UNLIMITED, FILEGROWTH = 16384KB )
 LOG ON 
( NAME = N'AdventureWorksDW2014_Log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\DATA\AdventureWorksDW2016CTP3_Log.ldf' , SIZE = 2048KB , MAXSIZE = 2048GB , FILEGROWTH = 16384KB )
GO