Database Access across servers or Firewall Issue

Hi All,

I am facing a strange issue with accessing data from SQL server 2000. We are using SQL Server authentication for connecting to SQL Server 2000 and ADO .Net SQL client in the application. The problem here is, we have databases installed in one of the servers in some other office location that is in different state but on same domain. We do development from two locations that is one from Virtual Machines hosted on servers where Database Servers are located and on our local desktops in India.

Now when I developed a test client in Virtual Machines which will call a web service to get datasetof some data and show them in DataGrid. When I run the test client in debug mode from Visual Studio 2003, then I am getting data in the DataGrid. Even when I just run the exe from Virtual Machine still I am getting data.

But when I copy debug folder of the test client to the local desktop in India and when I run the Test client I am not getting any data loaded on to the DataGrid. I am getting an empty dataset when I am running from the local desktop.

Also I have converted the VS2003 project to VS2005 and when I run the application from Local Desktop then I am getting the data loaded fine. I could not understand what is the problem, what is the difference between accessing data from VS2003 and VS2005 using webservice.

Is this a firewall issue or I am missing any thing here. The webservice method I am calling to get data is actually returning dataset when I am calling this method directly from the browser also and even through application in virtual machine.

I am still not able to come to terms with this problem. This is severely affecting our deployment. Please help us resolve this problem. Please let me know if you need more information.

Thanks,

Venkatesh

Hi Venkatesh,

From what I understand, you have 3 components in your application system. There's the SQL Server, some client code, and a web service.

The SQL Server is in the same domain as the Virtual Machines that you are developing in. Your local desktops in India are in the same domain or a different domain than the SQL Server machine?

Since you are using SQL Server user authentication, the cross domain connectivity should not be an issue. You can verify this by running osql.exe or Query Analyzer tool on your local desktop connecting to the SQL Server machine using the same SQL Server user credentials as your client application.

As for the test client not seeing any data from the web service, I think it would help if you can mention how the web service is connecting to SQL Server and how the ADO.Net application is retrieving data from the web service. I can see the web service using ADO.Net (SqlClient) to connect to the backend SQL Server. Then, some front end client application built using the web service's WSDL document.

I think it will help people understand your application setup and provide possible solutions, if you can specify:

What is the web service using to connect to SQL Server (SqlClient)? Is the web service running on the same machine as SQL Server? If the web service is running on a different machine, is there a connectivity issue between the web service and SQL Server? Is the web service connected to SQL Server using SQL User credentials or Integrated authentication? Does the client application require to send user credentials to connect to the web service?|||

Sorry for the delay in response,

1. Web Service has Business Logic DLL reference which uses SQLClient ADO .Net commands to connect to database.

2. Web Service and SQL Server are on different servers but on the same domain.

3. There is no connectivity issue between the two machines because when I access same WebService through Web Browser, it works fine.

4. Web Service is connecting to SQL Server using SQL User credentials.

5. The User credentials are configured through WebService's Web.Config file.

I really have no clue about this issue. The issue just lies between the Windows Client and Web Service. Because when I access WebService directly through Web Browser then the Web Method is returning data, that means Web Service is getting the data fine but it is not able to give it to the Client.

FYI this issue occurs only on Windows 2003 Server if I install the same service on Windows 2000 server it works fine.

Anyhelp will be greatly appreciated.