Module 7: Securing Microsoft SQL Server v
Lesson: SQL Server Role-Based Security
Use Enterprise Manager and expand to the logins and then to server roles. Show
the students the list of server 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.
Explain all of the roles that are part of the fixed database role.
Show the class how to add the user database role into a database.
Show the class how to add the application database role into a database.
Introduce the demonstration by explaining the Internet Information Services
(IIS) authentication modes for the TailspinToys and TailspinToysAdmin Web
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.
This topic describes the best practices for connecting to SQL Server. Emphasize
to the class that these best practices must be followed when developing Web
applications that connect to SQL Server.
The focus of this practice is connection strings. Ask students why a particular
connection string succeeded or failed.
Lesson: Securing SQL Server Communication
The focus for this topic is the communication channel between the Web server
and SQL Server.
You can optionally run the lab after this topic and then return to the final lesson
after the lab.
Lesson: Preventing SQL Injection Attacks
Explain the SQL injection attack and provide examples of SQL injection
attacks.
The focus of this instructor-led practice is to show how an SQL injection attack
is performed.
Explain the best practices that must be followed to protect the Web application
against SQL injection attacks.
Use the Code Example link that is given at the bottom of the slide to show an
example of using SQL parameters in ActiveX
®
Data Objects (ADO)–based
Web applications.
Fixed Server Roles
Database Roles
Fixed Database Roles
User Database Role
Application Database
Role
Demonstration: Adding
Roles and Logins to
SQL Server
Best Practices for
Connecting to
SQL Server
Instructor-Led Practice:
Connecting to
SQL Server
Overview of SQL
Connection APIs
Secure Communication
with SQL Server
Overview of SQL
In
j
ection Attacks
Instructor-Led Practice:
Using an SQL Injection
Attack to Gain Access to
a Web Application
Protected Against SQL
In
j
ection Attacks
Using SQL parameters
in ADO
vi Module 7: Securing Microsoft SQL Server
Use the Code Example link that is given at the bottom of the slide to show an
example of using SQL parameters in Microsoft ADO.NET–based Web
applications.
This demonstration shows how to secure a Web page against an SQL injection
attack. The Web page will be secured by using a parameterized SQL query
instead of an SQL query that is built using string concatenation.
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.
Using SQL parameters
in ADO.NET
Demonstration:
Securing a Web
Application Against an
SQL Injection Attack
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 <allow roles="London\TailspinAdmins"/> tag to be <allow
roles="computerName\TailspinAdmins"/>, 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 <appSettings> 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
******************************
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.
The code samples in this module are provided in both Microsoft
Visual Basic
®
.NET and C#.
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.
Introduction
Note
Objectives
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
******************************
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.
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.
Introduction
Lesson objectives
Module 7: Securing Microsoft SQL Server 3
SQL Client Identity
!
Which identity is used to communicate with SQL Server?
Users
Users Internet
Information
Services
Internet
Information
Services
SQL Server
SQL Server
Internet
Domain\User
SqlUser
Intranet
1
1
3
3
2
2
*****************************
ILLEGAL FOR NON
-
TRAINER USE
******************************
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.
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.
Introduction
T
y
pes of identities
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
******************************
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
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 can also authenticate users by using its own built-in security
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.
Introduction
Windows Authentication
SQL Server
Authentication
Module 7: Securing Microsoft SQL Server 5
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.
Authentication choices
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
******************************
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.
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.
The SQL Server and Windows security mode uses either 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.
Introduction
Windows only
SQL Server and
Windows
Không có nhận xét nào:
Đăng nhận xét