SQL Server security of ASP.NET2.0 database

Author：Eve Cole Update Time：2009-06-30 16:43:56

There are two options for safe mode when installing SQL Server. The difference between them is which software performs the authentication process. Authentication is the process of confirming the identity of a user who is connecting to SQL Server. Once authentication is performed, SQL Server can verify that the user has permission to connect to a requested resource, such as a database. If the user has permission to connect to the database, SQL Server will allow the connection request to succeed, otherwise, the connection will fail. This process of verifying user permission is also called authorization.

· Windows Authentication (also known as Trusted Authentication or Integrated Security) uses the identity of the Windows user making the connection request to perform authorization to the database. In this case, the connection string does not have to provide an explicit username and password. ASP.NET runs as a local user named "ASPNET" (or "Network Service" in IIS 6.0), so when using Windows Authentication, SQL will check whether this user has permission to use the database. At this point, all ASP.NET applications are running with this same user, so the safe mode treats them equally. Although it is possible to run each application in a separate ASP.NET process (a separate user running each application), or to impersonate the Windows user identity of the browser client making the connection request, these are beyond the scope of this book. range. However, client impersonation is the most common use of Windows Authentication in web applications.

· SQL Authentication checks explicitly supplied usernames and passwords against users configured within SQL Server (without involving the operating system). In this case, each application running in the ASP.NET process can connect to the database with a separate certificate, thus reasonably isolating the applications (Application A will not be able to connect to the database if it does not have B's username and password). Cannot connect to B's database). This is the most common authentication mode used for deployed web applications, especially in the case of shared hosting. A small disadvantage is that it requires the application to retain the password of the user account used to connect, and if this password is obtained by a malicious user, the security of the database will be compromised. However, as you will see later in this book, ASP.NET provides a secure way to store the SQL Authentication password in an encrypted format in the Web.config file, thus reducing the risk of the password being obtained.

· Mixed Mode is a configuration of SQL Server that allows both Windows Authentication and SQL Authentication.

When installing SQL Server or SSE, you must choose an authentication mode. In SQL Server, there is a wizard to help you choose during the security steps, while in SSE, the default choice is Windows Authentication. If you want to install SQL Authentication, you must configure it explicitly. This article uses Windows Authentication.

If you have installed SQL Server or SSE, you can view the specified authentication mode by opening RegEdit (of course you need to back it up first), find HKey_Local_Machine/Software/Microsoft/Microsoft SQL Server and search for LoginMode. A registration subkey with a value of 1 represents Windows Authentication, while a value of 2 represents Mixed Authentication mode.

Table 3-1 summarizes the differences between these modes.

Table 3-1

Location of list of

	Windows Authentication	SQL Authentication
replaceable name	Trusted Authentication Integrated Security	No, but Mixed Mode Authentication allows the use of Windows or SQL Authentication.
Typical environment.	Intranet.	Internet
users and authentication processes.	Windows	SQL Server
SSE installation.	Default installation	requires specifying the installation
connection string	Trusted_connection=true or Integrated Security=true	user=username; password=password
ASP.NET Web application users	ASP.NET process, ASPNET (IIS 5.x) or Network Service (IIS 6)	SQL users
have the advantage	of better security; can control the user's SQL events and Windows events. Activity tracking	can be deployed on the host machine without creating a new account; independent of the operating system Hosted intranet site requires only moderate skills Provides a more flexible way for applications to connect to each database with different certificates
Disadvantages	Giving a Windows certificate to a web application has the potential to set the scope of permissions in the OS too large	Passwords are stored in the web application (which is not the case in Windows authentication ). Confirm that the password is saved in the Web.config file and is encrypted. Allows low-level operations for web applications using sa certificates. Always create new certificates for ASP.NET web applications and give only the required permissions

Now that we know the safe way to use SQL, let's consider how the data consumer (DataSource control) will meet the requirements. First, use data obtained from VWD and VWD Web Server (Cassini), mainly during design and testing. Second, the data should be accessed from IIS after deployment. The two data consumers have different usernames. VWD and VWD Web Server use the name of the person logged into Windows, while the IIS program uses the name ASPNET.

If SQL Server uses Windows authentication, the SqlDataSource control needs to include the following code in the connection string: Integrated Security=true (or Trusted_connection=true). This parameter will instruct SQL Server to authenticate data requests based on the requester's Windows login account. If you are logged in as the user who installed SSE, their certificate will grant access to SSE. Everything will go smoothly using VWD and VWD Web Server, because users of VW Web Server are considered to be programmers logged into Windows and therefore have accounts on SSE. However, even if the application works fine outside of VWD, it will not work properly when the site is migrated to IIS. IIS runs under a user account named ASPNET (or Network Service in IIS6/Windows 2003 Server). Therefore, the administrator of the machine running IIS must add the ASP.NET user and grant its permissions. This process is beyond the scope of this book, but is described in detail in many IIS administrator manuals. In summary, if SQL Server uses Windows authentication, you can use VWD and VWD Web Server to perform the exercises in this book. Your page can run on IIS only after granting permission to the ASP.NET process account that accesses the database.

If SQL Server is using SQL authentication, SQL will conduct its own authentication process. This process will not rely on the Windows user list. The connection string contains two parameters: user=username, password=password. The page can now be used from VWD, VWD Web Server or IIS, as there is no need to create a user account in Windows. However, we also need to use an account in SQL Server. The only default account is sa. Before deployment, another account should be created in SQL Server that only has permission to execute ASPX pages. Failure to create an alternative account other than the sa (and password to protect the sa) leaves the site open to one of the most well-known and easily exploitable security vulnerabilities. Any hacker knows to log in using userID='sa' with an empty password.

For both authentication modes, when using the connection string described previously, the user will log in to SQL Server with the initial account. This account is sa, which means system administrator. As can be seen from the name, it has all permissions on all objects. In the current version of SQL Server, the service cannot be installed with sa with a NULL password. In SSE, it must be installed with the parameter SAPWD="MyStrongPassword". A strong password here means at least not NULL. It's best to use at least seven characters and be sure to use a mix of letters, numbers and symbols. In most cases outside of student exercises, an account will need to be assigned to each database and application. Giving one application permission to access data from other applications should be avoided.