2008 Sql Server Chapter 11

Front 1

Endpoints can be of two different transports: ____ and ______.

Back 1

TCP
HTTP

Front 2

An endpoint has two basic parts: a ____ and a _____ .

Back 2

transport
payload

Front 3

An endpoint can have one of three states:

Back 3

STARTED
STOPPED
DISABLED.

Front 4

The first layer of access security is determined by the _____ state.

Back 4

endpoint

Front 5

The second layer of security is permission to connect to the ______.

Back 5

endpoint

Front 6

You can create Transmission Control Protocol (TCP) endpoints with three different payloads:

Back 6

TSQL
DATABASE_MIRRORING
SERVICE_BROKER

Front 7

Because port ______ is the default TCP port number for a DATABASE_MIRRORING endpoint and _____ is the default TCP port for a TSQL endpoint, you might want to specify a different port number to avoid DOS attacks.

Back 7

5022
1433

Front 8

TSQL endpoints do not have any additional configuration options beyond the universal _____ settings.

Back 8

TCP

Front 9

When all Database Mirroring and Service Broker instances reside within a single domain or across trusted domains, you should use _____ authentication. When instances span non-trusted domains, you should use _______-based authentication.

Back 9

Windows
certificate

Front 10

Use _____ for minimal encryption strength and best performance. Use _____ if you require strong encryption.

Back 10

RC4
AES

Front 11

You can specify only one TCP endpoint with a payload of ________ for each instance of SQL Server.

Back 11

DATABASE_MIRRORING

Front 12

What are the two parts of an endpoint?

Back 12

An endpoint has a transport defined as either TCP or HTTP and has a payload defined as TSQL, SERVICE_BROKER, DATABASE_MIRRORING, or SOAP.

Front 13

What are the three states of an endpoint, and what is the difference between each state?

Back 13

The three states are STARTED, STOPPED, and DISABLED. An endpoint that is STARTED listens for and allows connections. An endpoint that is STOPPED listens for connection requests and returns an error message. An endpoint that is DISABLED does not respond to any request.

Front 14

What authority must be granted before an endpoint allows a connection request?

Back 14

granted the CONNECT permission on the endpoint

Front 15

What types of authentication are available for Service Broker and Database Mirroring endpoints?

Back 15

NTML or Kerberos authentications can be specified. You can also specify an option of NEGOTIATE, which causes the specific authentication method to be negotiated between the application and the endpoint.

Front 16

What are the two universal arguments for TCP endpoints?

Back 16

You are required to specify a port for the endpoint to listen on. If you want, you can configure an IP address that restricts the endpoint to respond to traffic coming only from the specified IP address.

Front 17

Endpoints in SQL Server act very similar to ________ by filtering out any traffic that does not meet allowed formats.

Back 17

firewalls

Front 18

Each endpoint has a transport that is defined as either ___ or _____.

Back 18

TCP
HTTP

Front 19

Endpoints have a second part called the payload, which is defined as ____, _______, _____, or ______.

Back 19

tsql
database_mirroring
service_broker
soap

Front 20

__________ endpoints are configured during installation to listen on the port number specified for the instance .

Back 20

TSQL

Front 21

_________ and _________endpoints can have an authentication method specified as well as enabled for encrypting all traffic sent based on an algorithm that you specify.

Back 21

Service Broker
Database Mirroring

Front 22

How do you configure an instance so that only local connections are allowed?

Back 22

The TCP/IP provider enables connections to be created to the instance remotely. By disabling the TCP/IP provider, you can create only local connections to the instance.

Front 23

What do you use to enable or disable features for an instance?

Back 23

The sp_configure system stored procedure is used to enable or disable features.

Front 24

The first surface area configuration decision that you make occurs during installation, when you decide whether to force all login access to the instance to use _______ credentials.

Back 24

Windows-only

Front 25

You should disable the _______ for any instance that you do not want remote connections.

Back 25

TCP/IP provider

Front 26

The _______ tool is used to enable or disable features.

Back 26

sp_configure

Front 27

Logins within SQL Server 2008 can be five different types:

Back 27

Standard SQL Server login
Windows login
Windows group
Certificate
Asymmetric key

Front 28

You can create SQL Server logins mapped to ____ or ______ keys.

Back 28

certificates
asymmetric

Front 29

Logins mapped to certificates and asymmetric keys are used internally as a ____________.

Back 29

security container

Front 30

When the ________ option (the default and recommended setting) is enabled, SQL Server 2008 enforces the Windows password policy settings when you create a SQL Server login.

Back 30

CHECK_POLICY

Front 31

____________is used to prevent brute force attacks against a login.

Back 31

CHECK_EXPIRATION

Front 32

Using Windows _______ provides the greatest flexibility for managing security access.

Back 32

groups

Front 33

role - Administer BCP and Bulk Insert operations

Back 33

bulkadmin

Front 34

role - Creates databases

Back 34

dbcreator

Front 35

role - Manage disk resources

Back 35

diskadmin

Front 36

role - Manage connections and start or pause an instance

Back 36

processadmin

Front 37

role - Create, alter, and drop logins, but can’t change passwords

Back 37

securityadmin

Front 38

role - Perform the same actions as diskadmin and processadmin, plus manage endpoints, change instance settings, and shut down the instance

Back 38

serveradmin

Front 39

role - Manage linked servers

Back 39

setupadmin

Front 40

role - Perform any action within the instance. Members cannot be prevented from accessing any object or performing any action.

Back 40

sysadmin

Front 41

______ 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.

Back 41

Loginless

Front 42

Loginless users are designed to replace _____ roles. Loginless users also provide a much better audit trail than an application role because each user must authenticate to the ______ using their own credentials instead of using a generic account.

Back 42

application
instance

Front 43

fixed role - Add or remove users in the database

Back 43

db_accessadmin

Front 44

fixed role - Back up the database but cannot restore a database or view any information in the database

Back 44

db_backupoperator

Front 45

fixed role - Issue SELECT against all tables, views, and functions within the database

Back 45

db_datareader

Front 46

fixed role - 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.

Back 46

db_datawriter

Front 47

fixed role - Execute data defi nition language (DDL) statements

Back 47

db_ddladmin

Front 48

fixed role - Prevent SELECT against all tables, views, and functions within the database

Back 48

db_denydatareader

Front 49

fixed role - Prevent INSERT, UPDATE, DELETE, and MERGE against all tables within the database

Back 49

db_denydatawriter

Front 50

fixed role - Owner of the database that has full control over the database and all objects contained within the database

Back 50

db_owner

Front 51

fixed role - Manage the membership of roles and associated permissions, but cannot manage membership for the db_owner role

Back 51

db_securityadmin

Front 52

fixed role - Default group in every database that all users belong to

Back 52

public

Front 53

Which logins cannot be used to authenticate to an instance?

Back 53

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

Front 54

What database principal was created as a replacement for an application role?

Back 54

Loginless users are the replacement for an application role.

Front 55

Logins can be mapped to certificates or asymmetric keys, but logins mapped to certificates or asymmetric keys cannot be used to authenticate to an ________.

Back 55

instance

Front 56

Since the _____account cannot be locked out, you should rename the account using the ALTER LOGIN command.

Back 56

sa

Front 57

Loginless users, created as a replacement to application roles, are users in a database that are not mapped to a ____.

Back 57

login

Front 58

_____ are the objects on which you grant permissions. Every object within SQL Server, including the entire instance, is a ________.

Back 58

Securables
securable

Front 59

A stored procedure is nothing more than an ____ to the database, which means that developers do not even need to know the structure of the database.

Back 59

API

Front 60

To create a schema owned by another database principal, the user creating the schema must have ________ permission on the principal being designated as the schema owner.

Back 60

IMPERSONATE

Front 61

____________provide the basis for the encryption hierarchy within SQL Server and are also required to before you can create a certifi cate or asymmetric key.

Back 61

Master keys

Front 62

You have a single _____ for the entire instance along with a database ______ within each database.

Back 62

service master key
master key

Front 63

By default the _________ is used to encrypt any
database master key that is created within the instance.

Back 63

service master key

Front 64

A database master key must be generated explicitly using the following command:

Back 64

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘<StrongPasswrd>’

Front 65

The database ________ is used to protect any certificates, symmetric keys, or asymmetric keys that are stored within a database

Back 65

master key

Front 66

A _________ certificate is essentially a fi le that is supplied by a certifi cate authority that validates the entity using the certifi cate

Back 66

public

Front 67

________certificates are generated by and used to protect data within an organization.

Back 67

Private

Front 68

How are principals, securables, and permissions related?

Back 68

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

Front 69

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

Back 69

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 first 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 70

You GRANT permissions ON a _________TO a principal.

Back 70

securable

Front 71

All ________within SQL Server is secured. If you have not been granted permission on an object, you do not even see the object

Back 71

metadata

Front 72

Digital signatures can be applied to a code module through the ____________ statement to provide a means to escalate permissions only when you execute a specified module without allowing direct access to the underlying objects.

Back 72

ADD SIGNATURE

Front 73

An important feature of DDL triggers is the ability to _____ an action.

Back 73

roll back

Front 74

Within the execution context of the DDL trigger, you have access to a special function, __________, that provides information about the DDL action

Back 74

EVENTDATA()

Front 75

Which object can be used to audit as well as prevent most object changes?

Back 75

DDL triggers can audit any DDL command. If the DDL command executes within a transaction, a DDL trigger can be used to roll back the DDL and prevent the can be used to roll back the DDL and prevent the
change from occurring

Front 76

Which object is required before you can create a server or database audit specification?

Back 76

You must create a server audit object before a server or database audit specification can be created.

Front 77

_____triggers can be created to fire when specific DDL events or events within a group are executed.

Back 77

DDL

Front 78

If the DDL event executes within the context of a transaction, you can use a _____trigger to prevent the action from occurring.

Back 78

DDL

Front 79

_________creates an instance of an audit object.

Back 79

CREATE SERVER AUDIT

Front 80

After you create an audit object, you can hook server and database audit specifications to the audit object in order to ___________auditing.

Back 80

centrally manage

Front 81

________can be encrypted using a hash, passphrase, symmetric key, asymmetric key, or a certificate.

Back 81

Columns

Front 82

__________are commonly used encryptions since they provide the best balance between securing data and performance. __________and __________ provide
the strongest encryption and decryption method.

Back 82

Symmetric keys
Asymmetric keys
certificates

Front 83

A ________ is a one-way algorithm that allows you to encrypt data but does not allow decryption.

Back 83

hash algorithm

Front 84

SQL Server allows you to specify five different hash algorithms...

Back 84

SHA, SHA1, MD2, MD4, and MD5

Front 85

Hash algorithms are also platform-_______.

Back 85

agnostic

Front 86

A ________is a string of one or more characters that are added to the value before hashing.

Back 86

salt

Front 87

________keys provide the best possible performance for routine use of encrypted data.

Back 87

symmetric

Front 88

_______keys are generated by a key server within an organization and cannot be backed up or moved from one system to another.

Back 88

Asymmetric

Front 89

To prevent the theft of data as it resides on disk or within a backup, SQL Server 2008 introduced ______________

Back 89

Transparent Data Encryption (TDE)

Front 90

TDE provides real-time encryption and decryption services to ensure that data within the _______and ________ is encrypted.

Back 90

files
backups

Front 91

TDE works by using an encryption key stored within the database _________.

Back 91

boot record

Front 92

The TDE key is encrypted by using a certificate within the __________database.

Back 92

master

Front 93

The process of implementing TDE on a database is as follows:

Back 93

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 94

You must back up the ______used for TDE and store the backup in a safe location. After you encrypt it, you cannot access your data without the ________.

Back 94

certificate
certificate

Front 95

What object is required to implement TDE?

Back 95

You must create a certificate in the master database that is used to encrypt the database encryption key.

Front 96

What do you need to do to a hash algorithm to increase the complexity when the range of possible encryption values is small?

Back 96

If the range of possible values to encrypt is small, you need to salt the hash value in order to defeat brute force attacks.

Front 97

Data can be encrypted within tables using a _______, a _____, a _______key, an ________key, or a _________.

Back 97

hash algorithm
passphrase
symmetric key
asymmetric key
certificate

Front 98

A hash algorithm should be used with a salt value unless the range of values being encrypted is _________ to defeat a brute force attack.

Back 98

large enough

Front 99

_______is used to encrypt “data at rest.” The contents of the data and transaction log, along with any backups, are encrypted by the engine.

Back 99

TDE

Front 100

If you implement TDE, make certain that you have a backup of the ___________along with the _____; otherwise, you will not be able to restore a backup.

Back 100

certificate
private key