1. | You have just installed a new SQL Server 2005 instance with all defaults and upgraded a database from SQL Server 2000. One of the users reports that a function in the application that uses xp_cmdshell to send a Windows message is not working. What is the likely problem?
|
|
2. | You have set up a new database for testing using the Developer edition of SQL Server 2005. However, the test group complains that they cannot access the server because remote connections are not enabled. What should you do?
|
|
3. | You are looking to encrypt the salary information in an HR database on your SQL Server 2005 server. To maintain the best performance possible while ensuring strong encryption, how should you set up the encryption?
|
|
4. | You want to encrypt the credit card data in your SalesOrders table. No encryption commands have been run on your SQL Server 2005 server. What is the first step you need to take?
|
|
5. | How are user-defined database roles different from application roles?
|
|
6. | You want to allow Kendall to perform backups of the Sales database and receive the permissions of the SalesRep role in that database. Which two steps should you take?
|
|
7. | In SQL Server 2005, how are objects grouped inside a database for security purposes?
|
|
8. | Which type of encryption key can be set to expire after a particular date?
|
|
9. | Which of these methods makes the most sense for applying patches to your 12 production SQL Server instances?
|
|
10. | One of your development SQL Servers is experiencing hung queries, and you decide to delegate the ability to kill processes to one of the developers. To which server role should you add them?
|
|
11. | For the SQL Server 2005 instance that will only respond to queries and not perform any network access, which account should be used to run the service?
|
|
12. | You need the SQL Server Agent service to send mail to a remote Exchange Server for alerting DBAs of issues, and it should be able to detect low periods of activity in order to schedule the reindexing of tables. Which two steps should you take?
|
|
13. | You want to be sure that only certain users in a database are members of each role for a security audit. How can you easily do this?
|
|
14. | You need to set up a temporary system administrator for your SQL Server. A technical support person from an application vendor will use it for troubleshooting. After the troubleshooting is complete, what should you do?
|
|
15. | You change the password on one of your SQL Server service accounts and restart the service. Later, after rebooting another instance, you find it won’t start and discover it uses the same service account as the first instance. What should you do?
|
|
16. | Which permission gives a user the same level of permission as being the securable’s owner?
|
|
17. | You are following the Microsoft recommendations for password policy inside SQL Server. Which of these is a valid password for the login Steve?
|
|
18. | You have created a new domain user account to run your SQL Server instance. The appropriate permissions have been assigned to the SQL Server files in the file system. Which additional rights do you need to grant?
|
|
19. | You are deploying encryption inside your SQL Server and want to choose the securest algorithm to encrypt your keys. Which one of these should you choose?
|
|
20. | You are setting up a number of new login accounts using SQL Server authentication and want to be sure that the users change their passwords right away. What check box should you check?
|
|
Answers
1. | C. By default in SQL Server 2005, the xp_cmdshell extended stored procedure is disabled. By running the Surface Area Configuration tool, you can enable this and allow users to execute this procedure. |
2. | A. By default, remote connections are not enabled in the Developer edition of SQL Server. You can enable them with the Surface Area Configuration tool. |
3. | D. The best encryption strategy is to encrypt data with a symmetric key and secure that key with an asymmetric key. You should also choose to limit the columns that you encrypt to only those that are deemed critical to protect. |
4. | C. The first step in deploying encryption inside a database is to create the database master key. Once this is created, you can create asymmetric keys to secure the symmetric keys that encrypt the data. |
5. | C. In an application role, the permissions are granted to any user who invokes it. In a user-defined role, only those users assigned to the role get the permissions. |
6. | B, D. To assign the minimum permissions necessary to meet the requirements, you should add Kendall to the db_backupadmin role and the SalesRep role. Adding these same permissions to the user Kendall would work, but this creates unnecessary administration. |
7. | B. Although you could use all these to group objects, the schema is a construct specifically set up for security and administrative purposes. |
8. | A. Certificates use asymmetric algorithm principles but are separate from asymmetric keys in SQL Server 2005. They can be created with an expiration date. |
9. | D. The best solution for a dozen servers is to use a scheduling and deployment tool of some sort to deploy patches. Because a patch can cause issues with a SQL Server, automatic updates are not recommended. |
10. | B. The processadmin role allows a user to terminate processes and connections. Since you want to apply the least permissions necessary, this is the best choice. |
11. | B. The Local Service account is the one best suited for a service that will not access the network. Limited Service does not exist. |
12. | B, D. For the SQL Server Agent service to detect periods of low activity, it must be a member of the computer’s Administrators group. To send mail to a remote Exchange server, a domain user is needed. |
13. | C. The easiest method is to run sp_helpuser. This will show you only role information and not explicit permission assignments, which can be confusing. Checking the properties of each user will work, but it is tedious. |
14. | A. You should disable accounts used for temporary purposes but that could be needed again in order to prevent anyone from accessing the account. |
15. | C. Two different instances should use two separate service accounts to run the separate instances. If you find one service account being shared, create a second account, and configure one instance to use the new account. |
16. | A. The CONTROL permission implies all the same rights as if the principal were the owner of the securable. |
17. | A. The only one of these that is valid is the first one. The second one is not long enough, the third contains the username, and the fourth does not have either a numeric character or a nonalphanumeric symbol. |
18. | A. The only other permission that you need to assign to this user account is the Log On As a Service permission on the particular machine running SQL Server. |
19. | C. The strongest encryption algorithm for encrypting keys is an asymmetric one. Longer key lengths are more secure than shorter ones, so the 2,048-bit key length is the most secure. |
20. | D. By checking the User Must Change Password at Next Login check box, the user will be allowed to connect to SQL Server but be immediately forced to change their password. |