[TrevorL]

Issues Accessing SQL Server Databases

I have a couple of website hostings on eUKHost, including a simple www 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 Server 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 PersonalWebsite Hosting Starter Kit (PWSK).

I downloaded Web Express, built the PersonalWebsite Hosting 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 website hosting. 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: Recipe: Deploying a SQL Database to a Remote Hosting Environment (Part 1) - ScottGu's Blog).

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 web site hosting 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.

Help on www asp