ASP.NET with SQL Server 2005 Express Edition databases

[Will]

Hi, I'm relatively new to ASP.NET, and I've created a simple ASP.NET website using Microsoft Visual Web Developer 2008 Express Edition.

The website has one .aspx page, on this page, it has a SqlDataSource control that retrieves data from a Microsoft SQL Server 2005 Express database (a simple database with one table).

I then display that retrieved data using a GridView or DetailsView control (it doesnt matter).

The website work when I test it locally in MS VWD 2008, but when I upload it online and view the page, I receive this error:

Server Error in '/' Application.
--------------------------------------------------------------------------------

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

So I asked for help in LivePerson chat, and the person told me that they do not support "attached database" and that I would have to "create a database from Plesk and the create a backup (.BAK) file of my database and upload it", he also mentioned something about giving me a connectionString? So now Im really confused.

Can someone please help me and explain why can't I just upload the database and connect it in the normal way? and what I need to do exactly?

Is it as simple as just creating a .BAK file? What happens behind the scene? Do you guys copy the backed up database and transfer all the table to the database I created in Plesk? Do you transfer the data for us? How does it work?

<connectionStrings>
		<add name="ConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ASPNETDB.MDF;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>
	</connectionStrings>

Thats my connection string in the web.config file, is that correct? or do I need to change it?

I've looked for threads on this forum but it seems none of them addresses my question.

I hope someone from eUKHost can kindly help me and give me a brief explanation please.

Thank you so much!

Will

[Spencer]

Hi Will,

Can someone please help me and explain why can't I just upload the database
and connect it in the normal way? and what I need to do exactly?

It seems that you are trying to attach an MSSQL DB. We don't support MSSQL user instances on Shared servers.
I would recommend you to follow these steps to get your Database working :
a) Create a blank MSSQL DB from Plesk control panel.
b) Take a backup of your DB in .BAK format from local PC & upload the BAK file into your webspace.
c) Provide us the exact location of your DB-BAK file & update your connection string with the new DB details.
d) [We will get the DB-BAK] restored into your blank DB properly, once done, you can access the DB through your scripts.

Is it as simple as just creating a .BAK file? What happens behind the scene?
Do you guys copy the backed up database and transfer all the table to the
database I created in Plesk? Do you transfer the data for us? How does it work?

Under Plesk CP, when you create a website, a application pool user (IWAM user) also gets associated with it and your web applications run under this user. So for attaching the DBs and assigning privileges to this user is security thread. So we restore .bak file on your bank DB and it runs under the DB user created under Plesk CP.
After the DB gets restored from bak file, you will need to change your connection string in web.config file as follows:
<add name="ConnectionString" connectionString="Data Source=Server IP\SQLEXPRESS;Initial Catalog=Database Name;User ID=DB_User;Password=DB_PWD" providerName="System.Data.SqlClient" />

[Will]

Hi thank you for your reply, although I understand what I have to do now, I still don't understand a few things you said, its very complicated:

1. What do you mean by the you "don't support MSSQL user instances", what is user instances?

2. How do I create a .BAK file of my database? Can I not just zip it and upload it to my web space?

3. You say I need to change my connection string to this:

<add name="ConnectionString" connectionString="Data Source=Server IP\SQLEXPRESS;Initial Catalog=Database Name;User ID=DB_User;Password=DB_PWD" providerName="System.Data.SqlClient" />

But, there are no database user or password, I didnt set any, so what do I put there? And do I need to create a database user and password for it? Is it essential to create a password for the database?

Thank you for your help.

[eUK-Gabriel]

Hi Will,

Why does the attachDB method does not work.. /"don't support MSSQL user instances ?

Plesk creates a different user for application pool and applications on a website does not run under Network Service but under its application pool user. To attach a .MDF file to the SQL server the application pool user must be a member of SQL database administrators and it would not possible to assign these privileges to an application pool user on a Shared server for security reasons.

We noticed that many users failed to attach the database using the .MDF file placing it in their App_Data folder and hence we have suggested to create a physical database from the Plesk control panel and access it with the user name & password in their connection string.

You have two options -
Either access your database remotely and recreate the tables in it
Or
If you have your database on your local machine then create the backup of it in .BAK format and upload it on server so that we will restore it for you.


How to take the backup ?

Its very easy to backup the database if you are using SQL Server Management Studio - Connect your database present on your local machine >> Right click on database >> Select Tasks >> Back Up.. >> it will open up a new window >> under Source section select the database name from drop down list >> Backup type as Full >> under Destination section click on Add >> it will open a new window >> here enter the location and file name with extension as .Bak and hit Ok >> once again click Ok on main window.
It will generate the backup in the provided directory. You can upload this file on server by FTPing your web site.

SQL Server Management Studio is a recommended and absolutely free database management tool which you can download at -
microsoft.com / downloads / details.aspx?FamilyID=C243A5AE-4BD1-4E3D-94B8-5A0F62BF7796&displaylang=en

When you create database from Plesk, you have to create a user with password for it, you have to use these login details in the connection string we have provided.

You can refer following link for more information on generating the database backup -
eukhost.com / forums / f15 / how-backup-mssql-server-2005-database-4563

[Will]

Hi Gabriel,

You say I have 2 options:

How do I make the 1st option work? How do I access my database remotely and recreate the table? What do you mean by this?

Secondly as I mention in my first e-mail, I'm only fairly new to ASP.NET and Ive only read a few books on it, but what you say about application pool etc.. makes no sense to me at all. Please can you try to explain in more easier terms.

Lastly, I'm use Visual Web Developer 2008 Express Edition to create my website and databases, which is a Microsoft SQL Server 2005 Express Edition, so is there a way I can back it up without downloading SQL Server Management Studio?

I belive SQL Server 2005 Express Edition databases is different to SQL Server 2005 databases, because its only a express edition, so are the procedures the same? Will it still work?

Thank you Gabriel.

[Spencer]

Hi Will,

How do I make the 1st option work? How do I access my database remotely and recreate the table? What do you mean by this?

For accessing/managing databases remotely, You should have MSSQL Management Studio 2005 Express installed on your local machine. To access your database remotely and recreate the tables in it, is the same way you did it on local machine to create tables and add records under it.

Application pool = A grouping of one or more URLs served by a worker process. You don't need to worry about it, we do take care of it.

You will need to provide us the backup as per following procedure mentioned in the thread:
eukhost.com / forums / f15 / how-backup-mssql-server-2005-database-4563
and use the connection string in web.config as

<add name="ConnectionString" connectionString="Data Source=Server IP\SQLEXPRESS;
Initial Catalog=Database Name;User ID=DB_User;
Password=DB_PWD" providerName="System.Data.SqlClient" />

[Here DB_user and DB_PWD used are from the Database User created under Plesk CP.]

If you need any further assistance, kindly contact us on 24X7 Live Chat support.

[Will]

Thanks to the help of Gabriel, my problem is now solved. Just to summaries the solution for anyone else who have had the same problem as me, and for those who are new to ASP.NET and using Microsoft Visual Web Developer 2008 Express Edition with SQL Server 2005 Express Edition too.

1. Create a blank database in Plesk and create a username and password for the database.

2. Download Microsoft SQL Server Management Studio Express.

3. When you start Microsoft SQL Server Management Studio Express, it will ask you to connect to a server name, by default, it is "COMPUTER\SQLEXPRESS", which I believe is to manage your own database locally. But for now, we just want to make a back up of the database.

4. Upload it to your web space.

5. Contact a helpful staff like Gabriel and kindly ask him to restore the backup to the blank database.

6. Use Microsoft SQL Server Management Studio Express and log into your database with your database username and password and check if all the tables etc... have been restored properly. (Now you can even mange your database remotely).

7. Change your connection string on your web.config:

<add name="ConnectionString" connectionString="Data Source=Server IP\SQLEXPRESS;Initial Catalog=Database Name;User ID=DB_User;Password=DB_PWD" providerName="System.Data.SqlClient" />

8. Add this to your web.config also:

<membership>
<providers>
<remove name="AspNetSqlMembershipProvider" />
<add name="AspNetSqlMembershipProvider"
type="System.Web.Security.SqlMembershipProvider,
System.Web, Version=2.0.0.0, Culture=neutral,
PublicKeyToken=ABC"
connectionStringName="ConnectionString"
enablePasswordRetrieval="false"
enablePasswordReset="true"
requiresQuestionAndAnswer="true"
applicationName="/"
requiresUniqueEmail="false"
passwordFormat="Hashed"
maxInvalidPasswordAttempts="5"
minRequiredPasswordLength="7"
minRequiredNonalphanumericCharacters="1"
passwordAttemptWindow="10"
passwordStrengthRegularExpression="" />
</providers>
</membership>

<profile>
<providers>
<remove name="AspNetSqlProfileProvider" />
<add name="AspNetSqlProfileProvider"
connectionStringName="ConnectionString"
applicationName="/"
type="System.Web.Profile.SqlProfileProvider,
System.Web, Version=2.0.0.0, Culture=neutral,
PublicKeyToken=ABC" />
</providers>
</profile>

<roleManager>
<providers>
<remove name="AspNetSqlRoleProvider" />
<add name="AspNetSqlRoleProvider"
connectionStringName="ConnectionString"
applicationName="/"
type="System.Web.Security.SqlRoleProvider,
System.Web, Version=2.0.0.0, Culture=neutral,
PublicKeyToken=ABC" />
</providers>
</roleManager>

9. COMPLETE. It should work now.

However, I still have a few questions:

*** On step 8, Gabriel helped me to add those codes to my web.config, but why didn't Visual Web Developer add it for me automatically when I created my web application locally? It worked without those codes when I tested it locally, so why do I need to add it when I put my website online?

*** By default, the ASP.NET Web Site Administration Tool helps users to create user accounts by creating a database called ASPNETDB.MDF that works with the Membership class.

Now I tried to change the name of this database in Visual Web Developer, but it wouldnt let me, when I change it, it automatically changes it back to the default name which is ASPNETDB.mdf, does anyone know the reason for this? Does the ASP.NET Web Site Administration Tool require the database to be called ASPNETDB.MDF in order to work?

Cheers for the help again.

[eUK-Gabriel]

Hello Will,

There is a big difference when you run your scripts on your local machine and when you run it on a remote server. I am not sure why Visual Web Developer(VWD) did not add this code in web.config on your local machine but its necessary to add it on server in case of AspNetSqlRoleProvider.

Visual Web Developer is a tool for creating ASP.NET Web Sites / applications, by default it uses the name ASPNETDB.mdf for the database file.
You have to consider two factors -
connecting web site on local machine using VWD
or
connecting web site on remote server
When you access web site on local machine using VWD, it creates the database file ASPNETDB.mdf on local machine to attach it in connection string. As AttachDB method is not recommended on server we have suggested to create a physical database and use it in connection string.
To make changes(including database name, its login detail etc.) in your web site you have to connect to server using VWD and make the required changes. I am sorry but I am not sure why VWD did not allow you to change the database name on your local machine.

[eUK-Martin]

The major fact is:

On local machine you run your application as administrator who has access to the entire machine...
And
On the server you run your application as a web_user that has most limited access...

[Binary]

I always think a lot of this is down to Microsoft and their attempts to make it easy to do development, it just makes it harder to deploy sites to a proper server.

What I would recommend is get a copy of SQL Server Management Studio Express from Microsoft, create the databases in there and then just use a normal connection string from your local web site, treating your machine as an SQL server, not using the attach db option.

That way what you have locally mirrors what you will have on a hosting environment.

This might be more difficult for beginners but it's better in the long run.