Module 7: Securing

Chia sẻ: Mai Phuong | Ngày: | Loại File: PDF | Số trang:68

Thêm vào BST

Báo xấu

119
lượt xem 8
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

This module provides students with an explanation of how to use Microsoft® SQL Server security features to protect Web application data. After completing this module, students will be able to connect securely to a SQL Server database, and use the SQL Server security model to protect a Web application against SQL injection attacks.

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Module 7: Securing

Module 7: Securing Microsoft SQL Server Contents Overview 1 Lesson: SQL Server Connections and Security 2 Lesson: SQL Server Role-Based Security 15 Lesson: Securing SQL Server Communication 32 Lesson: Preventing SQL Injection Attacks 36 Review 46 Lab 7: Securing Microsoft SQL Server Data 48
Information in this document, including URL and other Internet Web site references, is subject to change without notice. Unless otherwise noted, the example companies, organizations, products, domain names, e-mail addresses, logos, people, places, and events depicted herein are fictitious, and no association with any real company, organization, product, domain name, e-mail address, logo, person, place or event is intended or should be inferred. Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means (electronic, mechanical, photocopying, recording, or otherwise), or for any purpose, without the express written permission of Microsoft Corporation. Microsoft may have patents, patent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrights, or other intellectual property.  2002 Microsoft Corporation. All rights reserved. Microsoft, MS-DOS, Windows, Windows NT, ActiveX, Active Directory, Authenticode, Hotmail, JScript, Microsoft Press, MSDN, PowerPoint, Visual Basic, Visual C++, Visual Studio, and Windows Media are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries. The names of actual companies and products mentioned herein may be the trademarks of their respective owners.
Module 7: Securing Microsoft SQL Server iii Instructor Notes Presentation: This module provides students with an explanation of how to use Microsoft® 75 minutes SQL Server™ security features to protect Web application data. After completing this module, students will be able to connect securely to a Lab: SQL Server database, and use the SQL Server security model to protect a Web 30 minutes application against SQL injection attacks. After completing this module, students will be able to: ! Use Microsoft Windows® Authentication or SQL Server Authentication to authenticate SQL Server connections. ! Create different types of SQL Server roles and assign members to those roles. ! Secure SQL Server communication channels by using connection application programming interfaces (APIs). ! Secure SQL Server against SQL injection attacks. Required materials To teach this module, you need the following materials: ! Microsoft PowerPoint® file 2300A_07.ppt ! Hypertext Markup Language (HTML) code sample file 2300A_07_code.htm Preparation tasks To prepare for this module: ! Read all of the materials for this module. ! Complete the practices and lab. ! Read Chapter 6, “SQL Server Security Overview,” in Designing Secure Web-Based Applications for Microsoft Windows 2000 by Michael Howard (Redmond, Microsoft Press®), 2000. ! Read Module 1, “SQL Server Overview,” in Course 2072, Administering a Microsoft SQL Server 2000 Database. ! Visit the Microsoft Security page at http://www.microsoft.com/security/ security_bulletins/ms02020_sql.asp.
iv Module 7: Securing Microsoft SQL Server How to Teach This Module This section contains information that will help you to teach this module. Lesson: SQL Server Connections and Security This section describes the instructional methods for teaching each topic in this lesson. SQL Client Identity Explain the two types of identities that can be used to connect to SQL Server. Inform the class that this topic provides the overview of the two identities, Windows user and SQL Server user. Do not cover the identities in detail with this topic. The rest of module explains the two identities in detail and provides the advantages and disadvantage of both of the identities. Overview of Explain the two authentication methods that are used to authenticate the client Authentication in based on its identity. For the Windows user identity, Windows Authentication SQL Server is used, and for the SQL User Identity, SQL Server Authentication is used. Compare the two authentication choices and emphasize that Windows Authentication is generally preferred because it offers more security services. Also, explain the situations in which using SQL Server Authentication is more useful. Configuring the Explain the two security modes that are available in SQL Server: Windows only SQL Server Security and SQL Server and Windows. Inform students that the Windows only and Mode SQL Server and Windows security modes are also known as integrated security mode and mixed security mode, respectively. When explaining the security modes, use Enterprise Manager on the London computer to show the security mode setting that is on the Glasgow computer. Security Configuration Explain how SQL Server security configuration affects database connection and Connection Pooling pooling. Also, explain how students can improve the database connection pooling by limiting the user identities that are presented to SQL Server. Show the Microsoft MSDN® documentation for the SqlConnection class to show all of the possible connection string values. Connecting to Explain the connection string values that specify the type of authentication that SQL Server will be used to authenticate clients. SQL Server Logins Explain the two logins that exist in SQL Server by default: sa and the Local administrators group. Emphasize to the class that these two logins have access to all of the databases in the SQL Server instance. Therefore, these logins are not good choices for Web application logins. Use Enterprise Manager to show the default logins to the students. SQL Server Permissions Explain the two categories of SQL Server permissions: object and statement. Also, inform students about the permissions that can be granted, denied, or revoked under each category.
Module 7: Securing Microsoft SQL Server v Lesson: SQL Server Role-Based Security Fixed Server Roles Use Enterprise Manager and expand to the logins and then to server roles. Show the students the list of server roles. Database Roles Inform students about the three types of database roles that exist on each database in SQL Server. The following three topics provide details of the three types of database roles; therefore, do not go into details with this topic. Fixed Database Roles Explain all of the roles that are part of the fixed database role. User Database Role Show the class how to add the user database role into a database. Application Database Show the class how to add the application database role into a database. Role Demonstration: Adding Introduce the demonstration by explaining the Internet Information Services Roles and Logins to (IIS) authentication modes for the TailspinToys and TailspinToysAdmin Web SQL Server applications. Ask the students how each Web application should connect to SQL Server. Use these answers to further ask what configuration changes the students would make to SQL Server to accomplish configuration. Best Practices for This topic describes the best practices for connecting to SQL Server. Emphasize Connecting to to the class that these best practices must be followed when developing Web SQL Server applications that connect to SQL Server. Instructor-Led Practice: The focus of this practice is connection strings. Ask students why a particular Connecting to connection string succeeded or failed. SQL Server Lesson: Securing SQL Server Communication Overview of SQL The focus for this topic is the communication channel between the Web server Connection APIs and SQL Server. Secure Communication You can optionally run the lab after this topic and then return to the final lesson with SQL Server after the lab. Lesson: Preventing SQL Injection Attacks Overview of SQL Explain the SQL injection attack and provide examples of SQL injection Injection Attacks attacks. Instructor-Led Practice: The focus of this instructor-led practice is to show how an SQL injection attack Using an SQL Injection is performed. Attack to Gain Access to a Web Application Protected Against SQL Explain the best practices that must be followed to protect the Web application Injection Attacks against SQL injection attacks. Using SQL parameters Use the Code Example link that is given at the bottom of the slide to show an in ADO example of using SQL parameters in ActiveX® Data Objects (ADO)–based Web applications.
vi Module 7: Securing Microsoft SQL Server Using SQL parameters Use the Code Example link that is given at the bottom of the slide to show an in ADO.NET example of using SQL parameters in Microsoft ADO.NET–based Web applications. Demonstration: This demonstration shows how to secure a Web page against an SQL injection Securing a Web attack. The Web page will be secured by using a parameterized SQL query Application Against an instead of an SQL query that is built using string concatenation. SQL Injection Attack Reiterate that using parameters prevents the form field values from being executable SQL. Lab 7: Securing Microsoft SQL Server Data The Web pages in both the TailspinToys and TailspinToysAdmin Web applications must read data from the TailspinToys SQL Server database. In this lab, students will create connection strings to connect to SQL Server and then call utility functions to read data from the SQL Server database.
Module 7: Securing Microsoft SQL Server vii Customization Information This section identifies the lab setup requirements for a module and the configuration changes that occur on student computers during the labs. This information is provided to assist you in replicating or customizing Microsoft Official Curriculum (MOC) courseware. Lab Setup To complete this lab, you can continue working in the Tailspin Toys Microsoft Visual Studio® .NET projects that you have already created, or you can start with new files. If you want to start with new files, you must copy the appropriate starter projects to the lab virtual root directories. There are separate starter projects for the Active Server Pages (ASP) and the Microsoft ASP.NET exercises. ! Create the Web applications for the ASP exercises 1. Copy all of the contents of the ASP starter folder install_folder\Labfiles\ Lab07\ASP\Starter\TailspinToys to the TailspinToys IIS virtual directory at C:\Inetpub\wwwroot\TailspinToys. 2. Copy all of the contents of the ASP starter folder install_folder\Labfiles\ Lab07\ASP\Starter\TailspinToysAdmin to the TailspinToysAdmin IIS virtual directory at C:\Inetpub\wwwroot\TailspinToysAdmin. ! Create the Web applications for the ASP.NET exercises 1. Copy all of the contents of the ASP.NET starter folder install_folder\ Labfiles\Lab07\ASPXVB\Starter\TailspinToys.NET to the TailspinToys.NET IIS virtual directory at C:\Inetpub\wwwroot\ TailspinToys.NET. 2. Copy all of the contents of the ASP.NET starter folder install_folder\ Labfiles\Lab07\ASPXVB\Starter\TailspinToysAdmin.NET to the TailspinToysAdmin.NET IIS virtual directory at C:\Inetpub\wwwroot\ TailspinToysAdmin.NET. 3. Edit the file c:\Inetpub\wwwroot\TailspinToysAdmin.NET\Web.config and change the tag to be , where computerName is the name of your computer.
viii Module 7: Securing Microsoft SQL Server ! Configure IIS authentication 1. Run the IIS administrative tool. 2. Expand the computer node and the Default Web Site node in the tree. 3. Right-click the TailspinToysAdmin virtual directory, and click Properties. 4. Click Directory Security. 5. In the Anonymous access and authentication control group, click Edit. 6. Clear the Anonymous access check box. 7. Click OK twice to save your changes. 8. Right-click the TailspinToysAdmin.NET virtual directory, and click Properties. 9. Click Directory Security. 10. In the Anonymous access and authentication control group, click Edit. 11. Clear the Anonymous access check box. 12. Click OK twice to save your changes. ! Configure SQL Server (instructors only) • You must perform the “Adding Roles and Logins to SQL Server” demonstration in Module 7, “Securing Microsoft SQL Server,” in Course 2300, Developing Secure Web Applications for this lab to function properly. Lab Results Performing the lab in this module introduces the following configuration change: The element of the Web.config file for TailspinToys.NET and TailspinToysAdmin.NET will be modified to use Integrated Windows authentication.
Module 7: Securing Microsoft SQL Server 1 Overview ! SQL Server Connections and Security ! SQL Server Role-Based Security ! Securing SQL Server Communication ! Preventing SQL Injection Attacks *****************************ILLEGAL FOR NON-TRAINER USE****************************** Introduction In Web applications, a database is an important component that must be secured against attack. Understanding the security features of Microsoft® SQL Server and how to use them effectively is critical for developing secure Web applications. In this module, you will learn how to use SQL Server security features to protect Web application data. Note The code samples in this module are provided in both Microsoft Visual Basic® .NET and C#. Objectives After completing this module, you will be able to: ! Use Microsoft Windows® Authentication or SQL Server Authentication to authenticate SQL Server connections. ! Create different types of SQL Server roles and assign members to those roles. ! Secure SQL Server communication channels by using connection application programming interfaces (APIs). ! Secure SQL Server against SQL injection attacks.
2 Module 7: Securing Microsoft SQL Server Lesson: SQL Server Connections and Security ! SQL Client Identity ! Overview of Authentication in SQL Server ! Configuring the SQL Server Security Mode ! Security Configuration and Connection Pooling ! Connecting to SQL Server ! SQL Server Logins ! SQL Server Permissions *****************************ILLEGAL FOR NON-TRAINER USE****************************** Introduction SQL Server provides several security features that can be used to secure Web application data. SQL Server also integrates with Windows operating systems and makes use of the secure Windows authentication services. To secure access to SQL Server, the administrator can create user logins, configure login permissions, and assign roles to the user logins. The permissions and roles determine which actions users can perform, in addition to what kind of data the users can access. The primary goal in managing SQL Server security is to restrict database permissions so that users are less likely to execute harmful commands and procedures. Lesson objectives After completing this lesson, you will be able to: ! Explain the SQL Server authentication modes and identify when to use each mode. ! Configure SQL Server for authentication modes. ! Select the appropriate connection method for accessing SQL Server. ! Define the SQL Server permission types and identify when to use each type of permission. ! Create logins to allow users to connect to SQL Server. ! Assign permission to the users.
Module 7: Securing Microsoft SQL Server 3 SQL Client Identity ! Which identity is used to communicate with SQL Server? Intranet Domain\User SqlUser Internet Users Users Internet Internet SQL Server SQL Server Information Information 1 2 2 3 Services *****************************ILLEGAL FOR NON-TRAINER USE****************************** Introduction The context of the client application defines the user identity that is presented to a SQL Server for authentication. In Web applications, the identity of the Internet Information Services (IIS) request determines the identity that is presented to SQL Server for authentication. Types of identities Client applications can connect to SQL Server using one of two types of identities: ! Windows user identity SQL Server identifies and authenticates users based on their Windows user identities. With the Windows user identity, the resources within SQL Server are secured for Windows users and groups. Using Windows user identity for authentication is most often used for an intranet Web application where users are already members of a Windows domain. ! SQL Server user identity SQL Server maintains its own user database, which is then used to identify the valid user. When the client application connects to SQL Server by using the SQL Server user identity, SQL Server validates that user identity by using the information that is stored within SQL Server itself. With the SQL Server user identity, the resources within SQL Server are secured by using SQL Server users and roles. Using SQL Server identity for authentication is not as secure as Windows user identity, but it may be required for some Internet Web applications.
4 Module 7: Securing Microsoft SQL Server Overview of Authentication in SQL Server ! Windows Authentication " Windows users and groups " Rich and secure service " Proxy servers pose problems ! SQL Server Authentication " Users’ login information is stored in SQL Server " Fewer security features " Useful in Internet scenarios ! Windows Authentication is the preferred choice because it is more secure than SQL Server Authentication *****************************ILLEGAL FOR NON-TRAINER USE****************************** Introduction When client applications connect to SQL Server, the user identity of the client application must be authenticated. SQL Server supports two different authentication systems: ! Windows Authentication ! SQL Server Authentication Windows Authentication SQL Server can authenticate users by calling the Windows security services. Windows Authentication requires trusted connections between the client application and SQL Server. These trusted connections use the impersonation features of Windows security services to present the user identity of the client application to the SQL Server service for authentication. With Windows Authentication, SQL Server uses the user and group accounts that are available in a Windows domain for authentication. Windows users do not require a separate SQL Server login and password. The benefit of using Windows Authentication is that it allows SQL Server to use the abundant and secure sets of services that are provided by Windows operating systems for authentication. With Windows Authentication, there is support for aging passwords, enforcing strong password schemes, and enforcing account lockout after repeated failed login attempts. SQL Server SQL Server can also authenticate users by using its own built-in security Authentication services. With SQL Server Authentication, the users and roles reside within the master SQL Server database, and SQL Server authenticates the calling user. SQL Server Authentication has the advantage of not being limited by proxy servers over the Internet; however, it has fewer security features than Windows security.
Module 7: Securing Microsoft SQL Server 5 Authentication choices Windows Authentication is the preferred authentication choice because it is more secure than SQL Server Authentication. However, it is not always possible to use Windows Authentication in Internet Web applications. Proxy servers can prevent identity flow when your database needs to authenticate the user through Windows Authentication over the Internet. A proxy server executes Hypertext Transfer Protocol (HTTP) requests on behalf of client browser users. Proxy servers may execute these requests without the security context of the client browser, so the identity of the user will not flow to the Web application (and subsequently, the identity of the user will not flow to SQL Server). Rather, the identity of the proxy server will flow to SQL Server, and in the case of Windows Authentication, this is most often not a Windows user identity. Some proxy servers can be configured to flow Windows identities, but this is not supported by all proxy servers. In such cases, SQL Server Authentication is useful, and is recommended.
6 Module 7: Securing Microsoft SQL Server Configuring the SQL Server Security Mode ! Windows only " Uses Windows Authentication " SQL Server objects are secured using operating system users and groups " Also called integrated security mode ! SQL Server and Windows " Uses either Windows Authentication or SQL Server Authentication " Client specifies authentication mode when connecting " Also called mixed security mode *****************************ILLEGAL FOR NON-TRAINER USE****************************** Introduction SQL Server can be configured to support one of two security modes: ! Windows only ! SQL Server and Windows These security mode settings are set for a SQL Server instance and therefore affect all of the databases in a SQL Server instance. When in doubt, select the SQL Server and Windows security mode for the broadest configuration option. Windows only The Windows only security mode in SQL Server exclusively uses Windows Authentication for client connections. All SQL Server users must be Windows users. The users can also be members of Windows groups. The Windows only security mode is also called the integrated security mode. SQL Server and The SQL Server and Windows security mode uses either Windows Windows Authentication or SQL Server Authentication. In the SQL Server and Windows security mode, the client application specifies the authentication mode to use when the user is first connected to SQL Server. The SQL Server and Windows security mode is also called the mixed security mode.
Module 7: Securing Microsoft SQL Server 7 Setting the security The SQL Server security mode can be configured by using Enterprise Manager. mode To set the SQL Server security mode: 1. Click Start, point to Programs, point to Microsoft SQL Server, and then click Enterprise Manager. This opens the SQL Server Enterprise Manager. 2. Expand the tree to locate the instance of SQL Server. The instance of SQL Server is often named (local). 3. Right-click the instance of SQL Server, and then click Properties. 4. In the SQL Server Properties dialog box, click the Security tab, as shown in the following illustration. 5. Under Authentication, click the appropriate option. Click the Windows only option for implementing Windows Authentication. Otherwise, click the SQL Server and Windows option to allow logins from both types of authentication: Windows Authentication and SQL Server Authentication. 6. Click OK to save your changes.
8 Module 7: Securing Microsoft SQL Server Security Configuration and Connection Pooling ! Connection pooling requires all the connections to have the same identity ! If the Web application uses Windows only authentication and impersonates the identity of the client browser: " Many identities connect to SQL Server " Each identity has its own connection pool ! Limiting user identities requires Web applications to not impersonate the client browser identity *****************************ILLEGAL FOR NON-TRAINER USE****************************** Introduction Connection pooling is useful for scaling up the concurrent database connections of a Web application. Database connection pooling enables a Web application to use any unused connection from a pool of connections that do not need to be reestablished for each use. The main benefit of connection pooling is improved performance, which is not achieved when you use authenticated connections that have multiple user identities that are associated with them. Connection pooling requires that all connections in the pool to have the same identity associated with them. How does SQL Server The SQL Server security configuration can have an impact on connection security affect pooling. Connections are pooled per user. If your Web application is using connection pooling? Windows only security and is impersonating the identity of the client browser to authenticate a connection to SQL Server, there may be many user identities connecting to the SQL Server server. Each of these user identities will have its own connection pool; and will not be in the same connection pool. How to improve To limit the user identities that are presented to SQL Server, thereby improving connection pooling? connection pooling, you can have a single identity communicate with SQL Server. To have a single identity, the Web application should not impersonate the client browser identity. You can ensure that the Web application does not impersonate the client browser identity by using either of the security modes: Windows only security or SQL Server and Windows security. In Windows only security, the identity of the Web application process is presented to SQL Server. In SQL Server and Windows security, a single SQL Server user can be presented to SQL Server as part of the connection string.
Module 7: Securing Microsoft SQL Server 9 Connecting to SQL Server ! Connection string specifies which type of authentication to be used " Values are same for ADO and ADO.NET ! Windows authentication " Connection string contains Trusted_Connection=yes ! SQL Server authentication " Connection string contains uid=username;pwd=password ! Requested mode must match SQL Server configuration ! Store connection strings in a configuration file " .inc for ASP Web applications and Web.config for ASP.NET Web applications *****************************ILLEGAL FOR NON-TRAINER USE****************************** Introduction When a client application connects to SQL Server, the connection string specifies which type of authentication the client application needs to use. The value of the connection string is the same for ActiveX® Data Objects (ADO) and Microsoft ADO.NET. Connection string For Windows Authentication, include Trusted_Connection=yes in the values connection string to specify that the identity of the client application should be used to authenticate the connection with SQL Server: server=computerName;Trusted_Connection=yes;! database=databaseName For SQL Server Authentication, include uid=username;pwd=password in the connection string and specify the required SQL Server login and password. The SQL Server login that is contained in the connection string is used to authenticate the connection with SQL Server: server=computerName;uid=userName;pwd=password;! database=databaseName Note If you specify uid and pwd in clear text in the connection string, you might want to encrypt the connection string. To learn about encryption, see Module 9, “Encrypting, Hashing, and Signing Data,” in Course 2300, Developing Secure Web Applications.
10 Module 7: Securing Microsoft SQL Server Connection strings should be stored in a separate file from the Web page content to better secure the connection string content. For Active Server Pages (ASP) Web applications, connection strings can be stored in .inc files. For Microsoft ASP.NET Web applications, connection strings can be stored in a Web.config file. Client and server must The authentication type that is specified in the connection string must match the match configured security settings for the SQL Server instance. If the client specifies SQL Server Authentication in the connection, but the SQL Server instance is configured for Windows only security, the connection will be refused.
Module 7: Securing Microsoft SQL Server 11 SQL Server Logins ! Add logins to permit user connections to SQL Server " Default logins are BUILTIN\Administrators and sa *****************************ILLEGAL FOR NON-TRAINER USE****************************** Introduction Logins must be added to SQL Server to permit particular users to connect to SQL Server. Logins must be added to SQL Server for both integrated and mixed mode security. For Windows only security, Windows users and groups may be added to the SQL Server logins. For SQL Server and Windows security, SQL Server users are added to the list of logins. These logins are added to an instance of SQL Server and not to individual databases. However, you can restrict the list of databases that a login can access. You can also restrict the role membership for a login, both within a database and at the server level. Default logins in SQL By default, there are two logins for an instance of SQL Server: Server ! sa The sa login is a SQL Server user that can be used for the SQL Server and Windows security connections. ! Local administrators group (BUILTIN\Administrators) The BUILTIN\Administrators login is a Windows 2000 group and can be used for Windows only security connections. Both of these logins are in the System Administrators and db_owner roles. These logins have access to all of the databases in the SQL Server instance. These logins are intended to be used to administer the SQL Server instance and SQL Server databases. These logins are not good choices for Web application logins because they have so much control over the SQL Server databases.
12 Module 7: Securing Microsoft SQL Server Adding a new login To add a Windows 2000 user or group to SQL Server: 1. Open SQL Server Enterprise Manager. 2. Expand the tree to locate the instance of SQL Server. The instance is often named (local). 3. Expand the instance of SQL Server. 4. Expand the Security folder. 5. Right-click Logins, and then click New Login. 6. In the SQL Server Login Properties dialog box, do the following: a. Select Windows Authentication. b. Under Authentication, in the Domain box, click the appropriate domain or computer name. c. In the Name text box, type the Windows 2000 user or group name. d. On the Server Roles tab, check the appropriate server roles for the new login. e. On the Database Access tab, check the databases that the new login will have access to. f. Click OK to save the new login.