次の方法で共有

SQL Server Connection Strings for ASP_NET Web Applications

  • [アーティクル]

[Note: This post is a preliminary version of a document that has been published on MSDN. The published version with changes resulting from the comments received is now available here. Thanks to everyone who sent comments about this version.]

This is one of a series of posts that present preliminary versions of pages that will eventually be published on MSDN. These pages are a work in progress and suggestions for improvements or corrections are welcome. The intended audience is newcomers to ASP.NET and the purpose is to provide basic guidance for making data access choices for ASP.NET application development. Please forgive the less than ideal formatting -- the HTML for this post was generated by tools that weren't designed for blog publishing. I corrected some of the deficiencies but did not fix all of them.

The series includes:

-- Tom Dykstra
ASP.NET User Education

This topic provides examples of SQL Server connection strings for typical ASP.NET web application scenarios. It also includes instructions for converting connection strings created by Visual Studio project templates between SQL Server Express and LocalDB. The remaining sections of the topic explain some of the more common connection string settings.

This topic contains the following sections:

Connection String Examples

The examples show the connection strings as they appear in the ConnectionStrings element of the Web.config file, where the connectionString attribute must be accompanied by a providerName attribute.

In all of these examples, if you are using Entity Framework Code First, ConnectionStringName is typically the name of the context class.

You can also construct connection strings in code by using the SqlConnectionStringBuilder API for SQL Server Express, LocalDB, SQL Server, or SQL Database. There is no corresponding API for SQL Server Compact.

Connection strings are not wrapped to multiple lines in the Web.config file but are done so here due to limitations of this blog.

  • SQL Server Express database that is defined in the local SQL Server Express instance.

     <add name="ConnectionStringName"    providerName="System.Data.SqlClient"    connectionString="Data Source=.\SQLEXPRESS;    Initial Catalog=DatabaseName;    Integrated Security=True;MultipleActiveResultSets=True"/>

    The example assumes that the SQL Server Express instance is named SQLEXPRESS, which is the default. For more information, see Data Source , Initial Catalog , Integrated Security, and MultipleActiveResultSets later in this topic.

 

  • SQL Server Express database in an .mdf file in the App_Data folder.

     <add name="ConnectionStringName"    providerName="System.Data.SqlClient"    connectionString="Data Source=.\SQLEXPRESS;    AttachDbFileName=|DataDirectory|\DatabaseFileName.mdf;    Integrated Security=True;User Instance=True;    MultipleActiveResultSets=True" />

    The example assumes that the SQL Server Express instance is named SQLEXPRESS, which is the default. For more information, see Data Source, AttachDbFileName, Initial Catalog, Integrated Security, User Instance, and MultipleActiveResultSets later in this topic.

 

  • LocalDB database.

     <add name="ConnectionStringName"    providerName="System.Data.SqlClient"    connectionString="Data Source=(LocalDB)\V11.0;    AttachDbFileName=|DataDirectory|\DatabaseFileName.mdf;    Initial Catalog=DatabaseName;Integrated Security=True;    MultipleActiveResultSets=True" />

    For more information, see Data Source, AttachDbFileName, Initial Catalog, Integrated Security, and MultipleActiveResultSets later in this topic.

 

  • SQL Server database using SQL Server security (log on to the server by using user credentials in the connection string).

     <add name="ConnectionStringName"    providerName="System.Data.SqlClient"    connectionString="Data Source=ServerName;    Initial Catalog=DatabaseName;Integrated Security=False;    User Id=userid;Password=password;    MultipleActiveResultSets=True" />

    The example assumes that you are connecting to the default SQL Server instance on the server. For more information, see Data Source, Initial Catalog, Integrated Security, and MultipleActiveResultSets later in this topic.

 

  • SQL Server database using integrated security (log on to the server using the credentials of the Windows user account).

     <add name="ConnectionStringName"    providerName="System.Data.SqlClient"    connectionString="Data Source=ServerName;    Initial Catalog=DatabaseName;Integrated Security=True;    MultipleActiveResultSets=True" />

    The example assumes that you are connecting to the default SQL Server instance on the server. For more information, see Data Source, Initial Catalog, Integrated Security, and MultipleActiveResultSets later in this topic.

 

  • SQL Database instance (formerly SQL Azure database).

     <add name="ConnectionStringName"    providerName="System.Data.SqlClient"    connectionString="Data Source=tcp:ServerName.database.windows.net,1433;    Initial Catalog=DatabaseName;Integrated Security=False;    User Id=username@servername;Password=password;    Encrypt=True;MultipleActiveResultSets=True" />

    For more information, see Data Source, Initial Catalog, Integrated Security, and MultipleActiveResultSets later in this topic.

 

  • SQL Server Compact database located in the App_Data folder.

     <add name="ConnectionStringName"    providerName="System.Data.SqlServerCe.4.0"    connectionString=    "Data Source=|DataDirectory|\DatabaseFileName.sdf" />

    For more information, see Data Source later in this topic.

 

  • For use with Entity Framework Database First or Model First.

     <add name="ConnectionStringName"    providerName="System.Data.EntityClient"    connectionString="metadata=res://*/     ContextClass.csdl|res://*/     ContextClass.ssdl|res://*/     ContextClass.msl;    provider=System.Data.SqlClient;    provider connection string=&quot;    Data Source=ServerName;Integrated Security=False;    User Id=userid;Password=password;    MultipleActiveResultSets=True&quot;" />

    The part before the first &quot; symbol specifies the conceptual model, data schema, and mapping information that is stored in the .edmx file. The part between the two &quot; symbols is the database connection string. In this example, the database connection string is the same as the one for SQL Server using SQL Server security.
    ContextClass in this example represents the fully qualified context class name (for example, namespace.classname). For more information about Entity Framework Database First or Model First connection strings, see ADO.NET Entity Framework Connection Strings.

How to Convert a SQL Server Express Connection String to LocalDB

Project templates for Visual Studio 2010 and Visual Web Developer 2010 Express create connection strings that specify SQL Server Express databases. To convert one of these connection strings to LocalDB, make the following changes:

  • Change "Data Source=.\SQLEXPRESS" to "Data Source=(LocalDB\V11.0)".
    This change assumes that you installed LocalDB with the default instance name. For more information, see Data Source later in this topic.
  • Remove "User Instance=True" if it is present. Also remove the preceding or following semicolon (;).

How to Convert a LocalDB Connection String to SQL Server Express

Project templates for Visual Studio 2012 and Visual Studio Express 2012 for Web create connection strings that specify LocalDB databases. To convert one of these connection strings to SQL Server Express, make the following changes:

  • Change "Data Source=(LocalDB)\V11.0" to "Data Source=.\SQLEXPRESS".
    This change assumes that you installed SQL Server Express with the default instance name. For more information, see Data Source later in this topic.
  • If the connection string contains AttachDBFileName, add at the end ";User Instance=True". Omit the semicolon (;) if the connection string already ends with one.

Data Source (synonyms: Server, Addr, Address, Network Address)

For SQL Server Express, LocalDB, SQL Server, SQL Database, this setting specifies the name of the server and the SQL Server instance on the server. For example, you can specify ServerName\Instancename. You can use ".", "(local)", or "localhost" in place of the server name to specify the local computer, and you can use an IP address instead of the server name. If you omit the instance name, the default instance is assumed. By default, SQL Server Express is installed without a default instance and with a named instance that is named SQLEXPRESS.

For LocalDB, use "(LocalDB)\V11.0" to refer to the automatic instance of LocalDB for SQL Server 2012. For named instances of LocalDB, replace "V11.0" with the name of the instance. For information about named instances of LocalDB, see SQL Server 2012 Express LocalDB.

For SQL Database the value of this setting typically has a "tcp:" prefix to indicate the protocol used and a ",1433" suffix to indicate the port number.

For SQL Server Compact, this setting specifies the path and name of the database file. You can use the |DataDirectory| variable in place of an absolute path; for information about this option, see the following section about the AttachDBFileName setting.

AttachDbFileName (synonyms: Initial File Name, Extended Properties)

This setting specifies the path and name of the database file for SQL Server Express or LocalDB databases that are not defined in the local SQL Server Express instance until the first time you access them by using the connection string. It is typically used for database files that you keep in the App_Data folder.

The App_Data folder a relatively secure place to store database files that you want to keep in the web application folder structure. ASP.NET will not serve contents of the App_Data directory in response to Web requests, which helps to maintain the security of the data in database files kept in this folder. In place of the physical path to a database file that is stored in the App_Data folder, you can specify the |DataDirectory| variable in the AttachDbFileName setting. ASP.NET automatically substitutes the file path to the App_Data directory for the |DataDirectory| connection-string variable when it opens a connection to the database. This ensures that the path to your database remains current if the application is moved to a different directory. If you don't use the |DataDirectory| connection string variable, you have to provide the full physical path to the database file.

For information about the database name that is used when the file is attached by the SQL Server Express or LocalDB instance, see the following section about the Initial Catalog setting.

Whichever method you use for specifying the location of the .mdf file, the .ldf file (log file) should be in the same folder as the .mdf file.

We recommend that if you want to use this option, you use it with LocalDB rather than SQL Server Express. If you use this option with SQL Server Express, you also have to specify the User Instance option, which is deprecated.

If you use this option with SQL Server Express, you won't find the database listed in the local SQL Server Express instance when you use SQL Server Management Studio or SQL Server Data Tools. For more information, see User Instance later in this topic.

The first time you connect to a database by using this option in the connection string, the SQL Server Express or LocalDB instance attaches the database, and it stays attached. When you want to connect to the same database in the future you could use Initial Catalog without AttachDbFileName if you prefer.

Initial Catalog (synonym: Database)

This setting specifies the name of the database in the SQL Server instance catalog.

Note
If the database will be used by the Entity Framework and your application targets the .NET Framework 4, you must include this setting for all connection strings except SQL Server Compact. You can omit this setting in applications that target the .NET Framework 4.5.

If you omit this setting, ADO.NET connects to the default database for the SQL Server instance specified in the Data Source setting.

If you omit this setting and include the AttachDbFileName setting for SQL Server Express or LocalDB, the full path to the database file is used as the database name. If the full path is longer than 128 characters, a default database name is constructed from the path by using a hash.

In LocalDB connection strings, the Visual Studio web project templates add a unique number as a suffix to both the file name and the Initial Catalog setting, as shown in the following example:

 <add name="DefaultConnection"    connectionString="Data Source=(LocalDb)\v11.0;   Initial Catalog=aspnet-ProjectName-20120702083251;   Integrated Security=SSPI;   AttachDBFilename=|DataDirectory|\aspnet-ProjectName-20120702083251.mdf"    providerName="System.Data.SqlClient" />

The reason for this is to avoid database name collisions in the SQL Server Express instance for LocalDB. Suppose the project specified the following values:

 Initial Catalog=aspnet;AttachDBFilename=|DataDirectory|\aspnet.mdf"

When you create a project with these settings and run it, LocalDB attaches the aspnet.mdf file and names the database "aspnet" Then when you create another project and run that one, LocalDB tries to attach that project's aspnet.mdf file to the same database name "aspnet". The operation fails because that name is already attached to the first project's aspnet.mdf file.

For the same reason, the suffixes added by the Visual Studio templates won't prevent name collisions if you create a copy of a project by copying the files instead of by creating a new project in Visual Studio, which generates a new unique number. If you want to create a new project by copying an existing project's files, change the suffix number manually to make it unique.

If you prefer simpler file names, omit the Initial Catalog setting, as shown in this example:

 <add name="DefaultConnection"    connectionString="Data Source=(LocalDb)\v11.0;   Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnet.mdf"    providerName="System.Data.SqlClient" />

With this connection string, the first project attaches its aspnet.mdf file to a database name that includes the full path to the file. A second project attaches its aspnet.mdf file to a different name because it is in a different folder. A disadvantage of this approach is that database names are long in the SQL Server Object Explorer window. If you have many such database names and the window is not especially wide, it might be hard to differentiate them, as you see in the following illustration:

Integrated Security (synonym: Trusted_Connection)

This setting specifies whether the connection specifies the user ID and password, or the current Windows account credentials should be used for authentication:

  • True means use Windows integrated security to log in to SQL Server, even if the connection string includes User ID and Password settings.
  • False means use SQL Server security to log in by using the User ID and Password values in the connection string, and raise an exception if they are not present.
  • SSPI (Security Support Provider Interface) means use Windows Security if User ID and Password are absent, and use SQL Server security if they are present.

You have to use integrated security when you use the AttachDBFileName option.

MultipleActiveResultSets

The MultipleActiveResultSets (MARS) option makes it possible to execute multiple queries simultaneously. This is a common scenario when you use the Entity Framework, especially if you leave lazy loading enabled. For example, the second line of code in the following example will raise an exception if you don't enable MARS because the query that returns instructors is still active when the ToList method executes a new query that retrieves related courses:

It is often more efficient to load related data by using eager loading, which retrieves all of the data by using a single join query, but sometimes join queries are inefficient. In most real-world applications, there are times when the best choice for loading related data is lazy loading or explicit loading. Therefore, connection strings for databases that you access by using the Entity Framework typically specify this option. This option carries a slight performance penalty, but in most scenarios its benefits outweigh any performance loss even if you aren't using the Entity Framework. For more information, see the following resources:

User Instance

This option is only used with SQL Server Express, not with LocalDB, a full edition of SQL Server, or SQL Server Compact.

You use the AttachDbFileName setting to connect to a database in an .mdf file, typically located in the web application's App_Data folder. In order to connect to this database file, the SQL Server Express instance on your computer has to attach the file. But only SQL Server Express administrators can attach databases, so this operation will fail if you are not an administrator in the SQL Server Express instance. Also, even if you are an administrator and are able to attach the .mdf file, the default service account used by SQL Express might not have permissions to read and write the .mdf file. The service account typically does not have permissions for folders in your user profile, such as your My Documents folder, which is where Visual Studio projects are created by default. You use the User Instance option to solve these problems.

When you have enabled the User Instance option in the SQL Server Express instance and connect using a connection string that has User Instance set to True, a special instance of SQL Server Express is created for your user account. You are a SQL Server Express administrator in this user instance, so the instance is able to attach the database file. And when your web application runs under the Visual Studio Development Server (Cassini) or IIS Express, the user instance runs under your account, so it has access to folders in your user profile. For information about what identity is used for the user instance when the application runs in IIS, see ASP.NET Impersonation.

Databases that you have attached to a user instance are difficult to manage by using tools such as SQL Server Management Studio (SSMS). You won't see them in SSMS when you attach to the SQL Server Express instance on your computer.

Note
In SQL Server 2012, user instances are deprecated. If you need the AttachDbFileNamefunctionality, we recommend that you use LocalDB instead of SQL Server Express.

For more information about SQL Server Express user instances, see the following resources:

Additional Connection String Resources

For more information about connection strings, see also the following resources:

See Also

ASP.NET Data Access Content Map

Comments

  • Anonymous
    February 02, 2013
    Best article ever! Thanks so much for solving 5 problems I was having in my new MVC application.
  • Anonymous
    January 16, 2014
    Thanks, the bit about connecting using SQLEXPRESS and IIS helped me.
  • Anonymous
    April 25, 2014
    This article is a HUGE help!  I'm migrating my app from Compact to Express and I'm pretty sure you answered at least three questions I had.  Thanks!
  • Anonymous
    July 20, 2014
    In the LocalDB database there is a mistype: InitialCatalog should have a space as "Initial Catalog "
  • Anonymous
    July 21, 2014
    Thanks for reporting that -- I've corrected it.
  • Anonymous
    January 22, 2015
    i have a problem that my asp.net application 15 to 20 seconds after login goes to hanging problem..............please help me...
  • Anonymous
    September 03, 2015
    Could you please provide an example of hosting a MVC app locally but using Azure as the SQL DB host with EF CodeFirst?