Mcts 70-432 Part 2

Front 1

What are the two basic parts of a TCP endpoint?

Back 1

a transport and a payload

Front 2

Endpoint can be of two different transports, what are they?

Back 2

TCP and HTTP

Front 3

What values can an Endpoint payload have?

Back 3

SOAP, TSQL, SERVICE_BROKER and DATABASE_MIRRORING

Front 4

What are the valid combinations of Endpoint Transport and Payload?

Back 4

TCP TSQL
TCP SERVICE_BROKER
TCP DATABASE_MIRRORING
HTTP SOAP

Front 5

What do TCP endpoint do?

Back 5

They reject requests that are not properly formatted based on the endpoint definition.

Front 6

What are the two layers of endpoint access?

Back 6

The first layer is the endpoint state; the second layer is permission to connect to the endpoint (an application must have a login created in SQL Server that has the CONNECT permission granted on the endpoint before the connection is allowed through the endpoint).

Front 7

What are the three states than an endpoint can have?

Back 7

Started - endpoint is actively listening for connections and will reply
Stopped - endpoint is actively listening but returns a connection error to an application
Disabled - endpoint does not listen and does not respond to any connection attempted

Front 8

What are the two arguments that are universal for all TCP endpoints? Which is required and which is optional?

Back 8

LISTENER_PORT -Required
LISTENER_IP -Optional

Front 9

What is the default TCP port for database mirroring?

Back 9

5022

Front 10

What is the default TCP port for TSQL?

Back 10

1433

Front 11

What is the default setting for LISTENER_IP?

Back 11

ALL - listens for connections sent to any valid IP address configured on the machine

Front 12

What additional options do both Database Mirroring and Service Broker endpoints offer that TSQ Endpoints do not?

Back 12

They offer options to specify the authentication method and the encryption settings.

Front 13

What authentication method can be used for Database Mirroring and Service Broker endpoints?

Back 13

Windows-based authentication or certificates.

Front 14

How do you specify windows-based authentication in Database mirroring and service broker endpoints?

Back 14

By selecting the NTLM, KERBEROS or NEGOTIATE option. The NEGOTIATE option causes the instances to select the authentication method dynamically.

Front 15

When should you use certificate-based authentication for endpoints?

Back 15

When instances span non-trusted domains.

Front 16

What is the default encryption algorithm used by SQL when encrypting communication between endpoints? What stronger algorithm can you specify?

Back 16

RC4
AES - more overhead, will affect performance

Front 17

How many TCP endpoints with a payload of DATABASE_MIRRORING can you specify for each instance?

Back 17

Only one.

Front 18

What third argument do Database mirroring endpoints offer?

Back 18

You can specify that an endpoint is a PARTNER, WITNESS or ALL.
PARTNER - can participate only as the principal or as the mirror.
WITNESS - can participate only as a witness
ALL - can function in either role

Front 19

What is the TSQL to create a database mirroring endpoint?

Back 19

CREATE ENDPOINT [Mirroring]
AS TCP (LISTENER_PORT = 5022)
FOR DATA_MIRRORING (ROLE = PARTNER, ENCRYPTION = REQUIRED);
ALTER ENDPOINT [Mirroring] STATE = STARTED;

Front 20

What additional argument is available for Service-Broker endpoints?

Back 20

The MESSAGE_FORWARDING option, which enables messages destined for a different broker instance to be forwarded to a specified forwarding address. The options are ENABLED and DISABLED.

Front 21

If MESSAGE_FORWARDING in a service broker endpoint is set to ENABLED, what additional option can you apply?

Back 21

MESSAGE_FORWARDING_SIZE, which specifies the max amount of storage to allocate for forwarded messages.

Front 22

What does DMV stand for?

Back 22

Dynamic management view.

Front 23

If you want to be able to connect to a SQL Server instance remotely, what needs to be enabled?

Back 23

The Transmission Control Protocol/Internet Protocol (TCP/IP) network provider.

Front 24

What are the two SQL features that pose the greatest security risk?

Back 24

OPENROWSET/OPENDATARESOURCE and OLE Automation procedures.

Front 25

How do you enable and disable SQL Server features?

Back 25

By using sp_configure.

Front 26

What features should you have disabled unless you need the specific functionality?

Front 27

What is a principal?

Back 27

The means by which you authenticate and are identified within an instance or database.

Front 28

What are the 5 types of logins in SQL Server 2008?

Front 29

How does SQL Server map a windows user or group to a Standard SQL Server login?

Back 29

SQL stores the name of the login or group along with the corresponding security identifier (SID); when a user logs in to the instance using windows credentials, SQL makes a call to the windows security application programming interface to validate the account, retrieve the SID and then compare the SID to those stored in the master database.

Front 30

What do SQL Server logins mapped to certificates or asymmetric keys lack?

Back 30

A means to authenticate. Logins mapped to certificates and asymmetric keys are used internally as a security container.

Front 31

What is the syntax to create a login?

Back 31

CREATE LOGIN loginName { WITH <option_list1> | FROM <sources> }

<option_list1> ::=
PASSWORD = { 'password' | hashed_password HASHED } [ MUST_CHANGE ]
[ , <option_list2> [ ,... ] ]

<option_list2> ::=
SID = sid
| DEFAULT_DATABASE = database
| DEFAULT_LANGUAGE = language
| CHECK_EXPIRATION = { ON | OFF}
| CHECK_POLICY = { ON | OFF}
| CREDENTIAL = credential_name

<sources> ::=
WINDOWS [ WITH <windows_options> [ ,... ] ]
| CERTIFICATE certname
| ASYMMETRIC KEY asym_key_name

<windows_options> ::=
DEFAULT_DATABASE = database
| DEFAULT_LANGUAGE = language

Front 32

What is the CHECK_POLICY option in creating logins?

Back 32

It is the default option; when enabled, SQL server enforces the windows password policy settings when you create a SQL Server login.

Front 33

What is the CHECK_EXPIRATION option in creating logins?

Back 33

It is used to prevent brute force attacks against a login. When enabled, each time the login is used to authenticate to an instance, SQL Server checks whether the password has expired and change the password if necessary.

Front 34

Why is the sa account a target for brute force attacks.

Back 34

Unlike other accounts, the sa account cannot be locked out due to failed login attempts. You can rename the SA account as an alternative.

Front 35

How can you prevent access by user/users without revoking permissions?

Back 35

You can disable the account:
ALTER LOGIN <loginname> DISABLE

Front 36

What are the instance level roles that ship with SQL; can you change permissions on them?

Back 36

-- bulkadmin - Administer BCP and Bulk Insert operations
-- dbcreator - Create databases
-- diskadmin - Manage disk resources
-- processadmin - Manage connections and start or pause an instance
-- securityadmin - Create, alter, and drop logins, but can’t change passwords
-- serveradmin - Perform the same actions as diskadmin and processadmin, plus manage endpoints, change instance settings, and shut down the instance
-- setupadmin - Manage linked servers
-- sysadmin - Perform any action within the instance. Members cannot be prevented from accessing any object or performing any action.

No, you cannot change permissions on them.

Front 37

How do you grant access to a database?

Back 37

By adding a login to the database as a user by executing the CREATE USER command.

Front 38

What is the syntax for the CREATE USER command?

Back 38

CREATE USER user_name
[ { { FOR | FROM }
{ LOGIN login_name
| CERTIFICATE cert_name
| ASYMMETRIC KEY asym_key_name}
| WITHOUT LOGIN ]
[ WITH DEFAULT_SCHEMA = schema_name ]

Front 39

What is a loginless user?

Back 39

A user in the database that is not associated to a login. Loginless users were added to replace application roles. Users still authenticate to the instance using their own credentials. The user’s login needs access to the database. After SQL Server changes the user’s context to the database, the user impersonates the loginless user to gain necessary permissions. Because the user is authenticating to the instance using his or her own credentials, SQL Server can still audit activity to an individual login even though the login is impersonating a loginless user.

Front 40

What are the fixed roles at the database level?

Back 40

-- db_accessadmin - Add or remove users in the database
-- db_backupoperator - Back up the database but cannot restore a database or view any information in the database
-- db_datareader - Issue SELECT against all tables, views, and functions within the database
-- db_datawriter - Issue INSERT, UPDATE, DELETE, and MERGE against all tables within the database. Members of this role must also be members of the db_datareader role.
-- db_ddladmin - Execute data defi nition language (DDL) statements
-- db_denydatareader - Prevent SELECT against all tables, views, and functions within the database
-- db_denydatawriter - Prevent INSERT, UPDATE, DELETE, and MERGE against all tables within the database
-- db_owner - Owner of the database that has full control over the database and all objects contained within the database
-- db_securityadmin - Manage the membership of roles and associated permissions, but cannot manage membership for the db_owner role
--public - Default group in every database that all users belong to

Front 41

Which logins cannot be used to authenticate to an instance?

Back 41

You cannot use logins that are mapped to a certificate or asymmetric key to authenticate to an instance.

Front 42

What are the only objects directly owned by a database user?

Back 42

schemas

Front 43

If you need to allow a user to view metadata in a database, what command do you execute?

Back 43

GRANT VIEW DEFINITION TO <user>

Front 44

What command can you execute to allow a login to view metadata on any object?

Back 44

GRANT VIEW ANY DEFINITION TO <user>

Front 45

In order for a login to see execution statistics, such as sys.dm_exec_requests, what command do you need to execute?

Back 45

GRANT VIEW SERVER STATE TO <user>

Front 46

How do you accomplish impersonation?

Back 46

{ EXEC | EXECUTE ] AS <context_specification>
<context_specification>::=
{ LOGIN | USER } = 'name'
[ WITH { NO REVERT | COOKIE INTO @varbinary_variable } ]
| CALLER

Front 47

What type of key is the Service Master Key?

Back 47

It is a symmetric key generated from the local machine key and encrypted using the SQL Server service account by the Windows Data Protection API.

Front 48

What statement is used to create a Database Master Key?

Back 48

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Strong password'>

Front 49

How is the database master key encrypted?

Back 49

Using Triple DES and the user-supplied password. A copy is also encrypted using the service master key such that automatic decryption can be accomplished within the instance.

Front 50

What is the database master key used to protect?

Back 50

Any certificates, symmetric keys or asymmetric keys that are stored in the db.

Front 51

When you make a request to decrypt data, what happens?

Back 51

The service master key is used to decrypt the database master key, that is used to decrypt a certificate, symmetric key or assymmetric key, and in turn is used to decrypt the data.

Front 52

To restore and be able to decrypt data successfully, you must be able to backup the db master key and then regenerate the db master key on another instance. What commands do you use to perform this?

Back 52

OPEN MASTER KEY
BACKUP MASTER KEY
RESTORE MASTER KEY
CLOSE MASTER KEY

Front 53

What standard are certificates based on?

Back 53

X.509

Front 54

What is the syntax for creating a self-signed certificate in SQL Server?

Back 54

CREATE CERTIFICATE certificate_name [ AUTHORIZATION user_name ]
{ FROM <existing_keys> | <generate_new_keys> }
[ ACTIVE FOR BEGIN_DIALOG = { ON | OFF } ]
<existing_keys> ::=
ASSEMBLY assembly_name | {
[ EXECUTABLE ] FILE = 'path_to_file'
[ WITH PRIVATE KEY ( <private_key_options> ) ] }
<generate_new_keys> ::=
[ ENCRYPTION BY PASSWORD = 'password']
WITH SUBJECT = 'certificate_subject_name'
[ , <date_options> [ ,...n ] ]
<private_key_options> ::=
FILE = 'path_to_private_key'
[ , DECRYPTION BY PASSWORD = 'password' ]
[ , ENCRYPTION BY PASSWORD = 'password' ]
<date_options> ::=
START_DATE = 'mm/dd/yyyy' | EXPIRY_DATE = 'mm/dd/yyyy'

Front 55

What is the purpose of a signature?

Back 55

It allows you to elevate a user's permission but to provide a restriction such that the elevation occurs only when the user is executing a specific piece of code.

Front 56

What can you add a signature to?

Back 56

stored procedures
functions
triggers
assemblies

Front 57

What is the process to sign code digitally?

Back 57

1. Create a database master key.
2. Create a certificate in the database.
3. Create a user mapped to the certificate.
4. Assign permissions on an object or objects to the user.
5. Execute ADD SIGNATURE on a module by the certificate.

Front 58

How are principals, securables, and permissions related?

Back 58

You can GRANT, REVOKE, or DENY permissions ON a securable TO a principal.

Front 59

What is an ownership chain, and how can you have a broken ownership chain?

Back 59

An ownership chain applies to objects that reference other objects within a database. The owner of the schema that contains the object is considered the owner of the object. SQL Server checks permissions for the fi rst object that you access, as well as each time the owner changes within the calling stack. The chain of object owners within a calling stack is called an ownership chain. You have a broken ownership chain when the object owner changes within a calling stack and you have not been granted sufficient permissions to continue accessing objects within the call stack.

Front 60

What principals can you not impersonate?

Back 60

Principals that have been mapped to a certificate or asymmetric key.

Front 61

What are DDL triggers?

Back 61

They allow you to trap and respond to login events.

Front 62

What is the syntax for creating a DDL trigger?

Back 62

CREATE TRIGGER trigger_name
ON { ALL SERVER | DATABASE }
[ WITH <ddl_trigger_option> [ ,...n ] ]
{ FOR | AFTER } { event_type | event_group } [ ,...n ]
AS { sql_statement [ ; ] [ ,...n ] |
EXTERNAL NAME < method specifier > [ ; ] }
Trigger on a LOGON event (Logon Trigger)
CREATE TRIGGER trigger_name
ON ALL SERVER
[ WITH <logon_trigger_option> [ ,...n ] ]
{ FOR | AFTER } LOGON
AS { sql_statement [ ; ] [ ,...n ] |
EXTERNAL NAME < method specifier > [ ; ] }

Front 63

Can you prevent an ALTER DATABASE with a DDL trigger?

Back 63

No, ALTER DATABASE executes outside the scope of a transaction and cannot be rolled back with a DDL trigger. A DDL trigger can only audit it.

Front 64

How are DDL triggers related to the Policy-based management framework?

Back 64

The Policy-based managment framework creates DDL triggers for all policies that you configure to prevent an out of compliance situation.

Front 65

What is the syntax for creating a server audit object?

Back 65

CREATE SERVER AUDIT audit_name
TO { [ FILE (<file_options> [, ...n]) ] |
APPLICATION_LOG | SECURITY_LOG }
[ WITH ( <audit_options> [, ...n] ) ] }[ ; ]
<file_options>::=
{FILEPATH = 'os_file_path'
[, MAXSIZE = { max_size { MB | GB | TB } | UNLIMITED } ]
[, MAX_ROLLOVER_FILES = integer ]
[, RESERVE_DISK_SPACE = { ON | OFF } ] }
<audit_options>::=
{ [ QUEUE_DELAY = integer ]
[, ON_FAILURE = { CONTINUE | SHUTDOWN } ]
[, AUDIT_GUID = uniqueidentifier ]}

Front 66

What does QUEUE_DELAY control for audit objects?

Back 66

Whether messages are sent synchronously or asynchronously; when set to 0, audit records are sent to the audit log synchronously with the transacton.

Front 67

What does the On_FAILURE action control in an audit object?

Back 67

It controls how the instance behaves if audit records cannot be written. The default option is CONTINUE, which allows the instance to continue running and processing transactions. If you specify a value of SHUTDOWN, if the audit record cannot be written to the log within the specifi ed QUEUE_DELAY interval, the instance is shut down.

Front 68

What is C2 auditing?

Back 68

A U.S. Department of Defense audit specification.

Front 69

What can columns be encrypted using?

Back 69

hash
passphrase
symmetric key
asymmetric key
certificate

Symmetric keys provide best balance between performance and securing data. Asymmetric keys and certificates provide strongest encryption.

Front 70

Can an encrypted column be indexed? Searched?

Back 70

No. No.

Front 71

What is an example of a one-way encryption algorithm (can only encrypt, not decrypt)?

Back 71

hash

Front 72

What does the On_FAILURE action control in an audit object?

Back 72

It controls how the instance behaves if audit records cannot be written. The default option is CONTINUE, which allows the instance to continue running and processing transactions. If you specify a value of SHUTDOWN, if the audit record cannot be written to the log within the specifi ed QUEUE_DELAY interval, the instance is shut down.

Front 73

What is C2 auditing?

Back 73

A U.S. Department of Defense audit specification.

Front 74

What can columns be encrypted using?

Back 74

hash
passphrase
symmetric key
asymmetric key
certificate

Symmetric keys provide best balance between performance and securing data. Asymmetric keys and certificates provide strongest encryption.

Front 75

Can an encrypted column be indexed? Searched?

Back 75

No. No.

Front 76

What is an example of a one-way encryption algorithm (can only encrypt, not decrypt)?

Back 76

hash

Front 77

How does SQL Server handle passwords?

Back 77

SQL Server uses an MD5 hash to handle passwords. When a password is specifi ed for an object, SQL Server applies an MD5 hash and stores the hash value. When you specify a password to access an object, the password is hashed using the same MD5 hash, the hashed password is transmitted in a secure channel, and the hash value transmitted is compared to the hash value stored. Even an administrator who is running a trace cannot access the password.

Front 78

What are the 5 hash algorithms allowed by SQL Server?

Back 78

SHA
SHA1
MD2
MD4
MD5

Front 79

Why is it not a good idea to use a hash algorithm to encrypt birth dates, salaries or credit card numbers?

Back 79

Hash algorithms are vulnerable to brute force attacks. If the range of values that you are seeking to encrypt is small, an attacker can easily generate all the possible hashes for the range of possible values. After generating these hashes, the attacker needs to compare the hash values to fi nd a match and thus reverse-engineer your data.

Front 80

How can you increase the complexity of a hash?

Back 80

By salting - adding a string of one more more characters to the value before hashing.

Front 81

What keys utilize a single key for both encryption and decryption?

Back 81

symmetric

Front 82

Which key is generated by a key server and cannot be backed up or moved from one system to another?

Back 82

asymmetric

Front 83

What does Transparent Data Encryption (TDE) provide?

Back 83

TDE is used to encrypt “data at rest.” The contents of the data and transaction log, along with any backups, are encrypted by the engine. TDE works by using an encryption key stored within the database boot record. The TDE key is encrypted by using a certificate within the master database.

Front 84

What is the process of implementing TDE on a database?

Back 84

1. Create a database master key in the master database.
2. Create a certificate in the master database.
3. Create a database encryption key in the target database using the certificate in the master database.
4. Alter the database and enable encryption.

Front 85

Why should you backup the certificate used for TDE and store the backup in a safe place?

Back 85

After you encrypt it, you cannot access your data without the certificate.