CovertCoder - Topic: Error - Unable to connect to SQL Server database (ASP.Net Web Site Administration Tool)

Main Navigation

Tools

Featured Articles

Tutorials

Topic: Error - Unable to connect to SQL Server database (ASP.Net Web Site Administration Tool)

The notes below are good to follow, if having more trouble, but initially try the following

1. Refesh your solution

2. Open the web.config

3. Add the code below in the Configuration elements

<add name="LocalSqlServer"

connectionString="Data Source=localhost;

Initial Catalog=aspnetdb;

Integrated Security=True"

providerName="System.Data.SqlClient"/>

</connectionStrings>

4. Change the Data Source to your instance of SQL Server

5. Change the Initial Catalog to your database

6. If you have not ran the aspnet_regsql.exe at a .net command prompt. You need to do this

first. This will add the necessary tables to a SQL Server database of your choosing. The

default is named aspnetdb but you can change that.

7. Change security if need and add userid and password

Other things to think about

1. Provision your database. you can do this, by running aspnet_regsql.exe at a .net command

prompt. This will add the necessary tables to a SQL Server database of your choosing. The

default is named aspnetdb but you can change that.

2. More than likely, the providers in your web.config are trying to point to:

connectionStringName="LocalSQLServer"

This causes a problem because VS 2005 automatically configures it to use SQL Server 2005 Express.

If you have installed another db on your machine and are trying to access it, but don't have

SQL Server 2005 Express, you are going to get errors. This is because LocalSQLServer is pointing

to .\SQLExpress when you don't have one. This is in the machine confige file, as well.

3. Whatever connection string you are using in your web.config file needs to be what

the provider is pointing to. If you look at the attributes in your provider, you will see

that this is the following connectionStringName:

e.g. connectionStringName="LocalSQLServer"

Change this to point to the connection string you are actually using

Notes from a web site

Microsoft's application services (membership provider, role provider, personalization provider, and profile provider) need to store their data somewhere. There is an issue when you opt not to install SQL Server 2005 Express on your machine because the machine.config is essentially hard-wired to use the SqlExpress instance on the local machine to store this data. In addition, if you wish to redirect the app services store to another machine you will have to initialize the data store. (Aside: if you are interested in creating a custom provider, check out this ASP.NET 2.0 Provider Model: Introduction to the Provider Model.)

The issue comes up when you attempt to run Website|ASP.NET Configuration. Basically, this utility won't be able to find your provider if you haven't installed SQLExpress on your machine (which should be very common if you are installing Visual Studio 2005 Pro or Team System).

So if you install any version of Visual Studio along with SQLExpress, you're fine. But if you don't install SQLExpress or wish to redirect your app store to another location that you need to select one of the following two options:

Option 1. Fix it on an app by app basis. This is the method suggested by Scott Guthrie in his MSDN Library article entitled Configuring ASP.NET 2.0 Application Services to Use SQL Server 2000 or SQL Server 2005 . If you follow this method, you'll need to do two things:

a. provision your database. you can do this, by running aspnet_regsql.exe at a .net command prompt. This will add the necessary tables to a SQL Server database of your choosing. The default is named aspnetdb but you can change that.

b. add the following to your web.config file:

<add name="LocalSqlServer"

connectionString="Data Source=localhost;

Initial Catalog=aspnetdb;

Integrated Security=True"

providerName="System.Data.SqlClient"/>

</connectionStrings>

</configuration>

NOTE: (DL)

I changed the connection in the ASP.NET Application to use the "LocalSqlServer" referenced above to get the administration page to work. Just changing the name above to what you are using in your application is not going to work.

The name LocalSqlServer is important here. This is the connection string that ASP.NET will be looking for and is defined in your machine.config to point to SQLExpress. The above will override the machine.config and have it point to a local unnamed SQLServer instance so if you are using a named instance or wish for it to pont to a different SQLServer, change the connectionString attribute in the above.

Option 2. Change your machine.config (carefully after backing it up, of course) to fix this once and for all on your each development machine. The steps:

a. Like option 1, you will need to provision your database. you can do this, by running aspnet_regsql.exe at a .net command prompt. This will add the necessary tables to a SQL Server database of your choosing. The default is named aspnetdb but you can change that.

b. Change the connectionStrings and/or providers sections of the machine.config. These sections appear very close to the end of the machine.config. By default, these sections look like this:

-------------------------------------------

</connectionStrings>

<system.data>

</DbProviderFactories>

</system.data>

<system.web>

</providers>

</membership>

</providers>

</profile>

</providers>

</roleManager>

-------------------------------------------

I suggest adding a new connection string and then pointing the providers to point to your new connection string. So I changed my connectionStrings section of my machine.config to look like (note that I left the LocaSqlServer connection string alone and just added a new one named LocalSqlServer2005):

-------------------------------------------

</connectionStrings>

-------------------------------------------

In this case, I am using a local named instance of Sql Server. Then I carefully located all the providers looking for "LocalSqlServer" and replaced them with "LocalSqlServer2005" in the machine.config. (In a production environment, I would make this point to a shared SQL Server on another server but you should get the point here. I would also name it differently since it no longer would be local.) In my machine.config I found three references to the connection string located in the following providers: AspNetSqlMembershipProvider, AspNetSqlProfileProvider, AspNetSqlRoleProvider.

Once I modified my machine.config to the above, the application services worked just fine for all newly created Web sites.