I created a dataset to contain all datatables used in my RDLC reports. There were no problems running on my development machine, however when running reports in client machines, following error was encountered.
System.Data.SqlClient.SqlException: 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)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
.
.
.
Kindly note that connection to SQL Server is fine. Error is generated only when running RDLC reports so I suspected it has something to do with my dataset.
I tried to set the following for my dsReports.xsd:
Build Action : Embedded Resource
Copy to Output Directory: Copy Always
Same problem. Inspecting the Full Path it is pointing to local folder and I cant change it since it is readonly.
Full Path: D:\dcs\dsReports.xsd
Did I created a local dataset in this case? When I inspect a TableAdapter connection string in one of datatables, it is showing:
Connection String: Data Source=connector\sqlexpress;Initial Catalog=dcsdb;User ID=qa
connector here is the host of my SQL Server so I guess server connection is ok. Certainly this had something to do with my dataset. I will post the complete error log if necessary. Hope you guys point me in the right direction. Thanks.
Finally I was able to trace the root of the problem. Everything stated above are correct. Root of the problem lies on Windows Hosts File. I had to modify this file from the client computer to map SQL Server.
System32 > drivers > etc > hosts
and add the following lines pointing to my SQL Server
10.x.x.x <server-name>
This is entirely a networking issue.