When establishing transparent data encryption, you used a certificate stored in SQL Server and a database encryption key created using this certificate. How should this certificate and key be handled as part of the regular backup process?

When establishing transparent data encryption, you used a certificate stored in SQL Server and a database encryption key created using this certificate. How should this certificate and key be handled as part of the regular backup process?

Answer: -  Export the certificate and key to a file and store this certificate and key backup in a separate highly secure location.

(While the certificate and the database encryption key are stored within SQL Server and thus backed up within SQL Server, restoring a backup to a replacement server requires the certificate and key to be installed first. This means that the certificate and the encryption key must be stored outside of the regular database backups. Storing these files with the backups defeats the purpose of TDE so these files should be stored separately.)

Your database contains a table named customers, which has a column named credit_card that contains the primary credit card number that the customer uses for purchases. This column of data must be encrypted. How can you best encrypt this column so that regular database users cannot read a credit card number?

Your database contains a table named customers, which has a column named credit_card that contains the primary credit card number that the customer uses for purchases. This column of data must be encrypted. How can you best encrypt this column so that regular database users cannot read a credit card number?

Answer: -  Create a certificate, then create a key from this certificate. Next encrypt the credit_card column data using this key, a predefined encryption algorithm, and an encryption function.

(Encrypting a column of data requires the use of a key, an encryption algorithm, and a function to perform the encryption. TDE encrypts an entire database and is transparent to an authorized database user.)

How does C2 compare to various security and encryption methods?

How does C2 compare to various security and encryption methods?

Answer: -  C2 is an auditing set of criteria, and it is not a means of establishing security nor encrypting data.

(C2 is a standard set of auditing criteria. Except for auditing access to secured or encrypted data, it has nothing to do with either encryption or security.)

You have been asked to establish the use of extensible key management (EKM) for a database that contains highly valuable data. Currently your SQL Server is running Windows Server 2008 64 but with SQL Server Standard Edition also using 64 bit. What steps must you take in order to implement EKM?

You have been asked to establish the use of extensible key management (EKM) for a database that contains highly valuable data. Currently your SQL Server is running Windows Server 2008 64 but with SQL Server Standard Edition also using 64 bit. What steps must you take in order to implement EKM?

Answer: -  All of the above are required.

(Extensible key management (EKM) requires the Enterprise Edition of SQL Server as well as software and a dll file from the EKM provider. A Hardware Security Module may optionally be used. Once the dll file is available, SQL Server must be configured.)

All SQL Server backups are made using transparent data encryption (TDE). The backup process involves first performing a backup to disk storage then copying the disk backup files to tape and storing the tapes at offsite facility. A courier service picks up each night’s backup tapes every morning. This morning, shortly after the courier picked up the tapes at your facility, the courier’s van was stolen. Upper management is concerned that the thief who now has your backup tapes can read highly confidential information from these tapes. What should you tell upper management?

All SQL Server backups are made using transparent data encryption (TDE). The backup process involves first performing a backup to disk storage then copying the disk backup files to tape and storing the tapes at offsite facility. A courier service picks up each night’s backup tapes every morning. This morning, shortly after the courier picked up the tapes at your facility, the courier’s van was stolen. Upper management is concerned that the thief who now has your backup tapes can read highly confidential information from these tapes. What should you tell upper management?

Answer: – The thief might be able to read he files on the tapes but transparent data encryption will prevent the thief from making any sense out of the data.

(The thief can perhaps find a way to read the backup tapes, however the contents of each backup file are still encrypted with transparent data encryption (TDE). This encryption cannot be removed by copying the backup files. Without the certificate and database encryption key used to set up TDE, the data will not be readable as meaningful data. Any SQL Server instance created would need this certificate and master key to access the data through TDE.)

You are the administrator of a SQL Server system that will contain marketing, sales, and production data. Each of these departments is contains in a Windows group named after the department. Each of these departments should be able to read and modify its own data, but they should not be able to read or modify that data of other departments. You need to configure the server so it meets security requirements with minimal administrative overhead and resource consumption. What should you do?

You are the administrator of a SQL Server system that will contain marketing, sales, and production data. Each of these departments is contains in a Windows group named after the department. Each of these departments should be able to read and modify its own data, but they should not be able to read or modify that data of other departments. You need to configure the server so it meets security requirements with minimal administrative overhead and resource consumption. What should you do?

B – Create a separate database for each department.
D – Create a Windows Authenticated login for each department.
F – Map each user account to the db_datareader and db_datawriter database roles.
H – Create database user accounts for each department in the database.

(In this instance, you should create a separate database for each of the departments so they do not have access to the other departments’ data. You also need to create a login for each of the groups and then create a database user account in the corresponding database for each login. Finally, add the accounts to the db_datareader and db_datawriter roles.)

You are the administrator of a SQL Server system that will be used only for development access; the server will have no production databases on the server whatsoever. All your developers need to be able to create databases and object inside the databases, such as tables, views, and so on. To which roles should they be added at the server and database levls to accommodate these needs?

You are the administrator of a SQL Server system that will be used only for development access; the server will have no production databases on the server whatsoever. All your developers need to be able to create databases and object inside the databases, such as tables, views, and so on. To which roles should they be added at the server and database levls to accommodate these needs?

Answer: – db_creator at the server level and db_ddladmin at the database level

(The db_creator membership will give the developers just enough permission to create databases at the server level, and db_ddladmins will give them just enough permission to create objects in the databases they create. The sysadmin and db_owner roles will give them too much permission and, therefore, result in lax security.)

You have a number of users in your customer service department who need Select, Insert, and Update permissions, but they should not be able to delete—only managers should have the permission to delete data. How can you ensure that only managers can delete data and users can only perform the tasks listed?

You have a number of users in your customer service department who need Select, Insert, and Update permissions, but they should not be able to delete—only managers should have the permission to delete data. How can you ensure that only managers can delete data and users can only perform the tasks listed?

Answer: – Add the users to a custom role that allows only Select, Insert, and Update permissions; add the managers to the db_datareader and db_datawriter roles.

(No fixed database role allows the permissions that the users need, but the managers need the permissions that are allowed by the db_datareader and db_datawriter roles. Therefore, you need to use fixed roles for the managers and custom roles for the users.)

You have an HR database that all users will be allowed to read from to obtain information, but only the HR department should be able to read from and update the data in the database. Select the easiest and most secure way to ensure this.

You have an HR database that all users will be allowed to read from to obtain information, but only the HR department should be able to read from and update the data in the database. Select the easiest and most secure way to ensure this.

Answer: – Add all the users who are not in the HR department to the db_datareader and bd_denydatawriter database roles, and add all the users from the HR department to the db_datareader and db_datawrite database roles.

(Users can be members of more than one group, so it is easiest to add the members of HR to the db_datareader and db_datawriter roles; add everyone else to the db_datareader role to grant the permission to read data and to the db_denydatawriter role to deny them the permission to modify data.)

You need to create a new login account for one of your Unix users named WoodsJ. Which command would you use to do this?

You need to create a new login account for one of your Unix users named WoodsJ. Which command would you use to do this?

Answer: -  sp_addlogin ‘WoodsJ’, ‘Pa$$w0rd’, ‘AdventureWorks’

(Because this is a Unix user, you know the user does not have a Windows account against which to be verified. You must use sp_addlogin as opposed to sp_grantlogin, which is used only for mapping to Windows accounts. The other two stored procedures do not exist.)

You want to be able to use email. replication, and other interserver services with SQL Server. When you install SQL Server, which type of account should you use?

You want to be able to use email. replication, and other interserver services with SQL Server. When you install SQL Server, which type of account should you use?

Answer: – A domain account with administrative privileges.

 (If you want to perform replication, your SQL Server Agent service needs to log in with administrative access. All other interserver services (such as e-mail) need at least a domain account with access to the requested services.)

You are setting up a kiosk in a library that hundreds of people will access every month. You want to make sure visitors to library have access to read data from the SQL Server, but they should not be able to change any of the data. You need to accomplish this with the least administrative overhead possible. What should you do?

You are setting up a kiosk in a library that hundreds of people will access every month. You want to make sure visitors to library have access to read data from the SQL Server, but they should not be able to change any of the data. You need to accomplish this with the least administrative overhead possible. What should you do?

Answer: – Enable the guest user account in Windows, and map it to a SQL login. No database user account named guest will beed to be created because it already exists in each database. Add the guest account to the db_denydatawrited and db_datareader roles.

(Creating a user account especially for this application is possible but hard to manage, especially when a database user account already exists for each database. Therefore, creating a user login for the guest account is the easiest way to allow access to the kiosk.)

You have just installed a new SQL Server on your network, and you want to make sure no Windows administrator has administrative access on the SQL Server until receiving the proper training. What you do to keep a Windows administrator from trying to administer the new SQL Server and possibly damaging it?

You have just installed a new SQL Server on your network, and you want to make sure no Windows administrator has administrative access on the SQL Server until receiving the proper training. What you do to keep a Windows administrator from trying to administer the new SQL Server and possibly damaging it?

Answer: – Remove the BUILTIN\Administrators account from SQL Server. Then create a SQLAdmins group in Windows, and add all the SQL administrators to the new group. Finally, create a login mapped to the SQLAdmins group, and add it to the sysadminds role.

(The most secure and easiest way to accomplish this task is to remove the Windows Administrators group from the SQL Server and add a new group of your own creation in its place. You do not actually have to remove the login entirely; however, because you have no use for it afterward, you don’t need to keep it around.)

You have several SQL Servers in your organization that participate in linked server queries, with security configured as shown in Table 14-3. BobH complains that the linked server queries are not working. What can’t BobH use linked server queries?

You have several SQL Servers in your organization that participate in linked server queries, with security configured as shown in Table 14-3. BobH complains that the linked server queries are not working. What can’t BobH use linked server queries?

Answer: – The users who cannot access the linked server use standard logins, so you need to map a linked server login by executing sp_addlinkedsrvlogin on the local server.

(For users who use standard logins to access a linked server, you need to map a local login to a login on the remote server using the sp_addlinkedsrvlogin command.)

You have the authority to create both Windows accounts and SQL logins and roles on your network. You have a Windows server that contains a shared folder called Administration and a shared folder called Marketing. On your SQL Sever database you have database called Marketing. Ten of your users will be working on a short-term project together; all of them require the same access to the Marketing database on the SQL Server and the Marketing folder on the Windows server, but only four of them are allowed access to the Administration folder on the Windows server. Choose the best way to grant these users access to the database resources.

You have the authority to create both Windows accounts and SQL logins and roles on your network. You have a Windows server that contains a shared folder called Administration and a shared folder called Marketing. On your SQL Sever database you have database called Marketing. Ten of your users will be working on a short-term project together; all of them require the same access to the Marketing database on the SQL Server and the Marketing folder on the Windows server, but only four of them are allowed access to the Administration folder on the Windows server. Choose the best way to grant these users access to the database resources.

Answer: – Create separate Windows logins for each user, and add them to a custom database role. Then assign permissions to the database role/

 (Because the users do not need access to the same resources on the Windows servers, you have no reason to create a Windows group for them. And because there are so few users here, it is easiest to create user accounts for each user and add them to a custom database role.)

You are the administrator of a SQL Server system that contains a database named Accounting. To maintain strict security on the database, you want to make sure users do not have any default permissions when their account is first created. What should you do?

You are the administrator of a SQL Server system that contains a database named Accounting. To maintain strict security on the database, you want to make sure users do not have any default permissions when their account is first created. What should you do?

Answer: – Execute the REVOKE ALL FROM PUBLIC command in Query Editor while using your database.

(Users cannot be removed from the public role, which has every permission granted by default. The easiest way to remove these permissions is with the REVOKE ALL FROM PUBLIC command.)

But Bob now complains he cannot access SQL Server when he logs in with his Windows account. Why not?

You have just created a new Windows account (Domain\BobH) for a new employee. You create a new SQL login for BobH using the following command:

sp_addlogin ‘domain\BobH’, ‘password’, ‘accounting’

But Bob now complains he cannot access SQL Server when he logs in with his Windows account. Why not?

Answer: – The sp_addlogin command creates standard login accounts, not mapped login accounts. You need to map Bob’s account to a SQL login with the sp_grantlogin stored procedure.

(You must use sp_grantlogin to map a SQL Server login to a Windows login. The sp_ addlogin stored procedure creates standard logins.)

You have spent a great deal of money and effort to create a custom accounting program in VisualBasic designed to meet some specific needs of your company. You find that some of your users still access your database through other methods such as Microsoft Excel and Query Analyzer, which causes problems with the integrity of your database. How can you fix this problem?

You have spent a great deal of money and effort to create a custom accounting program in VisualBasic designed to meet some specific needs of your company. You find that some of your users still access your database through other methods such as Microsoft Excel and Query Analyzer, which causes problems with the integrity of your database. How can you fix this problem?

Answer: – Create an application role, and grant it the necessary permissions in the database. Then remove any permissions for your users in the database, and hard-code the sp_setapprole stores procedure into your application to activate the role.

(In this case, you need to create an application role and activate it through your VisualBasic code. This will cause SQL Server to see all your users as the application role and grant them all of the rights and permissions of that role.)

You need to grant Robery permission to modify employee phone numbers in the Employees table, but you do not want him to be able to modify any other data in the table. Select the best way to accomplish this.

You need to grant Robery permission to modify employee phone numbers in the Employees table, but you do not want him to be able to modify any other data in the table. Select the best way to accomplish this.

Answer: -  Create a stored procedure to change the phone number, and grant Robert Execute permission on the stored procedure.

(Column-level permissions are possible in SQL Server, but they are hard to maintain, and rarely the answer to security problems. You could use a view, but it is not usually best to create a view for just a single column. Creating a stored procedure and granting Robert Execute permission is the best way to fix this issue.)

Two developers named IversonB and JacksonT need to be able to create objects in the Inventory database as part of their regular duties. You need to give them the ability to create these objects without giving them too much author on the server. Choose the most secure way to do this.

Two developers named IversonB and JacksonT need to be able to create objects in the Inventory database as part of their regular duties. You need to give them the ability to create these objects without giving them too much author on the server. Choose the most secure way to do this.

Answer: – Add IversionB and JacksonT to the db_ddladmin fixed database role, and instruct them to create objects as DBO.

(Adding users to the db_ddladmin role is the most secure way to accomplish this goal. Adding them to the db_owner or sysadmin role would grant them too much authority over the database and would not maintain strict security. Having them create objects as DBO will avoid broken ownership chains, as well.)