How to Configure a Connection String for SQL Server 2008 Express
TCP/IP should be Enabled within SQL Server Configuration Manager.
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.
- 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.
- Use the following data source connection string in your ASP.NET application:
Data Source=.\SQLEXPRESS;Initial Catalog=yourdatabasedb;Integrated Security=SSPI
- So in your ASP.NET web.config file the full connection string might look like this:
<add name="DBConnString" connectionString="Data Source=.\SQLEXPRESS;Initial Catalog=yourdatabasedb;Integrated Security=SSPI;" providerName="System.Data.SqlClient" />
- Now open SQL Server Management Studio.
- Within the "Object Explorer" sidebar, double click on the "Security" folder, then double click on the "Logins" folder.
- 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).
- 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