How to Configure a Connection String for SQL Server 2008 Express

by

Here's one way to setup your connection string for a SQL Server 2008 Express database for a live website using ASP.NET on Windows Server 2008. 

  1. Open "Sql Server Configuration Manager" -> the click on "SQL Server Network Configuration" -> then click "Protocols for SQLEXPRESS" ->  then right-click on "TCP/IP" and click Enable.
  2. Use the following data source connection string in your ASP.NET application:
    Data Source=.\SQLEXPRESS;Initial Catalog=yourdatabasedb;Integrated Security=SSPI 
  3. So in your ASP.NET web.config file the full connection string might look like this:
  4.  <add name="DBConnString" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=yourdatabasedb;Integrated Security=SSPI;" providerName="System.Data.SqlClient" />
  5. Now open SQL Server Management Studio.
  6. Within the "Object Explorer" sidebar, double click on the "Security" folder, then double click on the "Logins" folder.
  7. Now you must map the Login name "NT AUTHORITY\NETWORK SERVICE" to your database and also assign the Login name the database Role "db_owner".  To do this: Right-click on "NT AUTHORITY\NETWORK SERVICE" and select "Properties" and then go to the "User Mapping" section -> Select your database in the "Users mapped to this login" section and make sure the checkbox is checked in the "Map" column.  Then go to the "Database role membership for: yourdatabase" section and select "db_owner" role (so there is a checkmark next to db_owner).
  8. Click "OK" to close down the Login Properties box for NT AUTHORITY\NETWORK SERVICE.

That should do it... hopefully your site connection string will now be properly working for a SQL Server 2008 Express database. 

Keep in mind, that there's many different ways to setup your connection string, the method outlined above uses Integrated Security=SSPI.  If you're using a Username and Password in your connection string then the process may be slightly different for you.

The connection string setup example above may also help to solve the following error message:

Exception Details: System.Net.Sockets.SocketException: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond

 

 


0
3

3 Comments

anonymous by Rose Ann on 10/23/2011
There is no "NT AUTHORITY\NETWORK SERVICE" on the Logins folder. What will I do,? I've been working on our Software Analysis Development project for days and sleepeless nights, I still can't established connection to the database, my connection strings was incorrect.. Please help..
anonymous by bitfiddler on 12/12/2011
Rose Ann - Your post is a little old, but if you are still having the problem, make sure that you login to the Windows machine as administrator before running SQL Management Studio. YOu should see the NETWORK SERVICE then.
anonymous by bitfiddler on 12/12/2011
I'm working with OLE DB connection from ASP .NET and found that I needed to insert Provider=SQLNCLI10; at the front of the connection string before Data Source so actual entry looks like:

<add name="SQLExpress" connectionString="Provider=SQLNCLI10;Data Source=.\SQLEXPRESS;Initial Catalog=|DataDirectory|mydatabase.mdf;Integrated Security=SSPI;" />

Add your comment

by Anonymous - Already have an account? Login now!
Your Name:  

Comment:  
Enter the text you see in the image below
What do you see?
Can't read the image? View a new one.
Your comment will appear after being approved.

Related Posts


This step-by-step goes through the process of importing delimited text files into a SQL Server 2005 database. The example should help you better understand how to import different types of flat files into SQL Server 2005.  more »

If you’ve experienced as many problems as I have while trying to Import/Export my Sql Server 2005 database to a hosting provider for the first time, then hopefully this how-to will be of some assistance to you.  more »

When using SQL Server 2008, you may receive a Save (Not Permitted Dialog) box pop-up when trying to save changes to an existing table. The dailog box reads: "Saving changes is not permitted. The changes you have made require the following tables to be...  more »

I recently upgraded from Microsoft SQL Server 2005 to Microsoft SQL Server 2008 on my Windows Vista computer. After successfully installing SQL Server 2008, I could not find the Reporting Services instance installed. I have a feeling this was due to the...  more »