Review Questions

1.	Which of the following would ensure audit capability for physical access to your server? (Choose two.) A door secured by a key lock A door secured by a keypad lock Video monitoring of the data center A backup generator
2.	What type of SQL Server object is most useful in preventing SQL injection attacks? Stored procedure Function SELECT query INSERT/UPDATE/DELETE queries
3.	You are moving your Sales database from ServerA to ServerB. Inside the database are a series of DDL triggers to prevent tables and views from being altered. You want to be sure that these triggers are still enforced on the new server. What is the easiest way to do this? Move the database using a detach/attach procedure. Script out the triggers, back up and restore the database, and then run the script. Re-create the triggers from scratch after the database is moved. You cannot move DDL triggers.
4.	What are some things that you can do to prevent your database from being attacked through SQL injection? (Choose two.) Validate all input. Use stored procedures. Use dynamic SQL. Use triggers.
5.	You start a new job where all the developers have full access to the development server. It has been hard to track which objects are created and are part of each deployment. You decide to assign one developer to make all changes. What three things do you need to do? (Choose three.) Add the developer making changes to the db_ddladmin role. Add the developer making changes to the db_securityadmin role. Remove all developers not making changes from administrative roles. Change the administrative passwords.
6.	You are worried that your developers are not carefully tracking which stored procedures are being changed for the next deployment of your application. How can you ensure that you do not miss any altered stored procedures when creating your migration scripts? You can start Profiler every morning when you arrive and trace all the ALTER PROCEDURE events. You can send a reminder at the end of every day to all developers asking them to notify you of any procedures they changed that day. You can create a DDL trigger for the ALTER PROCEDURE event in the development database that logs the name of the procedure and the user who changed it. There is no way to track the procedures developers are altering.
7.	You notice that your SQL Server 2005 instance is not responding to Management Studio from your desktop. You want to connect to the server and determine the problem. What should you do? Log in locally to the server, and connect normally with Management Studio. Use sqlcmd from your workstation to connect. Wait for Management Studio to respond. Log in locally to the server and connect using the Dedicated Administrator Connection.
8.	You have been hired as the new DBA for a development team. The first day they tell you that their budget has been low and they can afford only one server. So, they have been making changes to the production database. What should you do? (There are two possible solutions; each of these is a complete solution.) There is nothing you can do if there is only one server. Create two additional databases on your server, one for development and one for QA. Lobby your boss to purchase at least one more server. Remove the developer’s rights to the production database, and make changes yourself to the production database.
9.	Your company is regulated by the federal government, which mandates that all of your backup tapes be encrypted. You determine that all of your database backups go directly to tape using the built-in SQL Server backup routines, which do not support encryption. What should you do? Do nothing if your company procedure is to use the SQL Server routines. Notify your boss that you must find a new backup routine that includes encryption. Rename your backup files so that they are not recognizable as database backups before they are stored on tape. Wait for SQL Server to add encryption to the backup routines.
10.	Your manager notifies you that today is the last day of employment for Kendall, the human resource director. What should you do? Disable Kendall’s SQL Server accounts immediately. Disable Kendall’s SQL Server accounts at the end of the day. Disable Kendall’s SQL Server accounts tomorrow. Do nothing.
11.	You notice that there seems to be an excessive amount of traffic on your SQL Server one day that is impacting performance. With the help of a network engineer, you trace the access to one of the internal workstations used by Jim. What should you do? (Choose two.) Have the network engineer disable traffic from Jim’s workstation to your SQL Server. Delete Jim’s account from the SQL Server. Shut down the SQL Server. Disable Jim’s account until you understand what is happening.
12.	You arrive at work one morning and read about a new virus that is attacking SQL Servers using a flaw in the driver code for linked servers. A few days later the linked server stops responding on one of your many servers and you suspect an infection. What should you do first? Check with Microsoft for a patch. Confirm the infection by researching the symptoms. Immediately delete your linked servers to prevent the spread of infection. Isolate the server by blocking all traffic to and from it until you can confirm the infection.
13.	Your company has implemented a strong physical security infrastructure for your database servers with logged access, video surveillance, and more. Your developers and DBAs, however, sit in an open cubicle environment with the marketing department because of a limited amount of space. What precautions can you implement to ensure security? (Choose all that apply.) Inspect all administrative workstations for key logging devices or software on a regular basis. Implement mandatory workstation locking when the computers are left unattended. Force all administrators to choose strong passwords and change them often. Ask for video cameras to be installed above the administrators’ workstations.
14.	You join a new company that is just setting up its network. The network engineer decides to put a firewall next to the Internet and a second firewall in front of the internal network. The web server and its supporting SQL Server database are planned for the space between the firewalls. Your network engineer plans on allowing all traffic from the internal network to the SQL Server through to prevent issues. How should you comment on this design? Agree with the design. Tell the engineer he’s an idiot and only traffic from your workstation should be allowed to the SQL Server. Ask the network engineer to move the SQL Server to the internal network and allow traffic only from the web server to the SQL Server. Ask the engineer to move the web server and the SQL Server to the internal network to better protect them.
15.	Which of the following changes should be controlled by your database control procedures? (Choose all that apply.) Schema changes in your databases Service packs Operating system patches Changes to assemblies hosted in the SQL CLR
16.	How should changes to your database flow through your environments? Development to production to QA for issues Development to QA to production Production to QA to development QA to production to development for issues
17.	What can you do to protect your SQL Server from worm and virus infections? (Choose all that apply.) Patch your server in a timely manner. Do not allow anyone to browse the Internet from the SQL Server. Ensure the SQL Server is in the DMZ. Install antivirus software on your server.
18.	One day a network engineer informs you that a tremendous amount of traffic is coming from one particular IP address. You also receive a call from the help desk that the server is not accessible. What should you do? Turn off the SQL Server to prevent it from being attacked. Have the network engineer turn off all traffic to the SQL Server. Have the network engineer disable traffic from the one particular IP address. Do nothing, and wait for the traffic to subside.
19.	One of your help desk people wants to build another environment to use in troubleshooting production issues. There is a spare workstation in the help desk person’s cubicle that has enough disk space to restore the production database, which the help desk department wants to do daily. What should you do? Refuse to allow this workstation to be used. Allow this and set up a job to restore the production database to this workstation. Allow this, but move the workstation into the secured data center. Ask that all help desk people agree to keep the workstation’s console locked.
20.	You want to be sure that no new logins are added to your production servers unless you personally add the logins. What is the best way to implement this? Use a database-level DDL trigger in the master database to roll back all CREATE_LOGIN events. Use a server-level DDL trigger to log all CREATE_LOGIN events. Use a server-level DDL trigger to roll back all CREATE_LOGIN events. There is no way to do this in SQL Server 2005.

Answers

1.	B, C. If a keypad lock uses separate codes for each individual and stores the date, time, and person inputting the code, then this provides an audit of physical access. Video monitoring provides an audit capability as well.
2.	A. Stored procedures are the most useful SQL Server object for preventing SQL injection attacks.
3.	A. Since DDL triggers on database-level structures, such as tables and views, are part of the database structure, they will move automatically when you move the database. A detach/ attach is the easiest way to accomplish this.
4.	A, B. Validating all input and using stored procedures will help to prevent SQL injection attacks. By validating input and using this input as a parameter in a stored procedure, you can prevent some SQL injection attacks from occurring.
5.	A, C, D. All three of these items are necessary to protect your server from unauthorized changes. By giving one developer the db_ddladmin role, you are allowing them to make schema changes. Removing the other developers from administrative roles and changing passwords ensures that only the one developer can make changes.
6.	C. The best way to capture all changes is to use a DDL trigger that fires whenever a developer executes an ALTER PROCEDURE statement. By logging the data from the statement, you can get a list of procedures that have been changed.
7.	D. The best way to determine what is happening on an unresponsive server is to use the Dedicated Administrator Connection. This requires a local login.
8.	B, C. Either B or C would be a better situation than the current one. Your first priority should be to separate the development and QA environments from the production environment to control changes and ensure stability.
9.	B. One of the important parts of analyzing your security situation is to identify deviations from regulatory requirements as well as company policy. You should bring this to the attention of management and get the current process changed.
10.	B. When you are informed that someone with SQL Server access is no longer working with your organization, you should disable their account as soon as possible. However, you should not unnecessarily impact their ability to do their job by removing access too soon. The prudent time to remove access is at the end of the day.
11.	A, D. Since we do not know what the cause of the traffic is, you should not remove Jim’s account. It is prudent to disable the account in case this is an internal attack and prevent traffic from causing performance problems on your server.
12.	D. If you suspect an infection, you should first isolate the server in order to prevent the spread of the virus.
13.	A, B, C, D. All of these actions will help ensure strong security for your database servers by protecting the administrators’ workstations, which often contain privileged connections to the servers.
14.	C. A web server between the internal and external firewalls makes sense, but a SQL Server used to support the web server should be further protected by being behind the internal firewall. Traffic to the SQL Server from outside the internal firewall should be limited to traffic from the web server.
15.	A, B, C, D. All of these changes should be controlled with procedures.
16.	B. Changes should first be applied to the development environment, then migrated to QA for testing, and finally deployed in production.
17.	A, B, D. All of these are good actions to do to prevent your server from being infected by a virus or worm. You should avoid having your SQL Server in a DMZ and instead put it behind a second firewall with limited access from the DMZ for specific machines.
18.	C. The best action you can take when you suspect a denial-of-service attack is to shut off network traffic from the suspected computers. This allows you to continue to service legitimate clients.
19.	C. As a DBA you should try to help others do their jobs better and make it easier where you can. Providing another environment for troubleshooting makes sense, but since this is a copy of your production data, it should be secured physically. This means preventing the physical drives from being removed. Locking the console is not a good solution for this, but moving the workstation into the secured data center is a solution.
20.	C. A server-level DDL trigger is needed to respond to CREATE_LOGIN events. To prevent the logins from being added, you should roll back the transaction in the trigger.