Standard Geo-Replication

Standard Geo-Replication allows for the creation of (at most) one secondary replica (non-readable) to a Microsoft designated “DR Pair” region (see Figure 9-9). The list of Microsoft designated DR pair regions can be found at:

Figure 9-9. Representational view of the standard Geo-Replication

http://blogs.msdn.com/b/windowsazurestorage/archive/2013/12/11/introducing-read-access-geo-replicated-storage-ra-grs-for-windows-azure-storage.aspx

The secondary replica is non-readable while the primary is up and running. A manual failover is required to make the database available for user access. Standard Geo-Replication provides the classic DR scenario, wherein if the primary replica has some issue, the secondary can be immediately brought online.

Standard Geo-Replication can be set up using either the Management Portal, as illustrated in Figures 9-10 through 9-12, or by using PowerShell (see Listing 9-3).

Figure 9-10. Configuring standard Geo-Replication

Figure 9-11. Configuring standard Geo-Replication options

Figure 9-12. Configuring standard Geo-Replication options

Clicking on the Geo-Replication Role option opens up the window to set up Geo-Replication. This can also be configured by clicking on All Settings and then choosing Geo-Replication.

When on the Geo-Replication setup page, you get an option to specify the secondary server. As mentioned earlier with standard Geo-Replication, only a “DR Pair” region is allowed as the secondary region.

Just as with the Azure Portal, PowerShell can be used to set up standard Geo-Replication (see Listing 9-3).

Listing 9-3. Configuring Standard Geo-Replication Using PowerShell

$resourceGroupName = "Default-SQL-SoutheastAsia"
$DbServerName = "primarysvr"
$SecondaryServerName = "secondsvr"

$DBName = Get-AzureRmSqlDatabase -ServerName $DbServerName -ResourceGroupName $resourceGroupName | Where-Object {($_.Edition -eq "Standard") -and ($_.DatabaseName -ne "master")}

$replicationLink = New-AzureRmSqlDatabaseSecondary -DatabaseName $DBName[0].DatabaseName -ServerName $DbServerName -ResourceGroupName $resourceGroupName -PartnerResourceGroupName $resourceGroupName -PartnerServerName $SecondaryServerName -AllowConnections No

if($replicationLink -ne $null)
{
    Write-Host "Standard Geo Replication Setup successfully!!"
}

Since the database in this case is configured with the standard tier, the only option allowed here is to have a non-readable secondary copy. This property can be verified using the Management Portal (see Figure 9-13).

Figure 9-13. Only a non-readable secondary copy is available in this case

Once replication is configured, the status of the database changes accordingly. For example, as shown in Figure 9-14, the primary database would show the Geo-Replication role status as Primary, while the secondary copy would show up as Secondary.

Figure 9-14. Database status has changed

Performing Database Failover

The Azure Portal provides a very easy one-click mechanism to failover the database when the primary database is down (see Figure 9-15). You simply need to browse to the Geo-Replication settings and then click on the secondary database and initiate failover. Azure also provides a mechanism to stop replication if required.

Figure 9-15. Performing database failover

PowerShell can also be used to failover a database to the secondary replica.

Set-AzureRMSqlDatabaseSecondary -DatabaseName $DBName[0].DatabaseName -PartnerResourceGroupName $resourceGroup -ResourceGroupName $resourceGroup -ServerName $SecondaryServer –Failover -AllowDataLoss

Active Geo-Replication

Active Geo-Replication uses the same technology as the standard Geo-Replication, but has the following differences (see Figure 9-16):

• The secondaries are readable.

• There can be up to four secondaries in any data center across the globe. Users can create the secondary in any data centre, irrespective of the DR Pair regions.

• Active Geo-Replication is available only for the Premium tier databases.

Figure 9-16. Representation view of the Active Geo-Replication

As with Standard Geo-Replication, Active Geo-Replication can be set using the Azure Portal, as shown in Figures 9-17 and 9-18, or by using PowerShell, as shown in Listing 9-4.

Figure 9-17. Configuring Active Geo-Replication

Figure 9-18. Initialization of Active Geo-Replication

As mentioned, with Active Geo-Replication, up to four secondaries can be created (each in a different region). All the secondaries are readable and can be utilized for read-only workloads (such as running reports). If there is a problem with the primary server, a manual failover can be initiated to recover the secondary database and make it available for the application workload. Figure 9-18 represents a scenario with two Active Geo-Replication secondaries.

Database failover can be performed using Azure Portal or PowerShell. After the failover, the new primary database goes into the “Online” state, while the new secondary goes into the “Readable” state. As can be seen in Figure 9-19, the East US database is now the primary database, while the Southeast Asia database has become a readable secondary database.

Figure 9-19. Active Geo-Replication in Action

Listing 9-4 shows a sample PowerShell script to enable Active Geo-Replication.

Listing 9-4. Enabling Active Geo-Replication

$resourceGroupName = "Default-SQL-SoutheastAsia"
$DbServerName = "primarysvr"
$SecondaryServerName = "secondsvr"

$DBName = Get-AzureRmSqlDatabase -ServerName $DbServerName -ResourceGroupName $resourceGroupName | Where-Object {($_.Edition -eq "Premium") -and ($_.DatabaseName -ne "master")}
$replicationLink = New-AzureRmSqlDatabaseSecondary -DatabaseName $DBName[0].DatabaseName -ServerName $DbServerName -ResourceGroupName $resourceGroupName -PartnerResourceGroupName $resourceGroupName -PartnerServerName $SecondaryServerName -AllowConnections All
if($replicationLink -ne $null)
{
    Write-Host "Geo Replication Setup successfully!!"
}

The “Allow Connections” Parameter t                                                          o the New-AzureRmSqlDatabaseSecondary cmdlet is of paramount importance. If the “All” option is not specified, Azure will end up creating a non-readable secondary copy of the database.                                                                                                               

Row-Level Security

Row-Level Security (RLS) provides a way to control access to individual rows in a table. This access control is implemented using security predicates (or security functions) created in the databases. Since the access control logic (the security predicates) is available in the database, it provides a very reliable and robust security mechanism. Moreover, since the logic is implemented within the database, the access will be controlled irrespective of which application or connection is requesting data.

Listing 9-5 shows a script for implementing RLS.

Listing 9-5. T-SQL Sample Script for Implementing RLS

CREATE USER GeneralManager WITHOUT LOGIN;
CREATE USER Manager1 WITHOUT LOGIN;
CREATE USER Manager2 WITHOUT LOGIN;

CREATE TABLE EmployeePerformanceData
    (
    EmployeeID int,
        EmployeeName varchar(200),
    ManagerName sysname,
    EmployeeRating int,
    EmployeeIncrementPercent float
    );

INSERT into EmployeePerformanceData values
(10, 'Employee10', 'Manager2', 1,10.00),
(11, 'Employee11', 'Manager2', 3, 6.53),
(12, 'Employee12', 'Manager1', 2, 8.71),
(13, 'Employee13', 'Manager2', 3, 6.25),
(14, 'Employee14', 'Manager1', 3, 5.87),
(15, 'Employee15', 'Manager2', 5, 0.00);

SELECT * FROM EmployeePerformanceData;

GRANT SELECT ON EmployeePerformanceData TO GeneralManager;
GRANT SELECT ON EmployeePerformanceData TO Manager1;
GRANT SELECT ON EmployeePerformanceData TO Manager2;

-- If any of the users select data from the table at this point, they would see all 6 records
EXECUTE AS USER = 'GeneralManager';
SELECT * FROM EmployeePerformanceData;
REVERT;

EXECUTE AS USER = 'Manager1';
SELECT * FROM EmployeePerformanceData;
REVERT;

EXECUTE AS USER = 'Manager2';
SELECT * FROM EmployeePerformanceData;
REVERT;

--- Implement RLS using Security Predicates and Filters
/*
In this case we are creating a security predicate such that the managers can only their own Employee Data and the GM can see all the employee information.
*/
CREATE SCHEMA Security;
GO

CREATE FUNCTION Security.fn_securitypredicate(@ManagerName AS sysname)
    RETURNS TABLE
WITH SCHEMABINDING
                      AS                                                                                                                                  
    RETURN SELECT 1 AS fn_securitypredicate_result
WHERE @ManagerName = USER_NAME() OR USER_NAME() = 'GeneralManager';

-- Tie the Security Predicate with the User Table
CREATE SECURITY POLICY SalesFilter
ADD FILTER PREDICATE Security.fn_securitypredicate(ManagerName)
ON dbo.EmployeePerformanceData
WITH (STATE = ON);

-- Now if we execute the Queries, each manager would only see their own employee information.

EXECUTE AS USER = 'GeneralManager';
SELECT * FROM EmployeePerformanceData;
REVERT;

EXECUTE AS USER = 'Manager1';
SELECT * FROM EmployeePerformanceData;
REVERT;

EXECUTE AS USER = 'Manager2';
SELECT * FROM EmployeePerformanceData;
REVERT;

-- Disable the security Policy
ALTER SECURITY POLICY SalesFilter
WITH (STATE = OFF);

Dynamic Data Masking

Data masking prevents exposure or unauthorized access to sensitive data by masking the database before it’s presented to the users. Data masking is configured by defining security policies within the table/object definition and using the mask/unmask permissions to control whether a user sees masked or unmasked data. Database owners and admins always see unmasked data.

A sample script to test data masking is included in Listing 9-6.

Listing 9-6. T-SQL Sample Script for Implementing Data Masking

CREATE TABLE Employee
(
EmployeeID int IDENTITY PRIMARY KEY,
FirstName varchar(100) MASKED WITH (FUNCTION = 'partial(1,"XXXXXXX",0)') NULL,
LastName varchar(100) NOT NULL,
Phone# varchar(12) MASKED WITH (FUNCTION = 'default()') NULL,
Email varchar(100) MASKED WITH (FUNCTION = 'email()') NULL,
Salary float Masked with (Function='random(1,7)') Null
);

INSERT Employee (FirstName, LastName, Phone#, Email, Salary) VALUES
('Roberto', 'Tamburello', '555.123.4567', '[email protected]',100000.00),
('Janice', 'Galvin', '555.123.4568', '[email protected]',200000.00),
('Zheng', 'Mu', '555.123.4569', '[email protected]',100000.00),
('Bill', 'Anderson', '555.123.4570', '[email protected]',150000.00),
('Graham', 'Scott', '555.123.4571', '[email protected]',120000.00);

SELECT * FROM Employee;

CREATE USER AppUser WITHOUT LOGIN;
GRANT SELECT ON Employee TO AppUser;

EXECUTE AS USER = 'AppUser';
SELECT * FROM Employee;
REVERT;

The user AppUser will see the following output (see Figure 9-23) with all sensitive information masked. The DBO, on the other hand, will see all the data as is.

Figure 9-23. Data masking

Connection Encryption

Azure SQL Database allows users to use an encrypted SSL connection to the database. To ensure that the connection to SQL Database is encrypted, application developers need to use the "Encrypt = True" connection string parameter.

Transparent Data Encryption

Transparent Data Encryption (TDE ) is not a new feature and has been around since SQL Server 2008. It provides a way to encrypt the data at rest. Azure SQL databases use the same technology to provide a way to encrypt all data at rest. Transparent Data Encryption can be enabled by using either the Azure Portal (or PowerShell) or the following T-SQL code.

Alter Database [MyDatabase1] set Encryption On

The PowerShell cmdlet Get-AzureRMSqlDatabaseTransparentDataEncryption can also be used to set TDE on the SQL database.

Cell-Level (or Column Data) Encryption

Column data encryption is not a new feature and users familiar with SQL Server know that this can be achieved by using a combination of symmetric keys and the EncryptByKey (or DecryptByKey) functions available with SQL. The key is to use the EncryptByKey function along with the key during data insert or update and the DecryptByKey function for SELECT operations.

The T-SQL script in Listing 9-7 creates a table to store encrypted data.

Listing 9-7. Sample T-SQL Script for Storing Encrypted Data

IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE symmetric_key_id = 101)
    CREATE MASTER KEY ENCRYPTION BY
    PASSWORD = 'ThisisaveryveryStr0ngPAss@w0rd1'
GO

CREATE CERTIFICATE EncryptCert
   WITH SUBJECT = 'Some Random Subject';
GO

CREATE SYMMETRIC KEY SymmetricKey
    WITH ALGORITHM = AES_256
    ENCRYPTION BY CERTIFICATE EncryptCert;
GO

-- Create a column in which to store the encrypted data.
Create table CustomerInfo
(
CustomerID int Identity Primary Key,
CustomerName varchar(200),
CustomerPhone varbinary(100),
CustomerEmail varbinary(200),
CustomerCreditCard varbinary(200)
)


-- Open the symmetric key with which to encrypt the data.
Begin Tran
OPEN SYMMETRIC KEY SymmetricKey
   DECRYPTION BY CERTIFICATE EncryptCert;
insert into CustomerInfo (CustomerName,CustomerPhone,CustomerEmail,CustomerCreditCard) values
(
'Mike Anderson',
EncryptByKey(Key_GUID('SymmetricKey'),'555-123-1234'),
EncryptByKey(Key_GUID('SymmetricKey'),'[email protected]'),
EncryptByKey(Key_GUID('SymmetricKey'),'1234567891011123')
)
Commit

-- Decrypt the Data
Begin Tran
OPEN SYMMETRIC KEY SymmetricKey
   DECRYPTION BY CERTIFICATE EncryptCert;

Select CustomerId,CustomerName,
convert(varchar,DecryptbyKey(CustomerPhone)) as Phone,
convert(varchar,DecryptbyKey(CustomerEmail)) as Email,
convert(varchar,DecryptbyKey(CustomerCreditCard)) as CreditCard
From CustomerInfo
Commit

Always Encrypted

Always Encrypted is a new feature available with Azure SQL Database and SQL Server 2016, and it allows clients to manage encryption of sensitive data within the application, never having to expose the information to the database layer. Since the encryption logic is not exposed to the database layers, database admins and server admins do not have access or control over the actual sensitive information.

Always Encrypted supports both Deterministic Encryption and Random Encryption. Deterministic Encryptionwill always generate the same encrypted output for the same plan text, while Random Encryptiongenerates a different value every time. Deterministic Encryption, while useful in scenarios where the encrypted data needs to be searched or joined, can be more susceptible to attacks.

AlwaysOn uses two different keys—the Column Master key and the Column Encryption key, which is encrypted using the Column Master key. Listing 9-8 implements Always Encrypted on an Azure SQL database.

Listing 9-8. Sample T-SQL Script for Implementing Always Encrypted

USE [MyDatabase1]
GO

CREATE COLUMN MASTER KEY [CMKey]
WITH
(
        KEY_STORE_PROVIDER_NAME = N'AZURE_KEY_VAULT',
        KEY_PATH = N'https://<KeyVaultName>.vault.azure.net/keys/AlwaysEncryptedkey'
)
GO

CREATE COLUMN ENCRYPTION KEY [CEKey]
WITH VALUES
(
        COLUMN_MASTER_KEY = [CMKey],
        ALGORITHM = 'RSA_OAEP',
        ENCRYPTED_VALUE = 0x016E000001630075007200720065006E00740075007300650072002F006D0079002F0034006600330065003700660064003700360031003100330036003400340061003600310063003100330064006600390037003000620032003300620037006100320062006300660039006600380066000DEF701B5FAB3F23266DADCAAE7B448122BA75BF1841DEF7143A45C16D37AA4AC57799D50596BA92C0406CC30A3D755D6F5D260DCCA42BB9926136985A7CCF4537B85330DA7C1B12047048A51B04A352F6C3E71BEFEAE777019506D11AC71AF8A7AEC4DE7F5B98ACF6EF7D56B0706E0D521533514335E500E476C6B1777212CE043BDD09B20BB97B5C731CB4D58BF8DDA38A7DF08EECE797DCC15A9E25B064003DE869F6D87B75A3F6625A016292C3B8D8F8D3876DE62DDEE57F7BC2C901E3A2097B8E050862BEA0E33EF434D2DED6D5F2E54745D6E5C616932C5F2144B623C48B7643EDECE4CA545C31AB23DD2DFDF8067D25C05EF1786CCBC110E005D1567B53D6E34ACCC02052F6E9AE7365DE30856EF9DB5EC4315770D255FA76A9865204E8FBE5419AB5836480DE8345141073EB113E012CBF7132DCC22A3A32B6E44B961DDE2B0E7F24733062412CEF9C1A0DC96976A97D48EE5DCE4F5AE1213E680A31ADDFD9344A004ED59C6168CB7D5C8E42A22676A7D64F59A4C1687C61B5F60349699A45D11B8EE7DC8DBB61A156AE70449483D93073497B23597A5F340A98FB7BD37D9DC926360E32F927BB672F6BE1FFC5C01760827AF24B603E184479905BA5DFA9C23E523182F7C5C8ABC53E5D6E6CB3806C5707EDBB7CAC3DE50DA4A2FC38D27EE65F2638FFF37483ABC1050EEAD835919B384BB9136C0F24A6BD9489910
)
GO

CREATE TABLE dbo.EncryptedTable
(                                                                                                              
  ID INT IDENTITY(1,1) PRIMARY KEY,

  LastName NVARCHAR(32) COLLATE Latin1_General_BIN2
    ENCRYPTED WITH
    (
       ENCRYPTION_TYPE = DETERMINISTIC,
       ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
       COLUMN_ENCRYPTION_KEY = [CEKey]
    ) NOT NULL,

  Salary INT
    ENCRYPTED WITH
    (
       ENCRYPTION_TYPE = RANDOMIZED,
       ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256',
       COLUMN_ENCRYPTION_KEY = [CEKey]
    ) NOT NULL
);
GO

Always Encrypted ensures that data is never in plain text, be it at rest in the database storage, or in transit from the client to database or from the database to the client.

Apart from having a plethora of features to ensure safety of data, Azure SQL Database is also complaint with various industry standards.