Visual Studio .NET - Connecting to SQL Express on Hosted Site

I recently registered my website to be hosted on GoDaddy.com.  I created a simple website with a SQLExpress Database.  Locally, it works great.  When I transfer my application to my hosted website, I get a remote connection error when trying to access any pages that require database access.
When I call GoDaddy.com, they simply say that they don't offer code support.  Does anyone know what I need to do to get this working?  HEEELLLLPPPP

Hi Richard,
I am also having my website hosted with GoDaddy.com.  Usually the problem is with the connection string.  Verify the connection string that you are using and this is not just the IP address of your website.  To know the server name to use, go to the Control Panel of your Hosting Manager.  Go to SQL Server and click on your user name.  You should be ale to see the Host Name and Database Name to use.

Hi Ronald,
Thank you for your reply.  I used the example from their help section.  Could you post a sample of a connection string that I could try?
Are you using their SQL database or are you using a SQL Express database?
Thanks a bunch.
Rich

<add name="AdventureWorksConnectionString" connectionString="Data Source=PCNAME\SQLEXPRESS;Initial Catalog=AdventureWorks;User ID=sa;Password=pwd**"
   providerName="System.Data.SqlClient" />

Thanks Aarthi.  
I'm sorry, I wasn't clear.  I do understand a basic connection string.  What I was trying to see is if there is something missing that is preventing me from connecting to a SQL Express Database on a hosted server (GoDaddy.com).  
When I connect locally, my connection works fine.  When I move my project up to the hosted website and I try to connect, I get the following error:
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)

Check out in the Surface Area config wizard if remote connections are allowed?

I did setup the SAC Wizard to allow remote connections.  My impression was that the Surface Area Config. Wizard was to open the TCP/IP connection for the PC running it.  I didn't think that it actually made any changes to the SQLExpress Database itself.  
The problem is that the database is being hosted at GoDaddy.com so I cannot affect any of the TCP/IP settings on their server.
I called them for help (5) times so far.  Unfortunately, they are clueless.

Daddy had a web-based interface where you could try and connect to their Sql server hosts..Something like a web based query analyzer.Do you have access to this?

Hi Richard,
What I am using is the SQL database and not the SQL Express database but it should almost be similar.  Make sure that the Initial Catalog you are using is the database name that they've provided (which is DB_ followed by a number) and that the Data Source is not the IP address of your website but the one that you get from the Control Panel of the Web Hosting.  The Data Source in my website is something similar to this:
"whsql-vxx.prodxx.mesaxx.secureserver.net"
This is not exactly what I have but changed some items for security reasons.

Hi Richard,
I am assuming you've seen the help file from GoDaddy.  Basically here's how the connection string in your web.config will look like:
<connectionStrings>
<add name="Personal" connectionString="
Server=whsql-v04.prod.mesa1.secureserver.net;
Database=DB_675;
User ID=user_id;
Password=password;
Trusted_Connection=False" providerName="System.Data.SqlClient" />
<remove name="LocalSqlServer"/>
<add name="LocalSqlServer" connectionString="
Server=whsql-v04.prod.mesa1.secureserver.net;
Database=DB_675;
User ID=user_id;
Password=password;
Trusted_Connection=False" providerName="System.Data.SqlClient" />
</connectionStrings> 
You can find your server name, database name, user ID, and password in the SQL Server section of your Hosting Manager. These connection string values map to host name, database name, user name, and password, respectively. The user name and password values are those specified during SQL database (not hosting account) creation.
Then after this, follow the steps mentioned in the "Adding Your "personal-add.sql" Schema to Your SQL Database".

I can connect via their web interface.  I can go in and add/update table structures, stored procedures etc. via the interface.   But I can't connect using my asp.net pages.  This is REALLY frustrating.  
They offer a SQL Database, they offer asp.net 2.0, but they don't offer any support on connecting to the SQL Server using ASP.NET.  I've used the example on help.godaddy.com site and still can't connect.

I have tried to do that.  I guess maybe you can clear up what is happening to me.
I have my website on my local PC and I want to test a page against the SQL Database on the GoDaddy Site.  I use the connection string as in the example on their help.godaddy.com.  I use the connection parameters just like you mentioned below.  But, when I debug the page, it says that the remote connection is not available.  
Next, I ftp my pages to the website which is hosted on GoDaddy.  I try to access the page and I still get the same error saying that the remote connection is not available even when I'm accessing the page on their server.
I feel like I'm doing everything I'm supposed to do - but I can't connect!  Ugghhhhh.
I have tried using their SQL Database and I've tried using a SQL Express database.  Regardless of which solution I choose, I get the same error message.

Hi Richard,
Can you please post your connection string when connecting to the SQL Database.  This will help us further in determining where the problem is.  For security purposes, just mask your user name and password.  As for the Data Source and Initial Catalog, you can change some of the values by adding extra characters but I just want to see if are using the correct values.
Also, make sure that the tables that you created are prefixed by dbo.  If you created your tables without including dbo, the tables will be created with the owner set to your user name.  This was one of the errors that I encountered before.

Hi Richard,
I would suggest that you try to create a simple page that will do a simple SELECT statement on one of your tables and have it displayed on the page.  Don't put any try/catch so that any errors will be displayed (of course assuming that you set it in your web.config file).
Also, if you have access to ASP.NET 1.1, try making the same page also and load this one to GoDaddy and see if this works.  If the same page works in ASP.NET 1.1 but not in ASP.NET 2.0, then you can tell them that they didn't set-up your web site properly.

Hi Richard
I have godaddy too... I can connect to my database hosted by godaddy.com that i created in the control panel under sql server.  Here is what i did:
1. open control panel in godaddy.com under runtime icon change it to 2.O
2. create your database in the control panel under SQL SERVER
3. go to your "web.config file" under connetion string  and add this code: Note: change all CAPITAL LETTER in the code to suit you need only. you can find your user, password,databasename,servername in the control panel under sql server at godaddy.com
      <connectionStrings>
    <add name="Personal" connectionString="
Server=YOUR_HOSTNAME;
Database=YOUR_DATABASENAME;
User ID=YOUR_USERNAME;
Password=YOUR_PASSWORD;
Trusted_Connection=False" providerName="System.Data.SqlClient" />
    <remove name="LocalSqlServer"/>
    <add name="LocalSqlServer" connectionString="
Server=YOUR_HOSTNAME;
Database=YOUR_DATABASENAME;
User ID=YOUR_USERNAME;
Password=YOUR_PASSWORD;
Trusted_Connection=False" providerName="System.Data.SqlClient" />
  </connectionStrings>
4. upload your file and it should work.
post back if you still have problems.
for the sake of peace. here is the copy of my WEB.CONFIG file exclude my personal data:
------------------------------------------------------------------------------------
<?xml version="1.0"?>
<!-- 
    Note: As an alternative to hand editing this file you can use the 
    web admin tool to configure settings for your application. Use
    the Website->Asp.Net Configuration option in Visual Studio.
    A full list of settings and comments can be found in 
    machine.config.comments usually located in 
    \Windows\Microsoft.Net\Framework\v2.x\Config 
-->
<configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">
	<appSettings/>
  <connectionStrings>
    <add name="Personal" connectionString="
Server=XXXXXXXXXXXXX;
Database=XXXXXXXX;
User ID=XXXXXXXXX;
Password=XXXXXXXX;
Trusted_Connection=False" providerName="System.Data.SqlClient" />
    <remove name="LocalSqlServer"/>
    <add name="LocalSqlServer" connectionString="
Server=XXXXXXXXXXX;
Database=XXXXXXX;
User ID=XXXXXXXX;
Password=XXXXXXX;
Trusted_Connection=False" providerName="System.Data.SqlClient" />
  </connectionStrings>
  <system.web>
		<!-- 
            Set compilation debug="true" to insert debugging 
            symbols into the compiled page. Because this 
            affects performance, set this value to true only 
            during development.
        -->
		<roleManager enabled="true" />
  <compilation debug="true"/>
		<!--
            The <authentication> section enables configuration 
            of the security authentication mode used by 
            ASP.NET to identify an incoming user. 
        -->
		<authentication mode="Forms" />
		<!--
            The <customErrors> section enables configuration 
            of what to do if/when an unhandled error occurs 
            during the execution of a request. Specifically, 
            it enables developers to configure html error pages 
            to be displayed in place of a error stack trace.
        <customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm">
            <error statusCode="403" redirect="NoAccess.htm" />
            <error statusCode="404" redirect="FileNotFound.htm" />
        </customErrors>
        -->
	</system.web>
 <system.net>
  <mailSettings>
   <smtp from="YOURNAME@YOURDOMAIN.COM">
    <network host="smtpout.secureserver.net" password="XXXXXX" userName="YOURNAME@YOURDOMAIN.COM" />
   </smtp>
  </mailSettings>
 </system.net>
</configuration>

Now THAT is what I call helpful.  I'll give it a try when I get home tonight.
I just wish the folks at GoDaddy were able to support us with this issue as easily as you did.
I appreciate your help with this.  I'll let you know later tonight.
Regards,
Rich

Hi James.
Thanks for your web.config.  I appreciate your help.
The part that has me totally confused:
1.)  On my local PC, when I created my app. an ASPNET SQL Express database was automatically created and placed in the App_Data folder.  Am I using that database, or do I remove it?
2.)  If I point my web.config file to the SQL Server hosted by GoDaddy.com (which is a SQL 2000 db), do I need to run some kind of script to add all of the table structures that are automatically created in my SQL Express database that I mentioned above?
3.)  Will I be able to test pages locally pointing them to the SQL Server on GoDaddy? or do I need to ftp my pages to the server before I can test them?
THANKS AGAIN!  I look forward to your reply.
Regards,
Rich

Hi Rich
Im going to try answer your 3 questions:
"1.) On my local PC, when I created my app. an ASPNET SQL Express database was automatically created and placed in the App_Data folder. Am I using that database, or do I remove it?" 
"2.) If I point my web.config file to the SQL Server hosted by GoDaddy.com (which is a SQL 2000 db), do I need to run some kind of script to add all of the table structures that are automatically created in my SQL Express database that I mentioned above?" 
"3.) Will I be able to test pages locally pointing them to the SQL Server on GoDaddy? or do I need to ftp my pages to the server before I can test them?" 
----------------------------------------------------------------------------------------------------
A1.) The ASPNETDB SQL EXPRESS created by your local pc will not work with the "web.config" script that I posted on my previous post. So its up to you if you want to remove that ASPNETDB or not.
A2.) The "web.config" script on my previous post will only work with the SQL SERVER database you created in godaddy.com(which is your SQL_2000DB). For your tables you have to create them in that (sql_2000db). There are already made tables in that sql_2000db, you can add more if you want.
A3.) After you add the "web.config" script from my previous post to your project on your local pc then you have to upload your project on your godaddy server to test them. You can not test them locally, it will not work.
For ASPNETDB.....I myself having trouble with it. I cant get it running when i upload my project. It works perfectly when i debug it locally. But im on the hunt to look for the solution for it. and when i find it i will post it here for everyone...heck I will post it all over the internet....hehehehe :-)
Post back if you have more questions
James

It's exaclty what I thought you were going to say. 
After I sent off the email last night I saw that the SQL DB on GoDaddy actually has all of the supporting tables.
I guess the big dis-join for me was "How do I test things locally against their remote database?"  You answered that by saying that you port your app. up to the server, then test it.   I wish there were a way to use our SQL Express database locally AND on the server.  That's where I was having the trouble.
Again, thanks for your help with this.  I'm sure we'll cross paths again someday.
Regards,
Rich

James.  With your help, I was able to connect to my database that is on GoDaddy.com.
Has anyone found a way to work on a project locally and debug it?  With the datasource being on GoDaddy's server, I can't find any way to do testing.  I literally have to write the code, then open the page.  I can't find any way to debug.
Anyone?

Richard, 
I had a very similar problem while working with the Club Website Starter Kit and Godaddy. Eventually, I found this link
http://www.aquesthosting.com/HowTo/Sql2005/SQLError26.aspx
Basically, you have to configure your providers to look for the correct database. I pasted the XML in the above article, changed the name of the connectionString and it works like a charm. Also, you'll have to remove the old definitions of roleManager and memberships, or you'll get "duplicate definition" errors.
Regards,
-Jonathan
Here's the XML just in case the above link goes away:
<membership>
      <providers>
         <remove name="AspNetSqlMembershipProvider" />
         <add name="AspNetSqlMembershipProvider"
           type="System.Web.Security.SqlMembershipProvider, 
           System.Web, Version=2.0.0.0, Culture=neutral,                                 
           PublicKeyToken=b03f5f7f11d50a3a"
           connectionStringName="LocalSQLServer"
           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="LocalSQLServer"                            
             applicationName="/"
             type="System.Web.Profile.SqlProfileProvider,
             System.Web, Version=2.0.0.0, Culture=neutral,                    
             PublicKeyToken=b03f5f7f11d50a3a" />
        </providers>    
   </profile>
   <roleManager>
        <providers>
          <remove name="AspNetSqlRoleProvider" />
          <add name="AspNetSqlRoleProvider" 
             connectionStringName="LocalSQLServer" 
             applicationName="/"
             type="System.Web.Security.SqlRoleProvider, 
             System.Web, Version=2.0.0.0, Culture=neutral,                                
             PublicKeyToken=b03f5f7f11d50a3a" />
        </providers>
   </roleManager>