In this workshop you'll cover the basics of securing SQL Server installations and databases, from on-premises systems to Microsoft Azure deployments.
In each module you'll get more references, which you should follow up on to learn more. Also watch for links within the text - click on each one to explore that topic.
(Make sure you check out the Pre-Requisites page before you start. You'll need all of the items loaded there before you can proceed with the workshop.)
This module builds on the previous module where you learned about the basics of SQL Server Security. This module focuses on the the differences between those security aspects and security in Microsoft Azure SQL Database.
You'll cover these topics in this module:
- 01 - Accessing Azure SQL Database
- 02 - Principals
- 03 - Securables
- 04 - Applications
- 05 - Encryption, Certificates, and Keys
- 06 - Auditing
In this Activity you will set the firewall rules to allow connections from your test system to an Azure SQL Database.
Steps
- Determine the IP Address on your test system, and record it.
- Open this resource, and complete the sections from Prerequisites to Setup Database Firewall Rules.
- Connect to that database with SQL Server Management Studio using an encrypted connection.
Using SQL Authentication means that Azure SQL Database stores the password for the Principals. Because passwords are stored in the master database, it is up to the database owner to ensure that a password and account policy is applied to each user.
As described in the last module, Microsoft Active Directory (AD) is a suite of services, and Active Directory Domain Services (AD DS) is the core Active Directory service used to manage users and resources. Microsoft Azure Active Directory (AAD) is a Domain Service run in the cloud, and works differently than the on-premises installation of Active Directory. AAD is a more secure way to access resources, and allows a higher level of security with Multi-Factor Authentication (MFA), as described in this reference.You can also connect your local Active Directory to Microsoft Azure Active Directory, allowing administration of your local domain to access resources in the cloud, in a single-sign-on approach. You can learn how to integrate Microsoft Azure Active Directory into Azure SQL Database using this resource.
Microsoft Azure Purview is a Data Governance Tool that allows you to catalog all of your data assets regardless of where they reside - on premises, in Azure, or even in other Cloud providers. It has a feature for certain Azure SQL services that allows you to also configure Role Based Access Control to database objects. You can learn more about Microsoft Azure Purview here.
For this Activity, you will run the same scripts as in the previous module, with the exception of not creating the Windows local users. For this course, you will focus on using SQL Authentication, and you will be pointed to resources and demonstrations for using Azure Active Directory.
Steps
- Run the following code on your test SQL Azure DB connected to the master database:
CREATE LOGIN [User1]
WITH PASSWORD=N'Tes#20. Use12!'
GO
CREATE LOGIN [User2]
WITH PASSWORD=N'Tes#20. Use22!'
GO
- Run the following commands while connected to the test database:
CREATE USER [User1]
FROM LOGIN [User1]
GO
CREATE USER [User2]
FROM LOGIN [User2]
GO
Similar to SQL Server installations, you can group Principals into Roles in Azure SQL Database. In the case of Azure SQL Database, you do not have direct access to an Instance of SQL Server, but instead rely on a logical construct called a Server. There are several built-in Server Roles, which allow the most common set of permissions. It is a best practice to always put Principals (users) into Roles, and apply permissions at the Role level. This prevents "orphaned" users, and allows easier tracking for the permissions spread.
You can see the included Server Roles and their permissions at this reference.
Similar to SQL Server Installations, Azure SQL Database also has Database-level Roles, and you can add more if you want to create custom permissions. You can see the included Database Roles and their permissions, along with the instructions to create more Database Roles at this reference.
Also similar to SQL Server Installations, Azure SQL Database has Application Roles. Application Roles remove the need for user permissions. Using Application Roles, the user runs a client application, which connects to the database as that user. The application switches contexts to the Application Role, runs the commands on behalf of the user, and returns the result.
You can learn more about Application Roles and how to use them at this reference.
In this Activity you will Create Database Roles, add Users to those Roles, and then list Roles and Members.
Steps
- Run the following code on your test SQL Azure Database:
CREATE ROLE Elevated_permissions;
- Add User1 to the new role
ALTER ROLE [Elevated_permissions] ADD MEMBER [User1]
GO
- Query all users and role memberships:
--Server Principals
--
SELECT Name, type_desc, create_date FROM sys.database_principals
WHERE type = 'S' AND is_fixed_role = 0 AND authentication_type= 1 AND name NOT IN ('dbo')
--
--Database Principals
--
SELECT Name, type_desc, create_date FROM sys.database_principals
WHERE type = 'R' AND is_fixed_role = 0 AND name NOT IN ('public')
--
--Find Role Memberships
--
SELECT DP1.name AS DatabaseRoleName,
isnull (DP2.name, 'No members') AS DatabaseUserName
FROM sys.database_role_members AS DRM
RIGHT OUTER JOIN sys.database_principals AS DP1
ON DRM.role_principal_id = DP1.principal_id
LEFT OUTER JOIN sys.database_principals AS DP2
ON DRM.member_principal_id = DP2.principal_id
WHERE DP1.type = 'R'
ORDER BY DP1.name;
Just as in a SQL Server Instance, Securables in Azure SQL Database are the objects the database contains. A special container called a Schema allows for a gouping of Securables into a single group. Each Schema is owned by one or more Principals, and using a Schema makes for a simplified security management process. You can learn more about Schemas here.
Securables fall into three categories, or scopes, for ease of use, the same as a SQL Server installation:
- Availability Groups
- Endpoints
- Logins
- Server Roles
- Databases
- Application Roles
- Assemblies
- Asymmetric Keys
- Certificates
- Contracts
- Fulltext Catalogs
- Fulltext stoplists
- Message types
- Remote Service Bindings
- Database Roles
- Routes
- Search Property Lists
- Services
- Symmetric Keys
- Users
- Types
- XML schema Collections
- Objects
- Aggregates
- Functions
- Procedures
- Queues
- Synonyms
- Tables
- Views
- External Tables
Each securable includes a set of Permissions relevant to its scope and function. Some of these permissions include Rights, such as the ability for one Principal to allow access to an object the first owns. Permissions are "most permissive" - meaning that if a user has three permissions in one Role they are a member of and two other permissions from another Role they are a member of, they will have a total of five permissions.
The primary commands for object access (Data Control Language, or DCL), just as in a SQL Server installation, are:
- GRANT - Allows a Principal to perform an action on the object, such as SELECT or DELETE.
- REVOKE - Removes the permission on an object for a Principal, but if the Principal is a member of another Role with access, does not remove that access.
- DENY - Removes the permission on an object for a Principal. Overrides all other permissions, including those inhereted from a Role assignment.
In this Activity you will work with Permissions on various database objects. You can alter these scripts after you run them to experiment with how Permissions work.
Steps
- Add the three objects from the previous Module:
CREATE TABLE Patient (
LoginID tinyint
, LastName varchar(255)
, FirstName varchar(255)
, Address varchar(255)
, City varchar(255)
, SSN nvarchar(11)
, CardNumber nvarchar(19)
);
INSERT INTO Patient (loginid, lastname, firstname, address, city, ssn, cardnumber)
VALUES
(1,'Arbiter', 'Agatha', '111 Apple Ave.', 'Atlanta', '111-11-1111', '1111-1111-1111-1111')
, (2, 'Bob', 'Billy', '222 Bayshore Blvd.', 'Boice', '222-22-2222', '2222-2222-2222-2222')
, (3, 'Choice', 'Charley', '333 Castaway Ct.', 'Chesterfield', '333-33-3333', '3333-3333-3333-3333')
, (4, 'Dangerfield', 'David', '4444 Denvue Drive', 'Denver', '444-44-4444', '4444-4444-4444-4444')
, (5, 'Engleton', 'Edbert', '5555 Esquire Rd. E', 'Easton', '555-55-5555', '5555-5555-5555-5555');
GO
CREATE VIEW Patient_Mailing_Address AS
SELECT FirstName, LastName, Address, City
FROM Patient;
GO
CREATE PROCEDURE uspGetCardInformation
@Loginid tinyint
AS
SET NOCOUNT ON;
SELECT Loginid, cardnumber
FROM Patient
WHERE loginId= @loginId
GO
- Connect with User1 or User2 to see what current Permissions allow:
Select LoginId FROM Patient WHERE Loginid = 1;
GO
EXEC uspGetCardInformation @Loginid = 2;
GO
SELECT * FROM Patient_Mailing_Address;
GO
- Switch to an admin user and GRANT CONTROL (all permissions) on all three objects to the Elevated_permissions role:
GRANT CONTROL ON Patient TO Elevated_Permissions;
GRANT CONTROL ON Patient_Mailing_Address TO Elevated_Permissions;
GRANT CONTROL ON uspGetCardInformation TO Elevated_Permissions;
- Run the script again on User1:
Select LoginId FROM Patient WHERE Loginid = 1;
GO
EXEC uspGetCardInformation @Loginid = 2;
GO
SELECT * FROM Patient_Mailing_Address;
GO
- Using your admin connection, Revoke SELECT for User1 on the Patient_Mailing_Address view:
REVOKE SELECT ON Patient_Mailing_Address TO User1;
- Now return to User1's conenction, run the query again:
SELECT * FROM Patient_Mailing_Address;
GO
- As you can see, GRANT supercedes REVOKE. Return to admin and DENY SELECT on the view:
DENY SELECT ON Patient_Mailing_Address TO User1;
- Finally, rerun the same query with User1. You will note that the DENY permission overrides the previous GRANT:
SELECT * FROM Patient_Mailing_Address;
GO
Always use the principle of Least Privilege each time you assign permissions, and cross referencing permissions granted through each role is necessary. The Permissions Poster produced by Microsoft can help illustrate that heirarchy.
This section repeats much of the same information as an installed Instance of SQL Server, so you can review the text below, running any sample scripts that you ommitted in the previous Module.
Apart from the Secure Coding principles your client applications should follow, there are security mechanisms within Azure SQL Database that you can leverage in your code for enhanced protections.
Azure SQL Database requires SSL/TLS at all times. There are, however, various version of TLS, and you want to implement the highest version possible when you connect to an Azure SQL Database. You can find the latest drivers and connection methods for Azure SQL Database at this reference. Each of these connection libraries has differing security impacts, so it is important to review the latest releases and use the most secure methods of access possible. You should follow the best practices for data design for your application, so that you store sensitive data in a separate, protected space. You can then move to to protecting the data within the application. You have various options for creating a secure database program. The first step is to implement a least-privilege approach within your permission structure, as described in the previous sections. By granting permissions only to the higher-level objects (such as Views, Functions and Store Procedures) you are able to protect the underlying base Tables from unecessary access by the end user.Views allow you to show only the columns and rows required for least-privilege, and Functions and Stored Procedures allow you to restrict both columns and rows.
Azure SQL Database provides Row-Level Security so that you can restrict access to objects based on the security context of the user - whether that is based on Role membership or even the execution context of the query.By creating a Function and a Security Policy, you can set up protections for SELECT, UPDATE and DELETE operations.
Activity: Apply Row-Level Security to an Object
Steps
- Navigate to this reference, and follow all the steps you see there for Scenario "A", using your sample Azure SQL Database environment.
Activity: Review Dynamic Data Masking
You can implement these scripts if you would like a hands-on experience in your sample workshop database.
Steps
- Navigate to this reference, and review all the steps you see there, using your sample Azure SQL Database environment.
SQL Server 2022 and Microsoft Azure SQL Database include a new feature called Ledger that stores a one-way hash root digest of the data rows in a table. This Ledger can be kept in a secure location so that the hash of the current rows can be compared to the digest, alerting you to any differences, which indicates that the data has been tampered with.
With the Ledger feature, you are able to create updatable tables, or insert-only tables depending on your application's needs.
Activity: Review Ledger in SQL Server and Azure SQL
Steps
- Navigate to this reference, and review all the steps you see there, using your sample Azure SQL Database environment.
Microsoft Azure SQL Database supports encryption of data in multiple places: in-transit, at-rest, in-database and more.
For data-in-transit, Transport Layer Security (TLS) is enforced for connections to the server over secure protocols.
For data encryption, two mechanisms are available. For data-at-rest, Azure SQL Database automatically implements the SQL Server Transparent Data Encryption (TDE) feature. With no code or database changes, your database is encrypted on storage.
For data encryption, it's important to understand how Azure SQL Database uses Keys to encrypt data.
On creation, an Azure SQL Database "Server" creates a Service Master Key (SMK), which is used to create a Database Master Key (DMK). These Keys are protected by either a Certificate, an Asymmetric Key (such as a password) or a Symmetric Key to lock and unlock the Key. An Extensible Key Management (EKM) module can also be used, holding symmetric or asymmetric keys outside of Azure SQL Database.
You can encrypt data as you insert it using T-SQL functions, such as ENCRYPTBYPASSPHRASE and DECRYPTBYPASSPHRASE calls.
Another method of setting up encryption for your database is using the Always Encrypted feature. Always Encrypted allows applications to encrypt sensitive data wihtout revealing the encryption keys to the Database Engine. This is transparent to the application, so you don't need to write special code to take advantage of this feature.
Activity: Set up Always Encrypted on your Sample Database
Steps
- Navigate to this reference, and follow all the steps you see there, using your sample Azure SQL Database environment.
Microsoft Azure SQL Database has an auditing capability similar to a SQL Server installation on-premises, but since you do not have access to the storage where the service is running, you send the output of the audits to an Azure storage account, a Log Analytics workspace, or to Azure Event Hubs. Each of these targets provides different interfaces and features, but they store the same information.
You can enable a logical Server audit, a Database audit, or both. In general, you should choose one or another, but not both. Enabling a Server audit audits all the databases on that server, and any new ones you create, and sends it to a single target. Enabling a Database audit allows you to send each audit collection to a different target.
You can learn more about Azure SQL Database audits here.
Activity: Set up a Server Audit on your Azure SQL Database account
In this Activity you will implement a Server audit on your sample course database environment.
Steps
- Navigate to this reference, and follow all the steps you see there, using your sample Azure SQL Database environment.
For Further Study
- Official Microsoft Documentation for this section
- Microsoft Azure SQL Database Security Best Practices
- Videos of several of these exercises are here.
Next Steps
Next, Continue to 04 - Monitoring and Incident Response.