LOCALDB Installation and connection

Original: LocalDB Installation and connection

Detailed documentation on LOCALDB, including installation, connection, shared connection, etc. https://technet.microsoft.com/zh-cn/hh510202

Objective:

LocalDB can be used when the debugger does not have SQL Server installed. This is a simple SQL Server database that is convenient for local testing and eliminates the task of installing SQL Server

Environment:

VS2013 VS2015 MSSM2016 is installed, but SQL Server is not installed. W10 system

Installation: LocalDB

After installing VS2015, the localdb2016 VS2013 is probably 2014,

In addition, the localdb2014 has a separate installation package, while 2016 is not found. The VS2015 installation package has a LOCALDB2016 MSI file, but it is not available after installation.

Use the command to view the version as follows:

　　

Use:

Learned that localdb this thing has been installed, but at one moment do not know how to use. Find answers online using the following methods:

1. Open cmd, use sqllocaldb.exe this command

Sqllocaldb.exe I //View An example of an existing LocalDB

Sqllocaldb.exe v //All versions of LocalDB installed on the PC

Sqllocaldb.exe s [instance name] //start this instance

Sqllocaldb.exe-? //The Help information of this command

2. How to connect this instance using MSSM:

Open MSSM, server name there input (LOCALDB) \mssqllocaldb //parentheses inside is localdb this may be fixed, I did not change the back is the name of the instance .

And then it's connected, you can build the database.

3. The server value of this connection string is also written in this

Server= (LOCALDB) \mssqllocaldb

4. Connection string:

The connection strings configured in the ASP. NET program are as follows:

　　　Specify to connect to this database file MDF

server= (LocalDB) \mssqllocaldb; Integrated security=true; Attachdbfilename=d:\data\mydb1.mdf

//Do not specify the path to the MDF file, specify the default database name

Server= (LocalDB) \mssqllocaldb; Integrated security=true;initial CATALOG=MYDB1 "

//similar to the first kind　　

Data source= (localdb) \mssqllocaldb;integrated security=true; Attachdbfilename=d:\data\mydb1.mdf

//Specify User name and password (currently used this is this, simple and straightforward)

Server= (localdb) \mssqllocaldb;uid=sa;pwd=123456;initial catalog=mydb1

There is no problem when using iisexpress debugging with these kinds of connection strings in VS, but publishing to IIS does not connect to the database. Because of the problem of access permissions. For details, please see the first line of the article link.

The solution is:

1. Change the permissions of the application pool to LocalSystem this speculative approach, more convenient. But there are still a lot of problems.

2. Open LocalDB instance sharing: (this summary is, to LocalDB open a shared instance, to LocalDB set up a connection account, this is a feasible way to test )

Open a shared instance to LocalDB

///MSSQLLOCALDB: Instance name mylocaldb the shared instance alias of the instance name, and the other account is connected by this alias (the command Window uses Administrator privileges)

>sqllocaldb h "mssqllocaldb" "Mylocaldb"

When you connect using the MSSM connection tool, you also need to open with administrator privileges. The instance name becomes (LOCALDB) \.\mylocaldb the first \ followed by the. \mylocaldb is a shared instance alias

Why the above MSSM to be opened with the Administrator tool, because the following authentication method is the form of the account password, if you use Windows authentication, it is not required.

　　　　　　

　　　　　Set up a connection account for LocalDB

After this step, the Web program's connection string is written to the specified account and password, the result is still inaccessible, or no permissions,

Server= (LocalDB) \.\mylocaldb;uid=sa;pwd=123456; Attachdbfilename=d:\data\mydb1.mdf

View the database account number, found that LOCALDB does not have SA this account , so add it, and give the DB_OWNER

Finally, open the program in the browser, found that the connection was successful, the Web page opened.

LOCALDB Installation and connection