SQL Server LocalDB application in ASP. NET

I believe that the world will always develop in a better direction. This year's New Year concert in Vienna does not have any Star Command in previous years. However, by returning to the essence of Austria, it adopts a more traditional aggregation rule, let the artists of the past create a splendid generation from generation to generation. In the hands of new command, a deeper syllable is formed. I wish you a happy New Year.
Like a symphony, building a software system does not have to be driven by a powerful star. We stand in the ADO of history. NET on the shoulders, better back to the core development of SQL Server: SQL Server LocalDB in ASP.. NET.

Advantages of using SQL Server LocalDB:

Quickly deploy the complete SQL Server. In the future, projects can be seamlessly upgraded to the advanced version. It is a real SQL Server and directly uses all functions of SQL Server 2012. Free of charge. For startups and Low-configuration servers, you can save a lot of operating costs in the early stage.
Disadvantages and limitations:

You must have full control over the server and the user who rents the VM cannot use it (however, the price of a VPS is not much different from that of the VM ). The DLL cannot be placed in the bin folder for green deployment. the SQL Server Express LocalDB must be installed on the Server.

First, we must understand how to manage the database, in SQL Server 2012 management tools:



Use the (LocalDb) \ v11.0 string to connect to the LocalDB runtime environment of the current local machine.

If the. net framework is earlier than 4.0.2, use the named pipe to connect to LocalDB. For example: "Server = np: \. \ pipe \ LOCALDB # F365A78E \ tsql \ query"

This step has little to do with our development environment settings, but it will be of great help for future debugging errors.

Follow these two steps to run LocalDB in ASP. NET:

1: Locate database files

Use the connection string: connectionString = "Data Source = (LocalDb) \ v11.0; Initial Catalog = xxx; Integrated Security = SSPI; AttachDBFilename = | DataDirectory | \ test666.mdf ".





After attaching the database files generated by the system to SQL Server in the management tool, the program automatically creates a table named DBBases.

The preceding points solve the basic connection function. Visual Studio 2012 and SQL Server 2012 Management Studio are successfully debugged.

However, the problem is only solved in half. Note that the above two words are "vs2012" and "debugging". Currently, I have not said "run" in "IIS ".

2: User Permissions in IIS

Debug a project in visual studio using a windows Local User process with high permissions (generally the same as Administrator ).

To run a project in IIS, The ApplicationPoolIdentity process is used by default for windows7, 2008, 2008R2, and Server 2012.

ApplicationPoolIdentity process permissions are not explained in this article. Here you only need to understand it as a user process with very low permissions (IIS_IUSRS group. Even if LocalDB is a simplified version, it is also an SQL Server. In the most extreme situation, it involves two steps: starting the sqlserver.exe process and creating a database, not the ApplicationPoolIdentity process (IIS_IUSRS group.

Solution

1: Application pool-Advanced Settings-ID, run as localsystem account. The Localsystem process is equivalent to the local administrator.



This solution is the easiest. You can run the process directly through the localSystem account, and all the troubles instantly become useless. However, the negative factor is that it brings potential security threats: If a malicious code is uploaded to a malicious client, once the malicious code gets a running opportunity, so it will run on the server with the administrator permission. What does this mean? Needless to say.

2: AttachDBFile is used to mount the database file to a later SQL Server version.

LocalDB is a real SQL Server and can be seamlessly compatible with other SQL Server versions. We only need to link the database files to Express or a later version of SQL Server,

You only need to change "Data Source = (LocalDb) \ v11.0;" to "Data Source =. \ SQLExpress" to solve all the troubles. Although this practice has practical significance, it has little to do with the topic of this article and is not described here.

Finally, security-based operation suggestions:

1: run the entire program directly using localsystem. As long as the client is not allowed to upload files, the entire program can run safely. However, in most cases, a meaningful web program allows the client to upload files. Therefore, we can list a solution for uploading files:

When a user uploads a file, the file is placed in another process space. During the operation, the file is uploaded through the external link (upload.abc.com, to make user files run in absolutely secure processes.



2: In contrast to recommendation 1, Code related to database operations is encapsulated as a service and runs in another security process (local connection only) through the self-host function of WCF or Web API ), public-oriented Web programs can be called through local service interfaces to minimize all security factors. (But the development process and maintenance will increase complexity)