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.
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.
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.
<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 can get these connections strings from inside of VWD.
Click Tools>Connect to Database.
ConnectionString=”<%$ ConnectionStrings:ContactSQLConnectionString %>“
SelectCommand=”SELECT * FROM [Contact]”></asp:SqlDataSource>
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.