SQL Server LocalDB in ASP. NET Application Introduction _ Practical Skills

I believe that the world will always develop in a better direction, this year's Vienna New Year concert does not have the star-level command of previous years, but by returning to the nature of Austria, with more traditional rules of aggregation, artists of the past generation of brilliant, in the hands of the new command, burst into a more profound syllable. Here, I wish you all a happy new year.
As with symphonies, the construction of software systems does not necessarily have to be driven by a strong star, we stand on the shoulders of successive ado.net, and better return to the core development of SQL Server: SQL Server LocalDB in ASP. NET in the application.

advantages of using SQL Server LocalDB :

Quickly deploy the full SQL Server. Later, the project can be upgraded seamlessly to the advanced version. It is a real SQL Server that uses all of the features of SQL Server 2012 directly. Free, for start-ups and low configuration servers, you can save a lot of early operating costs.
Disadvantages and Limitations：

You must have full control over the server, and users who hire a virtual host cannot use it (but the price difference between a VPS and a virtual host is not significant). The server must have SQL Server Express LocalDB installed because the DLL cannot be placed in a green deployment through the Bin folder.

First we have to understand how to manage the database, in SQL Server 2012 Administration Tools:



Use the (LocalDb) \v11.0 string to connect to the current native LOCALDB runtime environment.

The. NET Framework connects LocalDB directly using named pipes, such as "Server=np:\\.\pipe\localdb#f365a78e\tsql\query", earlier than 4.0.2.

This step has little to do with our development environment settings, but it is helpful for future debugging errors.

The following two steps are set to run LocalDB in asp.net:

1: Resolve database file positioning

Use connection string: connectionstring= "Data source= (LocalDb) \v11.0;" Initial catalog=xxx;integrated Security=sspi; attachdbfilename=| Datadirectory|\test666.mdf ".





We have the system-generated database files attached to SQL Server in the Admin tool, and we see that the program automatically creates a table named Dbbases

The above addresses basic connectivity features, which are debugged by Visual Studio 2012 with SQL Server Management Studio.

However, the problem is only half solved, notice above I use "vs2012", "debug" These two words, I have not said in IIS, "run".

user rights issues in 2:iis

debugging projects in Visual Studio uses the Windows local user process, which has relatively high permissions (typically the same as the administrator).

To actually run the project in IIS, executing the program Windows7, 2008, 2008R2, Server 2012 uses the applicationpoolidentity process by default.

The permissions of the applicationpoolidentity process are not too much explained in this article, so you can just interpret it as a very low user process (Iis_iusrs group). Even if LocalDB is a simplified version, it is also SQL Server, in the most extreme case, need to go through the "open Sqlserver.exe process", "CREATE DATABASE" two steps, not the applicationpoolidentity process (IIS _iusrs group) Do what you want to do.

Solutions

1: Application Pool – Advanced settings – logo, run with LocalSystem account. The LocalSystem process equates to the local administrator.



This solution is the simplest, directly through the LocalSystem account to run the process, all the troubles instantly vanished. But the downside is the potential security threat: If an unwanted client uploads a malicious code, then if the malicious code gets a chance to run, it will run on the server with administrator privileges, which would mean nothing more.

2: Through Attachdbfile, hook up the database file to a higher version of SQL Server to solve the problem.

LOCALDB is a real SQL Server that can be seamlessly compatible with other versions of SQL Server, and we simply hook up the database files to express or later SQL Server.

Just need to put: "Data source= (LocalDb) \v11.0;" Modified to: "Data source=.\sqlexpress", can also solve all the trouble. Although this practice has practical significance, but it is not related to the subject of this article, this is not much described.

Finally, the operational recommendations based on security factors：

1: Direct use of LocalSystem run the entire program, as long as the client does not allow uploading files, the entire program can be assured to run. But in most cases a meaningful web application is a way to allow the client to upload files, so list a solution for uploading files:

When the user uploads the file, puts the file into the other process space, runs, through the outside chain (upload.abc.com) file's method, has achieved lets the user file run in the absolute security process.



2: In contrast to recommendation 1, code that involves database operations is encapsulated as a service, running in another security process (local connectivity only) through a WCF or Web API's own hosting function, which minimizes all security factors by invoking the public-facing Web program through the local Service interface. (But the development process and maintenance will increase the complexity)