Database Access Issues

[TrevorL]

Issues Accessing SQL Server Databases

I have a couple of websites on eUKHost, including a simple ASP.NET one at ArmourArchive.co.uk that gets book data on request from an XML file and works fine. However, I'm currently trying to build a more sophisticated one at PreservedTanks.com that will get data from an SQL database. Although I'm a professional C# application developer, I'm less experienced at web development so I thought I'd start with something easy - the standard Microsoft Personal Website Starter Kit (PWSK).

I downloaded Web Express, built the Personal Website Starter Kit option, did some minor personalisation (basically changing name etc.) and uploaded it. Two days later I still can't get it to work. Please help!

Basically, as I understand it, there are two ways to get a SQL server database working on my site. Firstly, create one through the Plesk control panel, then run a script in its WebAdmin page to create the tables I need. I can create the databases fine (I've created two so far: aa_personal and person_remote). Then I used the MS Database Publishing Wizard to create the necessary scripts but when I paste the scripts into the WebAdmin page and Execute Query I just get lots of errors and no new tables (method is based on this tutorial: http://weblogs.asp.net/scottgu/archi...nt-part-1.aspx).

The second method is to attach my existing databases to SQL server - this is my preferred option since I can be sure they contain the right tables, and I can upload and download the complete databases (with all data) whenever I want. I have the standard PWSK personal.mdf and aspnetdb.mdf database files in my \app_data folder. For this method I've tried using connection strings such as the following, but nothing works:

The following gives an AttachDbFilename error:

	<connectionStrings>
		<add name="Personal" connectionString="Data Source=mssql.preservedtanks.com;Initial Catalog=person_remote;UID=<myuserID>;PWD=<myPW>;AttachDbFilename=|Data Directory|personal.mdf;Database=personal;Trusted_Connection=Yes;" providerName="System.Data.SqlClient" />
		<remove name="LocalSqlServer"/>
		<add name="LocalSqlServer" connectionString="Data Source=mssql.preservedtanks.com;Initial Catalog=person_remote;UID=<myuserID>;PWD=<myPW>;AttachDbFilename=app_net\aspnetdb.mdf;Database=aspnetdb;Trusted_Connection=Yes;" providerName="System.Data.SqlClient"/>
	</connectionStrings>

but the following gives a Login failed for user '(null)' error:

	<connectionStrings>
		<add name="Personal" connectionString="Data Source=mssql.preservedtanks.com;Initial Catalog=person_remote;UID=TrevorLarkum;PWD=personrem1;AttachDbFilename=app_net\personal.mdf;Database=personal;Trusted_Connection=Yes;" providerName="System.Data.SqlClient" />
		<remove name="LocalSqlServer"/>
		<add name="LocalSqlServer" connectionString="Data Source=mssql.preservedtanks.com;Initial Catalog=person_remote;UID=TrevorLarkum;PWD=personrem1;AttachDbFilename=app_net\aspnetdb.mdf;Database=aspnetdb;Trusted_Connection=Yes;" providerName="System.Data.SqlClient"/>
	</connectionStrings>

There are various references on this site to very similar strings, and it nearly works (i.e. it doesn't fail immediately, but once it starts to access data from the databases) so I wonder if I just need to set the correct disk location for my databases - if so please advise what they should be for me. Otherwise if I'm miles off please point me in the right direction.

Similary, I can access the databases created by Plesk via SQL Server Management Studio Express, but again don't know how to remotely access the databases in my App_data folder.

[eUK-Martin]

Hello,

Most of our clients have failed to attach the database from the .MDF file in their application directory and therefore we have suggested them to create a physical database from the Plesk control panel and access it with the user name & password in their connection string. Since you have already created the database from Plesk you can do either of the following:

1. Connect the SQL database remotely and create all the tables in it.
OR
2. Create a backup up of your database on your local machine and upload the .bak file on the server and ask us to restore it for you.

Now the question is, Why does the attach method does not work..?

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.

Now it would not be possible to assign these privileges to an application pool user on a Shared server for security reasons. Therefore it gives "Access Denied" error when attaching a .MDF file.

[TrevorL]

Martin,

Thanks for your response and for your explanation of the Plesk database access issue.

Taking your suggestions in order, some questions:

0. "Most of our clients have failed to attach the database from the .MDF file in their application directory" - it sounds like this could still be worth a try. Please let me know the physical location of my site so I can give it a go, and I'll report back.

1. "Connect the SQL database remotely and create all the tables in it." - assuming you mean do this with Management Studio or similar in place of the Plesk WebAdmin route - I'll try this and report back.

2. "Create a backup up of your database on your local machine and upload the .bak file on the server and ask us to restore it for you." Thanks for the offer. However, the db will get upgraded a number of times over the next few weeks and months. Does this mean we would have to do this manual upload every time?

Thanks again,

Trevor

[TrevorL]

Originally Posted by TrevorL

1. "Connect the SQL database remotely and create all the tables in it." - assuming you mean do this with Management Studio or similar in place of the Plesk WebAdmin route - I'll try this and report back.

I've just tried this (and I remember now I did try it over the weekend). I use SQL Server Management Studio Express and connect to mysql.preservedtanks.com and login in to my database using my SQL Server user id and password (i.e. the same as I used in Plesk to create the database). This shows me a list of databases called DURHAM\Databases; there are about a 100 shown so I imagine they are all the client databases on our shared server - but I only have permission to see the details in my databases. Within my ones I can see the available tables etc. However, if I Execute a query against my database to create new tables it just generates errors and no new tables are created.

Edit: I've looked into this in some more detail - some simple commands (e.g. CREATE TABLE) execute successfully, but the full script required to recreate my databases fails. I'm thinking it may be a SQL Server version problem. I'm using SQL Server 2005 Express at my end. Could it be that SQL Server 2000 or some other version is running on the server?

It's looking like this 'Method 1.' could work, but I'd still appreciate some feedback on the questions in my post above.

[Rock]

Hi Trevor,
Apologies for replying late, we were busy with few administrative tasks being carried out on few of our servers.. Let me answer your questions thoroughly:

Originally Posted by TrevorL

0. "Most of our clients have failed to attach the database from the .MDF file in their application directory" - it sounds like this could still be worth a try. Please let me know the physical location of my site so I can give it a go, and I'll report back.

The complete physical path of your website on the server is : "D:\Inetpub\vhosts\domain.com\httpdocs\"
You can replace the domain.com with any of your domains hosted on the server.

Originally Posted by TrevorL

1. "Connect the SQL database remotely and create all the tables in it." - assuming you mean do this with Management Studio or similar in place of the Plesk WebAdmin route - I'll try this and report back.

Yes, this has to be done from the local machine using SQL Server Enterprise Manager, which is supplied freely with the MSSQL 2000 Connectivity Tools.

Originally Posted by TrevorL

2. "Create a backup up of your database on your local machine and upload the .bak file on the server and ask us to restore it for you." Thanks for the offer. However, the db will get upgraded a number of times over the next few weeks and months. Does this mean we would have to do this manual upload every time?

Yes, you'd need to backup your DB, compress it into a Zip file & upload it onto the server, notify us by placing a ticket or through live chat, we'd uncompress it & restore it into your database properly..

Originally Posted by TrevorL

I've just tried this (and I remember now I did try it over the weekend). I use SQL Server Management Studio Express and connect to mysql.preservedtanks.com and login in to my database using my SQL Server user id and password (i.e. the same as I used in Plesk to create the database). This shows me a list of databases called DURHAM\Databases; there are about a 100 shown so I imagine they are all the client databases on our shared server - but I only have permission to see the details in my databases. Within my ones I can see the available tables etc. However, if I Execute a query against my database to create new tables it just generates errors and no new tables are created.

Edit: I've looked into this in some more detail - some simple commands (e.g. CREATE TABLE) execute successfully, but the full script required to recreate my databases fails. I'm thinking it may be a SQL Server version problem. I'm using SQL Server 2005 Express at my end. Could it be that SQL Server 2000 or some other version is running on the server?

It's looking like this 'Method 1.' could work, but I'd still appreciate some feedback on the questions in my post above.

Yes, you are hosted on a server which has MSSQL 2000 Enterprise Edition installed on it. Hence you'd need to connect to the database server using Microsoft Query Analyzer [isqlw.exe] utility & execute/run the complete scripts from within it into your database...

PS: Opening a support ticket with windows@eukhost.com would provide faster responses/solutions..

[TrevorL]

Thanks for the response. I'll try out those ideas.

Meanwhile, what is the process to open a support ticket?

[eUKhost.com]

Originally Posted by TrevorL

Thanks for the response. I'll try out those ideas.

Meanwhile, what is the process to open a support ticket?

You can open a ticket from our helpdesk located at http://support.eukhost.com/ and let us know the ticket number over here.

[TrevorL]

Ok, thanks.

[TrevorL]

I've had a go connecting to SQL server remotely and creating the tables I need (with scripts). This worked for the MS Personal Website Starter Kit.

However, I'm now using an extended version of this starter website, and I'm getting errors that it is using incompatible types - i.e. it must be using SQL Server 2005 types which, of course, your SQL Server 2000 doesn't recognise.

Is there any chance my website could be transferred to a server running SQL Server 2005? If so, is there a charge? Nothing would actually have to be moved (I'm still setting it up) so the current stuff could be deleted and I'd just set up on the new server.

[eUKhost.com]

Originally Posted by TrevorL

I've had a go connecting to SQL server remotely and creating the tables I need (with scripts). This worked for the MS Personal Website Starter Kit.

However, I'm now using an extended version of this starter website, and I'm getting errors that it is using incompatible types - i.e. it must be using SQL Server 2005 types which, of course, your SQL Server 2000 doesn't recognise.

Is there any chance my website could be transferred to a server running SQL Server 2005? If so, is there a charge? Nothing would actually have to be moved (I'm still setting it up) so the current stuff could be deleted and I'd just set up on the new server.

You will need to submit a ticket for our Billing Department and ask them to setup a new account for you on a Windows server which has mssql 2005 express. They will get this done for you and you can ask them to delete your account from old server once your new account is setup.

You can open a ticket for Billing Department from http://support.eukhost.com