Database connection problem

Wilfredo Rosado · June 30th, 2010, 02:31 AM

Hi Imar,

My login page works fine while running it local. When I publish my changes to the host server it fails with the following error:

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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

I enabled remote connections on the SQL 2005 server as per the book's instruction and I still get the same error. I believe it may be with my firewall. I have my own webserver sitting on DMZ and the SQL 2005 in my local LAN, both connected via firewall hardware. I may need to allow the SQL's port to pass from DMZ to local LAN, but I do not know which port the SQL 2005 is using. Any ideas?

Fred Rosado

Imar · June 30th, 2010, 02:40 AM

Hi Fred,

Typically, SQL Server requires port 1433.

But are you sure it's a port issue? What's the name of your SQL Server instance and how does your connection string look?

Imar

Wilfredo Rosado · June 30th, 2010, 02:53 AM

Hi Imar,

No I am not sure, just a shot in the dark. My connection string is as follows:

Code:

  <connectionStrings>
    <add name="Home-RemakeConnectionString1" connectionString="Data Source=ascus-svr6;Initial 
      Catalog=Home-Remake;Integrated Security=True" providerName="System.Data.SqlClient" />
  </connectionStrings>

I did not use SQLExpress while working on this project, I used SQL2005. My database is on SQL2005 and in local mode all database reference was done on my SQL2005.

My SQL2005 instance is MSSQLSERVER as per the SQL Server Configuration Manager.

Does any of this help?

Fred

Imar · June 30th, 2010, 03:18 AM

Quote:

My SQL2005 instance is MSSQLSERVER as per the SQL Server Configuration Manager.

Then shouldn't use machinename\instancename in your connection string? E.g.:

Data Source=ascus-svr6\MSSQLSERVER

Can you connect to the database using SQL Server Management Studio? If so, how?

Imar

Wilfredo Rosado · June 30th, 2010, 04:47 AM

Hi Imar,

I performed Appendix B instructions to make sure the databases were in SQL2005 and the aspnetdb was not so I attached as per instructions. I also modified the connection string as follows:

Code:

<connectionStrings>
    <clear />
    <add name="Home-RemakeConnectionString1" connectionString="Data Source=
      ascus-svr6\MSSQLSERVER;Initial Catalog=Home-Remake;User ID=******;password=*******" 
      providerName="System.Data.SqlClient" />
    <add name="localSqlServer" connectionString="Data Source=ascus-svr6\MSSQLSERVER;Initial 
      Catalog=aspnetdb;User ID=******;password=*******" providerName="System.Data.SqlClient" />
  </connectionStrings>

This time the local mode got the same error. I removed \MSSQLSERVER from the local <add> line's data source, and local mode now works again. However I am still getting the same error message in remote mode.

It's 4:34 AM my time, and I am again too tired to think anymore. I will try it again tomorrow. thanks for all your quick responses.

Fred

Imar · June 30th, 2010, 04:56 AM

Are you trying to let your remote host connect back to your SQL Server on your local machine? How are the two machines connected? A LAN?

Imar

Wilfredo Rosado · June 30th, 2010, 01:45 PM

I have my own LAN with a router/firewall hardware unit. Internet comes into this unit. This unit also has a DMZ LAN and a local LAN. The two LANs are separated and control by the policies on the router/firewall unit.

My webservers are in the DMZ LAN, while my SQL2005 is on a Windows 2003 server in the local LAN and the SQL Express is on my Windows XP desktop also on the local LAN.

What I am trying to do is to let my remote host (in the DMZ) connect back to my SQL2005 server in my local LAN. Everything is here at my site but on two separate LANs. The firewall controls communication between the two LANs.

Is this what you are asking?

Fred

Imar · June 30th, 2010, 02:10 PM

Yes, that clarifies things a bit.

This is a bit too off-topic for me to support and help fixing over a foum, but I'll try...

It looks like a network or configuration error. Try opening all ports on the firewall temporarily (or disable it) and see if that fixes it. If it does, you need to open the right ports. At least 1433 and maybe 1434 as well.

Also check configuration settings of SQL Server and see if it suppports remote access and TCP.....

Cheers,

Imar

Wilfredo Rosado · June 30th, 2010, 02:58 PM

Ok...

My firewall was set to allow TCP port 1433 and UDP port 1434 to pass through from DMZ to Local LAN. This gave me the error I originally talked about. I changed the firewall (temporarily) to allow all ports to pass through from DMZ to Local LAN and I still get same error message. I am able to see the DMZ computers from the Local LAN and vice versa, which tells me they are able to communicate pass the firewall. Maybe it is a SQL server config problem.

I followed the steps on page 690 to enable Remote Connections in SQL Server via the "SQL Server Configuration Manager". For the "Protocols for MSSQLSERVER": Shared Memory, Named Pipes and TCP/IP are enabled, while the VIA option is disabled. This should indicate that the SQL server is set to allow remote connections, no/yes?

My two databases used for this project are Home-Remake and aspnetdb, both are found in SQL 2005 Server Manager. I have set a user name in both with passwords as per the pages (691-693) on attaching databases.

The thing I'm not so confident in is which authentication method to use. Currently I used the SQL Server Authentication. But this is my concern:

My IIS server is running on my webserver#1 in the DMZ, while the SQL2005 is on another server (SQLServer) in my local LAN. This scenario is not listed on page 694 and it may be what I need to set up for.

What do you think?

Fred

Imar · June 30th, 2010, 03:13 PM

From a security point of view, it should be the same; it's the network that's making it a problem here I think. The machines can't reach each other, or at least can't communicate over the configured ports or protocols.

Like I said, this s pretty difficult for me to do over a forum. It may be a 1-minute fix when I sit behind your machine, but not being able to try things out and see how it behaves makes this extremely difficult and time consuming.

You may want to Google for some network analyzing tools and see if you can find out what's going on.

Also, you may want to try connecting to the machine using its IP address ratther than it's network name.

Cheers,

Imar