Review Questions

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? The xp_cmdshell stored procedure is not available in SQL Server 2005. The Windows executable is not available on this server. The Surface Area Configuration tool needs to be modified to enable this functionality. Windows messages are disabled in SQL Server 2005 servers.
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? Run the Surface Area Configuration tool, and enable remote connections using TCP/IP. Run the Surface Area Configuration tool, and enable the CLR environment. Add the developer group to the server Logins folder. Add the developer group to each database’s db_owner role.
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? Encrypt the entire table with an asymmetric key. Encrypt the entire table with a symmetric key, and secure that with an asymmetric key. Encrypt the salary amount column only with an asymmetric key. Encrypt the salary amount column only with a symmetric key, and secure that with an asymmetric key.
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? Create a symmetric key to encrypt the data. Create an asymmetric key to encrypt the data. Create a database master key. Create the server encryption key.
5.	How are user-defined database roles different from application roles? A user-defined role can have custom permissions; an application role has preset permission sets. A user-defined role has preset permissions; an application role has custom permissions. A user-defined role grants permissions only to members; an application role can grant permissions to any user. A user-defined role allows access to data; an application role grants administrative permissions.
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? Grant Kendall BACKUP DATABASE rights. Add Kendall to the db_backupadmin role. Grant Kendall rights to all the objects that the SalesRep role has permissions to access. Add Kendall to the SalesRep role. Add Kendall to the db_owner role.
7.	In SQL Server 2005, how are objects grouped inside a database for security purposes? By role By schema By user By name
8.	Which type of encryption key can be set to expire after a particular date? Certificates Symmetric keys Asymmetric keys Credentials
9.	Which of these methods makes the most sense for applying patches to your 12 production SQL Server instances? Enable automatic updates using Windows Update. Enable automatic updates against your WSUS server. Manually apply patches. Use SMS to schedule patches after they have been tested.
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? sysadmin processadmin securityadmin setupadmin
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? Local System Local Service Network Service Limited 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? Use the Local Service account to run the service. Add the service account to the computer’s Administrators group. Add the SQL Server instance to the Exchange Server. Run the service under a domain user account. Run the service under the Network Service account.
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? Check the properties of each user in the database. Query the sys.database_principals catalog view for role membership. Run the sp_helpuser stored procedure. Run the sp_who stored procedure.
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? Disable the login. Delete the login. Change the password on the login. Do nothing.
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? Change the password in the second instance’s Configuration Manager to the new password. Temporarily use your domain administrator’s account to start the service and make a note. Create a new service account, and change the second instance to use it. Change the password on the service account, and enter it in Configuration Manager for the second instance.
16.	Which permission gives a user the same level of permission as being the securable’s owner? CONTROL COMMAND EQUATE IMPERSONATE
17.	You are following the Microsoft recommendations for password policy inside SQL Server. Which of these is a valid password for the login Steve? MyF@v0riteHors3 M5Pas Steve4(5f3 Porsche
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? Log On As a Service Act As a Service Log On Locally Run SQL Server
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? RSA_512 RSA_1024 RSA_2048 DES
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? Disable Enforce Password Policy Enforce Password Expiration User Must Change Password at Next Login

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.