So after writing the last post, I thought my SQL connection problems were solved.  After all, I could remotely connect to my server through SSMS, but then when I actually tried to connect through my asp.net web application, I got the dreaded:

"A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) "

I googled and messed around with my new server for 6 hours.  I got so desperate to figure out why I couldn't connect to my new server that I signed up for some cheapie SQL 2008 hosting, attached my database, and tried to connect to that one.  Same exact issue.  I had played around with IIS security, file and folder security, SQL permissions, asp.net permissions, but nothing worked.  I read the message....it says it can't find my server..hmm..  So I set a breakpoint in my code and ran in debug mode.  I looked at the datacontext and bingo!!!  I don't know why I hadn't thought of this earlier.  I have three seperate class libraries in my web app that use Linq to Sql.  I never pay attention to how it actually works, I just drag on my tables and go.  But I discovered that it was using a connection string that was located in my application settings file that was NOT the correct connection string!  It was using the connection that I set up during development.  So obviously I need to change the connection string, but what a pain that would be.  Seems like there should be a better way.  After all, you need to move your site through development, testing, staging, and production, so you would need to change these connections.

Well, here is the trick:

1.  Open up your dbml designer and look in the properties windows.  Expand Connection.  Change Application Settings to "False". 

2.  Next to Connection select "(None)" from the drop down.  This will clear out the connection string that was being used from your Application Settings.  You can delete the Settings.cs file under Properties too now and also the App.Config.

3.  If you compile, you'll get errors.  This is because you need to somewhere pass in a connection string.   To do this, create a partial class in your class library.  

In this class, you are pointing to a connection string that is located in your web.config file.

That's it.  Do this for all your class library projects and they will all point to a single web.config located in your web project.   I must give credit to Rick Strahl, whose blog I read regulaly.  He got me thinking about Linq to SQL and connection string when reading his blog this afternoon.  Much headbanging and pulling my hair out and it was something as simple as trying to connect to the wrong server!   I really hope this helps someone.