SQL Server security for the asp.net2.0 database

The installation of SQL Server has two options for Safe mode. The difference between them is which software performs the authentication process. Authentication is the process of confirming the identity of the user who will connect to SQL Server. Once the 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 fails. This process of authenticating user licenses is also known as authorization.

· Windows authentication (also known as trusted authentication or integrated security) performs authorization to the database using the identity of the Windows user who made the connection request process. In this case, the connection string does not have to provide an explicit user name and password. Asp. NET runs with a local user named "ASPNET" (or uses the username "network Service" in IIS 6.0), so when you use Windows Authentication, SQL checks to see if the user has permission to use the database. At this point, all asp.net applications run with the same user, so this safe mode treats these applications equally. Although you can run each application in a separate ASP.net process (a separate user runs each program), or you can simulate the Windows user identity of the browser client who made the connection request, the content is beyond the scope of this book. However, customer impersonation is the most common use of Windows authentication in Web applications.

· SQL authentication checks explicitly provided user names and passwords for users configured within SQL Server (no operating system is involved). In this case, each application running in the ASP.net process can connect to the database with a separate certificate, leaving the application reasonably separated (application A cannot connect to B's database without a user name and password for b). This is the most common authentication mode for Web applications deployed, especially in the case of shared hosting. A minor drawback is that the application retains the password for the user account used for the connection, and if the password is acquired by a malicious user, the security of the database is compromised. However, as you will see later in this book, ASP. NET provides a secure way to keep the SQL authentication password in an encrypted format in the Web.config file, thus reducing the risk of password acquisition.

· Mixed mode is a configuration of SQL Server that allows both Windows authentication and SQL authentication.

When installing SQL Server or SSE, select an authentication mode. In SQL Server, a wizard helps you choose between security steps, whereas in SSE the default selection is Windows Authenti cation. If you are installing SQL authentication, you must configure it explicitly. This article is using Windows authentication.

If you have installed SQL Server or SSE, you can open regedit to view the specified authentication mode (of course, you need to back up) to find the Hkey_local_machine/software/microsoft/microsoft SQL Server and search for LoginMode. A registry subkey with a value of 1 indicates Windows Auth entication, and a value of 2 indicates mixed authentication mode.

Table 3-1 summarizes the differences between these patterns.

Table 3-1

	WINDWOS Authentication	SQL Authentication
Replaceable names	Trusted Authentication Integrated security	No, but mixed Mode authen tication allows you to use Windows or SQL authentication
Typical environment	Internal network	Internet
Location of the user and authentication process list	Windows	SQL Server
SSE installation	Default installation	Need to specify installation
Connection string	Trusted_connection=true or Integrated Security=true	User=username; Password=password
Users of ASP.net Web applications	Asp. NET process, ASPNET (IIS 5.x), or Network Service (IIS 6)	SQL user
Advantage	better security; You can track the activities of users in SQL events and Windows events	Deploy on the host without creating a new account; independent of the operating system The host intranet site requires only general technology Provide applications with a more flexible way to connect each database with different certificates
Disadvantage	Giving a Web application Windows certificate might set the scope of permissions in the OS too large	The password is stored in the Web application (not in Windows authentication). Confirm that the password is saved in the Web.config file and is encrypted. Low-level operations that allow Web applications that use the SA certificate. Always create a new certificate for the ASP.net Web application and give only the required permissions