This is information that exists all over the place- the problem is that it is spread around forum comments and the solutions are as plentiful as the questions. I am going to explain how to create connections strings in an ASP.NET web application. The app is hosted on IIS 7.5, Server 2008 R2, and created in Microsoft Visual Web Designer 2010 Express (VWD). For the back-end I use SQL 2008 R2 Standard. VWD uses SQL 2008 Express natively, but I am not going to downgrade for this app.

There is also a little issue of server architecture. I was running into all sorts of problems with IIS and VWD being x64, while the connections drivers were running as x32.

A couple of key points.

  • In order for your application to connect to a database of any sort, it needs a connection string, which is a bit of code inserted into the web.config file in the root of your site.
  • The connections string tells the app: the source of the data, the driver to use, and the data location.
  • Each connection to a database needs to use a specific driver.

The first situation in which you need a connection string is for your app to contain membership information. Most of this stuff is done through wizard-like windows in VWD, but you will often need to change the default settings. This database is created by default when you Configure ASP.NET from within VWD. But I would rather create my own database, and hook up my application to it.

So I first create my database. Open up SQL Server Management Studio. connect to the instance you wish to have your app use. You can create a new instance, but I had an old one not being used, named MyServer\SQLEXPRESS. Do not be confused- this is not actually and Express database, just the name of the instance.

Connect to the instance, use Windows Authentication– this is of course your choice.

Connect to SQL Instance

Connect to SQL Instance

I’m going to glaze over the rest, but here’s what has to happen. You need to create a database, the default settings are alright. You need to grant the account that runs your application pool access to the database. My apppool runs under its own identity, and that identity needs Connect, Read, Select, Update, and Delete rights on the database.

For your ASP.NET security database, it needs to be prepared with a tool for ASP.NET to access it. Make sure you run the right tool, there are many.

C:\Windows\Micrososft.NET has a Framework and a Framework64 folder. Choose the one which is the same architecture as your OS.

So C:\Windows\Micrososft.NET\Framework64\ and then choose the folder that corresponds to the app Framework Version. This can be found many different ways, but if you right-click on the address of your app in solution explorer, and select property pages, you can get an idea.

Framework

Framework

 

So  C:\Windows\Microsoft.NET\Framework64\v4.0.30319\ and click on the application named aspnet_regsql.exe. This will run you through a wizard that prepares the database to hold site membership information.

When you use the wizard in your ASP.NET configuration, it will create all the required connections strings and providers for you, I had to tweak mine a bit.

Here is an example of the connection string I used.

<add name=ApplicationServices connectionString=data source=.\SQLEXPRESS;Integrated Security=True;Initial Catalog=aspnet; providerName=System.Data.SqlClient/>

 The name is only the name of the connection, this san be whatever you wish. The connectionstring is important. this has more arguments such as username, password, security, etc.

 
Important: data source=.\SQLEXPRESS;
This is the source instance. .\ means the local box. You could also do:
 
localhost\instancename
servername\instancename
remoteservername\instancename
  
Then there is Initial catalog=aspnet
This is the database name that this connection uses. There are other ways to format this, but Initial catalog=databasename without prefix works the best for my app.
With the ASP.NET membership security comes some other entries in your web.config, and these need to point at the connectionstrings NAME, in this case AplicationSecurity.
Here are two more examples of connection strings:
<add name=TheDatabaseSQLConnectionString connectionString=Data Source=******MEMBER\SQLEXPRESS;Initial Catalog=TheDatabaseSQL;Integrated Security=True providerName=System.Data.SqlClient;

/> 

 

 <add name=ContactSQLConnectionString connectionString=Data Source=******MEMBER\SQLEXPRESS;Initial Catalog=ContactSQL;Integrated Security=True providerName=System.Data.SqlClient/>

<add name=ContactConnectionString connectionString=Data Source=C:\TheDatabase\thedatabase2\App_Data\Contact.mdb;User Id=Admin;Password=; providerName=Microsoft.Jet.OLEDB.4.0;Data/>

You will notice that the data sources on the second one points to a file location, .mdb. this is because it is a Microsoft Access database, which is not hosted, only a file. you also see the provider name is actually the driver that is used for the connection, Jet in this instance.

You can get these connections strings from inside of VWD.

Click Tools>Connect to Database.

Add Data Connection

Add Data Connection

 

The Data Source is the driver you will use for your connection. I use SQL Server and Microsoft Access.
Click the drop down box and select the server\instancename where your database is located.
 
Select your authentication, mine is Windows.
Select or enter database name is used mostly with SQL databases, pick the database you created or have for the link here. With Access,  you can use Attach a Database file.
 
Now Test Connection, if everything is configured and set up, you should get a success message.
Now click Advanced.
 
There are more things to customize here. In the bottom is a box- this is your connectionstring. You can copy this information and paste it into your web.config, then edit the formatting to match the examples above.
Advanced Properties

Advanced Properties

With these connectionstrings in your web.config, you can drag AccessDatasource and SqlDataSource controls from the VWD toolbox onto your application, and then point them at the correct databases.
 

<asp:SqlDataSource ID=”ListDataSource” runat=”server”ConnectionString=”<%$ ConnectionStrings:ContactSQLConnectionString %>SelectCommand=”select COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = ‘Contact'”></asp:SqlDataSource>

 

 

<asp:SqlDataSource ID=”SqlDataSource1″ runat=”server”

ConnectionString=”<%$ ConnectionStrings:ContactSQLConnectionString %>

SelectCommand=”SELECT * FROM [Contact]”></asp:SqlDataSource>

 

Note: For Access database files, make sure to add them into your App_Data folder in the application, then point to them there.There is another thing to mess around with when talking about application connections. Well drivers and all that- but I won’t go into that here.

I was having one hell of a time getting my application to talk to my databases. that is because when you design in IIS 7.5 x64, and VWD x64, your application is ment for x64 machines. It does not talk well to x32 drivers (in theory it should, with Microsoft’s backwards compatible drivers). So I changed my application to x32. This can be done using the Build>Configuration Manager… menu in VWD. This can also be set on your application pools in IIS. I changed the apppool serving my web application to support x32, and everything is working great with my older databases and .JET drivers. Right click on your application pool, select Advanced Properties, then change the value for enable 32-bit Applications to True.

Enable 32-bit Applications

Enable 32-bit Applications