Module 3 - Implement a Secure Environment Flashcards
What does AD and Azure AD Provide
Both provide Authentication services and ID Management
What does AD use to provide Authentication and what is used for Querying AD
AD uses Kerberos to provide Authentication using tickets and queries via LDAP (Lightweight Directory Access Protocol)
What Protocols does AZ AD use
HTTPS protocols like, SAML and OpenID Connect for Authentication along with OAUTH for Authorizaion
How would you go about providing AZ AD services to an on-prem Windows Server
You would need to configure AZ AD Connect to connect AD IDs with AZ AD
What are the modes of AuthN in SQL and which is considered most secure and why
Windows Mode and SQL Mode (Windows and Mixed)
Windows is more secure as SQL allows login info to be seen in plain text when passed over the network.
Describe SQL Authentication
SQL logon stored in master DB (or user DB if using contained users)
Describe Windows Authentication in the context of SQL Server
Connect to SQL using AD Credentials
Windows also allows for easier management of user turnover
True or False - Azure SQL Database supports SQL AuthN and AZ AD AuthN giving users the ability to log into SQL with the same credentials as pother resources such as the Azure portal of O365
True
How can you connect to on prem devices using the same AZ AD Credentials
AZ AD can be configured to sync with on-prem AD allowsing access to on-prem and AZ resources
True or False AZ AD cannot provide MFA services
False
What are the headlines around the Azure AD Admin Config within the portal for a DB resource
Allows admin access to all DBs in server
Best practice is to make this a group
Grants special sysadmin type access
Can be set via ARM using Portal, PowerShell or CLI - not at the DB level though
What is a Security Principal
An entitiy that can request access which you can (usually) grant access to.
Do security principals exist at the Server or DB level + are security principals individuals or collections
Security principals exist at either the server level or the database level and can be either individuals or collections
What are the 3 scopes for securables
Server,
Database,
Schema
What is a Schema
A schema is a collection of objects within a DB allowing objects to be grouped into seperate name spaces
What is a securable
A securable is the resource (i.e. a table in a database)
When no Schema is specified in a query which order will schemas be looked in
1st = users default schema
2nd = DBO
If not in either of those an error will be returned
When writing queries best practice dictates to specify a schema.
If no schema is specified when creating a an object it will go where? And what happens if the user cannot create objects at that location
A create statement with no defined schema will go into the users default schema - if the user does not have permission for this access will be denied.
Which DB are (SQL and Windows) Logins stored in
Master
Where are contained users stored and What Authentication can they use?
Stored in the UserDB
AuthN with SQL Auth, Win Auth, AZ AD Auth
What is the pre-requisite to using contained users and what is the default state of this for the DB Variants
Database must be configured for “Partial Containment”
Default in AZ,
Optional Setting in OnPrem/IaaS
Why is it best practice in Azure SQL DB to use Contained users
These users only have access to the DB set up in
What are some of the headlines on Database and Server Roles
Effectively Security groups sharing common permissions
Built in Roles are decided by Microsoft with ability to add custom roles at Server or DB level
Server Roles cannot be granted access to DB objects directly
Server Roles are only available in IaaS, On Prem and MI
Describe Application Roles
A user is not a member of an App role, the app role is activated by using the password.
Once active app role permissions are applied to a user until de-activated
What does the role db_accessadmin do
The db_accessadmin allows creation of other users in DB, no access to Schema, Tables of Data within database
What does the role db_backupoperator do
The db_backupoperator role allows users to backup the Database (obviously not applicable with Azure SQL DB)
What does the Role db_datareader do
The db_datareader rol;e allows read from every table/view in DB
What does the role db_datawriter do
The db_datawriter role allows insert/update/delete to all tables and views in the DB
What does the role db_ddladmin do
The db_ddladmin role allows the creation and modification of objects in the DB with no read/write access to the data itself
What does the role db_denydatareader do
Prevents reading of any Db data when a user has had access through other roles or directly granted
What does the role db_denydatawriter do
Prevents writing of any data to DB when a user has had access through other roles or directly granted.
What does the role db_owner do
Allows user to perform any action in database and cannot be denied
What does the role db_security admin do
Grants access to other users - no access to data itself but can grant access to tables
What does the public database role do?
Default role without permissions unless you assign some to it - however all users including guest (if enabled) have access to this role.
What should you do if you have more granular security requirements than the fixed database roles provide
Use Custom Database roles
What 2 additional database roles does Azure SQL DB have and where are they stored
DBManager
Login Manager
Both stored in Master DB
What does the Azure SQL DB role DBManager allow?
Allows members to create DBs (similar to the dbcreator fixed server role)
What does the Azure SQL DB role LoginManager allow?
Allows creation of additional logins at th eserver level (Similar to the security admin fixed server role)
What does the fixed server role SysAdmin allow
Sysadmin allows member to perform any action on server
What does the fixed server role ServerAdmin allow
ServerAdmin allows member to change server wide config and shutdown the server
What does the fixed server role SecurityAdmin allow
SecurityAdmin allows the management of logins and well as grand and revoke of Server and DB level permissions
What does the fixed server role ProcessAdmin allow
ProccessAdmin allows member to kill processes in SQL Server
What does the fixed server role SetupAdmin allow
SetupAdmin allows Add and Remove of linked servers via T-SQL
What does the fixed server role BulkAdmin allow
BulkAdmin allows member to run BULK INSERT T-SQL
What does the fixed server role DiskAdmin allow
DiskAdmin allows management of Backup devices in SQl
What does the fixed server role DBCreator allow
DBCreator allows creation, restore, alter and drop of any DB
What does the fixed server role PUBLIC do
Public is a fixed server role that everyone belongs to - this can be granted, denied or revoked permissions.
What are the 4 basic permissions within a DBMS
SELECT, UPDATE, INSERT, DELETE
Which takes precidence, GRANT or DENY?
DENY
What is EXECUTE AS USER in SQL Server and Where is it availbale
Allwos for user context to be change
Availble in OnPrem, IaaS and MI
True or False - You cannot restrict columns available to a security principal
False - you can restrict columns to a security principal
The 4 main permissions on tables and views are, Select, Insert, Update, Delete - what additional permissions are there for Azure SQL DB and Managed Instance
CONTROL and REFERENCES
What do the Control and References permissions do with regards to table permissions in Azure SQL DB and MI
Control - Grants rights to all objects, can perform any action on an object
References - Allows the viewing of FK on an object (also available in MySQL and PostgreSQL in Azure)
What are the 3 permissions of Stored Procs and Functions and what do they do
Alter - Change the defnition of an object
Control - Grants all rights to an object
Execute - Allows execution of an object
What does View Change Tracking and View Definition control
View Change Tracking - Allows viewing of the change tracking settings
View Definition - Allows viewing of the definitions
What does ownership chaining allow and what are the 2 caveats
Ownership chaining allows users to inherit permissions from other objects (such as a user that has no access to a certain table but can get the data via executing a Stored proc that can get the data from the table)
Caveat 1 - Owners of all the objects in question need to be the same
Caveat 2 - Does not work with Dynamic SQL
Describe TDE
TDE = Transparent Data Encryption
Encrypts all the data in a database, with data being encrypted as data written to the page and decrypted as being read into memory.
Prevents underlying data and log files being attached elsewhere
No decryption is done when taking backups so backups end up encrypted
New Azure SQL DBs and MI have this on by default (can be turned off via the portal)
Azure SQL DB allows use of MS key and BYOK
If TDE Certificate is lost you will be unable to restore you DB elsewhere
What are the Steps to enable TDE via T-SQL
Create Master Key in MasterDB
Create Cert in MasterDB used for encryption
Create Database Key in target DB using the Cert
Alter Database Statement to enable TDE
What does SQL do once you enable TDE
Starts to encrypt all the pages which can take some time, overhead is kept to a minimum as this is deemed a low priority task by SQL
TDE is not available on Azure MySQL or PostgreSQL - what can you do instead?
Instead of TDE you can encrypt the disks you databases are stored on.
Azure Key Vault configuration is easy for Azure Resources and can be done for VMs at creation or a later date, what information is required when configuring Azure Key Vault
Key vault URL
Principal Name
Principal Secret
Credential Name
What are the steps to configure Azure Key Vault within SQL Server?
Create SQL Login at instance
Create and Map credential to login
—Use the name of the key vault for the identity of the credential
—Use the ApplicationID in the key vault for the credential secret
Then…
Create an Asymmetric Key in Key Vault
Create an Asymmetric Key in SQL DB which maps to key vault using “CREARE ASYMMETRIC KEY … FROM PROVIDER” Syntax
The Asymmetric key can then be used for Backups, TDE Always Encrypted etc
What does Azure Disk Encryption do
Encrypts disks of Azure VMs for extra level of protection as an ADDITION to any SQL Level encryption you implement
What is Azure Key Vault and How would you typically access it
Azure Key Vault is a tool for storing secrets and is typically accessed programatically
What’s special about the Azure Key Vault RBAC policies
Azure Key Vault RBAC policies are seperate from the subscription so a subscription admin doesn’t nessecairly have access to Azure Key Vault secrets
Describe Always Encrypted
Encrypts data within columns
Allows encryption of data at the Client App based on the column settings within SQL Server
Based on Master + Column Encryption Keys (columns can be encyrpted with different keys for additional protection
Describe the process of using Always Encrypted
In AE Wizard Choose Columns to encrypt, the encryption type to use and configure the column encryption key
Generate or Select the master encryption key
Where must an AE Master Encryption Key be stored
In a keystore such as Azure Key Vault or Windows Certificate Store or a Hardware Security Module
What is the difference between deterministic and randomized encryption
Deterministic = Source data always encrypts to same encrypted value Randomized = Identical source data will have different encrypted values
What are the requirements to decrypt AE encrypted data?
App needs to connect via a driver that supports AE
App needs access to the key store
App can then retrieve data, any data written back is encrypted at the client driver level.
What does the applications connection string need to include to use AE
App connection string needs to include “Column Encryption Setting = Enabled” to be able to use AE
Where can further adjustments to the metadata queries for AE be adjusted
SQLCommandColumnEncryptionSetting
For the Metadata queries in AE define, Disabled, Enabled and ResultSet
Disabled = No meta data queries Enabled = All meta data queries ResultSet = Only queries values in the SELECT statement (i.e. not the where clause etc)
What is a Secure Enclave
Secure portion of memory in SQL Server for processing encrypted data
A black box that you cannot view any data or code within - even with a debugger
What benefit does Secure Enclave bring
Allows pattern matching and range comparisons on Randomized Encryption columns
Define Dynmaic Data Masking
Allows viewing of secure data whilst masking part of the value
Presentation layer feature
How can DDM be implemented
Via the Azure Portal (for PaaS) or via T-SQL using the ALTER TABLE _ ALTER COLUMN Command
What does the mask option default do?
Displays X’s for strings, 0 for numbers and 01/01/1900 for dates
What does the mask option Credit Card do
Displays xxxx-xxxx-xxxx-1234
What does the mask option Social Security Number do?
Displays xxx-xx-1234
What does the mask option Random Number do
Displays a random number - should only be used for number columns
What does the Custom Text Mask option allow
Custom rules to be set up
Give a good use case for Dynamic Data Masking
When exporting a prod DB to dev, do so with an account that does not have (UNMASK PERMISSIONS) to persist the maskings in the dev environment.
What are firewalls used for
To prevent unautorized access to protected resources
Fill in the Blanks
Each Azure SQL DB maps to a [blank 1] [blank 2] hosted by Microsoft.
Each Azure Region will have one of more [blank 3] [blank 4] where you can reach you [blank 5] [blank 6], which will take you to your DB
1. Public 2 IP 3 Public 4 IP 5 Database 6 Gateway
Azure provides built in firewall to limit access, it has 2 sets of firewall rules what are these
Server Firewall rules
Database Firewall rules
What do the Azure Firewall rules use to govern Authentication
Azure Firewalls use IP Rules instead of SQL Logins, this allows all users at same location access
What does a server level firewall allow
Access to MasterDB and all user DBS
What does a database level firewall allow
Access to a specific DB
How can Server level firewalls be configured
Via Azure Portal or T-SQL
How can Database level firewalls be configured
Via T-SQL only
Describe the steps Azure SQL DB takes (in terms of firewalls) when a user is making a connection?
Upon Connection Azure SQL DB looks for a server firewall rule in master.
If a DB is specified in connection string will look to the database level firewall
If either exist the connection complete.
If neither exist and User is connection via SSMS or Azure Data Studio they will be prompted to create a firewall rule
Describe Virtual Network Endpoints
Allow traffic from a specific Azure vNet
Rules apply at server level not just DB level
Service endpoint applies to only one region (the underlying endpoints region)
Added concern is that Azure DB must have outbound access to the Public IP address for Azure SQL DB - can be configured with tags
Describe Private Link
Allows you to connect to Azure SQL DB (and other PaaS offerrings) via a private endpoint over the Azure Backbone and not via the Public Internet
Provide Private IP Address on your vNet
Allows for Express Route connections through that circuit
Also allows for Cross-Region private connectivity and protection against data leakage by only allows connections to specific resources
Confidential data should be classified as such, data classification allows apps and users to know the sensitivity of data - at what level does this happen
Data classification is done on a column by column basis.
Where is sensitivity meta data for data classification stored as of SQL 2019
In the DMV, sys.sensitivity_classification (previously in extended properties)
Data classification can be managed from where
Data classification can be managed from Azure Portal (for PaaS) and via SSMS
You should verify classification suggestions by the Classification Engine…why?
You should verify classification suggestions by the Classification Engine as it scans based on Column name
You can add Sensitivity classifications to columns via T-SQL, why is this practice good for users and DBA
It allows users to make better desicions on how to handle data
It gives DBAs a good idea on which columns to encrypt.
Describe Advanced Threat Protection
Advanced Threat Protection offers a suite of protections
Configured via the Az
What does Advanced Threat Protection monitor
Advanced Threat Protection Monitors Connections and Queries being executed
Where is Advanced Threat Protection managed from
Advanced Threat protection is managed via the AZure Portal
What should you do to get maximum benefit from Advanced Threat Protection
To get maximum benefit from Advanced Threat protection you should enable Auditing as this allows for deeper investigations
What alerts does Advanced Threat Protection have
Vulnerability to SQL injection (checks for things like a SP not sanitizing user input.
Potential SQL Injection (when a user is actively trying to carry out a SQL injection attack)
Access from Unusual location
Access from Unusual Azure Data Centre
Access from Unfamiliar principal (when user or app uses a DB they havent before)
Access from potentially harmful application
BruteForce of SQL Credentials
Describe SQL Injection
Common Method for data breaches
Attacker appens SQL Code via user entry form in attempt to execute there own SQL
What is the Best way to combat SQL injection
Best way to combat SQL injection is to check input is the correct data type is of correct length etc and contains no Binary data
Whilst Fixing Application should be top priority - Advanced Threat Protection can help protect you as a backup
