Brainscape's Knowledge GenomeTM


Top Flashcards (Certified)
All Flashcards

DP-300 > Module 3 - Implement a Secure Environment > Flashcards

Module 3 - Implement a Secure Environment Flashcards

(106 cards)

Start Studying
1
Q

What does AD and Azure AD Provide

A

Both provide Authentication services and ID Management

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

What does AD use to provide Authentication and what is used for Querying AD

A

AD uses Kerberos to provide Authentication using tickets and queries via LDAP (Lightweight Directory Access Protocol)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

What Protocols does AZ AD use

A

HTTPS protocols like, SAML and OpenID Connect for Authentication along with OAUTH for Authorizaion

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

How would you go about providing AZ AD services to an on-prem Windows Server

A

You would need to configure AZ AD Connect to connect AD IDs with AZ AD

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

What are the modes of AuthN in SQL and which is considered most secure and why

A

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Describe SQL Authentication

A

SQL logon stored in master DB (or user DB if using contained users)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

Describe Windows Authentication in the context of SQL Server

A

Connect to SQL using AD Credentials

Windows also allows for easier management of user turnover

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

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

A

True

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

How can you connect to on prem devices using the same AZ AD Credentials

A

AZ AD can be configured to sync with on-prem AD allowsing access to on-prem and AZ resources

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

True or False AZ AD cannot provide MFA services

A

False

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

What are the headlines around the Azure AD Admin Config within the portal for a DB resource

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

What is a Security Principal

A

An entitiy that can request access which you can (usually) grant access to.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

Do security principals exist at the Server or DB level + are security principals individuals or collections

A

Security principals exist at either the server level or the database level and can be either individuals or collections

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

What are the 3 scopes for securables

A

Server,
Database,
Schema

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

What is a Schema

A

A schema is a collection of objects within a DB allowing objects to be grouped into seperate name spaces

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

What is a securable

A

A securable is the resource (i.e. a table in a database)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

When no Schema is specified in a query which order will schemas be looked in

A

1st = users default schema
2nd = DBO
If not in either of those an error will be returned

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

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

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.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

Which DB are (SQL and Windows) Logins stored in

A

Master

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

Where are contained users stored and What Authentication can they use?

A

Stored in the UserDB

AuthN with SQL Auth, Win Auth, AZ AD Auth

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

What is the pre-requisite to using contained users and what is the default state of this for the DB Variants

A

Database must be configured for “Partial Containment”
Default in AZ,
Optional Setting in OnPrem/IaaS

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

Why is it best practice in Azure SQL DB to use Contained users

A

These users only have access to the DB set up in

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

What are some of the headlines on Database and Server Roles

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

Describe Application Roles

A

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

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
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
26
What does the role db_backupoperator do
The db_backupoperator role allows users to backup the Database (obviously not applicable with Azure SQL DB)
27
What does the Role db_datareader do
The db_datareader rol;e allows read from every table/view in DB
28
What does the role db_datawriter do
The db_datawriter role allows insert/update/delete to all tables and views in the DB
29
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
30
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
31
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.
32
What does the role db_owner do
Allows user to perform any action in database and cannot be denied
33
What does the role db_security admin do
Grants access to other users - no access to data itself but can grant access to tables
34
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.
35
What should you do if you have more granular security requirements than the fixed database roles provide
Use Custom Database roles
36
What 2 additional database roles does Azure SQL DB have and where are they stored
DBManager Login Manager Both stored in Master DB
37
What does the Azure SQL DB role DBManager allow?
Allows members to create DBs (similar to the dbcreator fixed server role)
38
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)
39
What does the fixed server role SysAdmin allow
Sysadmin allows member to perform any action on server
40
What does the fixed server role ServerAdmin allow
ServerAdmin allows member to change server wide config and shutdown the server
41
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
42
What does the fixed server role ProcessAdmin allow
ProccessAdmin allows member to kill processes in SQL Server
43
What does the fixed server role SetupAdmin allow
SetupAdmin allows Add and Remove of linked servers via T-SQL
44
What does the fixed server role BulkAdmin allow
BulkAdmin allows member to run BULK INSERT T-SQL
45
What does the fixed server role DiskAdmin allow
DiskAdmin allows management of Backup devices in SQl
46
What does the fixed server role DBCreator allow
DBCreator allows creation, restore, alter and drop of any DB
47
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.
48
What are the 4 basic permissions within a DBMS
SELECT, UPDATE, INSERT, DELETE
49
Which takes precidence, GRANT or DENY?
DENY
50
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
51
True or False - You cannot restrict columns available to a security principal
False - you can restrict columns to a security principal
52
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
53
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)
54
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
55
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
56
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
57
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
58
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
59
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
60
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.
61
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
62
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
63
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
64
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
65
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
66
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
67
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
68
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
69
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 ```
70
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.
71
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
72
Where can further adjustments to the metadata queries for AE be adjusted
SQLCommandColumnEncryptionSetting
73
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) ```
74
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
75
What benefit does Secure Enclave bring
Allows pattern matching and range comparisons on Randomized Encryption columns
76
Define Dynmaic Data Masking
Allows viewing of secure data whilst masking part of the value Presentation layer feature
77
How can DDM be implemented
Via the Azure Portal (for PaaS) or via T-SQL using the ALTER TABLE _ ALTER COLUMN Command
78
What does the mask option default do?
Displays X's for strings, 0 for numbers and 01/01/1900 for dates
79
What does the mask option Credit Card do
Displays xxxx-xxxx-xxxx-1234
80
What does the mask option Social Security Number do?
Displays xxx-xx-1234
81
What does the mask option Random Number do
Displays a random number - should only be used for number columns
82
What does the Custom Text Mask option allow
Custom rules to be set up
83
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.
84
What are firewalls used for
To prevent unautorized access to protected resources
85
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 ```
86
Azure provides built in firewall to limit access, it has 2 sets of firewall rules what are these
Server Firewall rules | Database Firewall rules
87
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
88
What does a server level firewall allow
Access to MasterDB and all user DBS
89
What does a database level firewall allow
Access to a specific DB
90
How can Server level firewalls be configured
Via Azure Portal or T-SQL
91
How can Database level firewalls be configured
Via T-SQL only
92
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
93
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
94
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
95
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.
96
Where is sensitivity meta data for data classification stored as of SQL 2019
In the DMV, sys.sensitivity_classification (previously in extended properties)
97
Data classification can be managed from where
Data classification can be managed from Azure Portal (for PaaS) and via SSMS
98
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
99
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.
100
Describe Advanced Threat Protection
Advanced Threat Protection offers a suite of protections Configured via the Az
101
What does Advanced Threat Protection monitor
Advanced Threat Protection Monitors Connections and Queries being executed
102
Where is Advanced Threat Protection managed from
Advanced Threat protection is managed via the AZure Portal
103
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
104
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
105
Describe SQL Injection
Common Method for data breaches | Attacker appens SQL Code via user entry form in attempt to execute there own SQL
106
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

DP-300 (4 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2025 Bold Learning Solutions. Terms and Conditions