Need Help connecting to database in WCF

[fredziff]

I have a WCF service that connects to a database on my local machine. It works great, however I get an error when invoking the service from my website on DASP.

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)

Server stack trace:
at System.ServiceModel.Channels.ServiceChannel.ThrowI fFaultUnderstood(Message reply, MessageFault fault, String action, MessageVersion version, FaultConverter faultConverter)
at System.ServiceModel.Channels.ServiceChannel.Handle Reply(ProxyOperationRuntime operation, ProxyRpc& rpc)
at System.ServiceModel.Channels.ServiceChannel.Call(S tring action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs, TimeSpan timeout)
at System.ServiceModel.Channels.ServiceChannel.Call(S tring action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs)
at System.ServiceModel.Channels.ServiceChannelProxy.I nvokeService(IMethodCallMessage methodCall, ProxyOperationRuntime operation)
at System.ServiceModel.Channels.ServiceChannelProxy.I nvoke(IMessage message)

Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.RealProxy.HandleRe turnMessage(IMessage reqMsg, IMessage retMsg)
at System.Runtime.Remoting.Proxies.RealProxy.PrivateI nvoke(MessageData& msgData, Int32 type)
at IKidtracksService.LogonSuccess(String Email, String Password)
at KidtracksServiceClient.LogonSuccess(String Email, String Password)

I am using the wcftestclient.exe to test the service. The service is running in a subdirectory of the root website and the application has been installed there.

The database connection works on my VS2008 WCF Service locally, but I cannot get the connection to work on the DASP Server.

Any Ideas?

Thanks

[fredziff]

Here is my connection string in the WEB.Config:

<add name="DBName" connectionString="Data Source=tcp:sql2k803.discountasp.net;Initial Catalog=SQL2008_1234_MyDB;User ID=SQL2008_1234_MyDB_user;Password=MyPW;"
providerName="System.Data.SqlClient" />
</connectionStrings>

And in my APP.Config in my Service:

<connectionStrings>
<add name="MyServiceName.My.MySettings.MyServiceNameConnectio nString"
connectionString="Data Source=tcp:sql2k803.discountasp.net;Initial Catalog=SQL2008_1234_MyDB;User ID=SQL2008_1234_MyDB_user;Password=MyPW;"
providerName="System.Data.SqlClient" />
</connectionStrings>

Where is this connection supposed to be? Does it need to be in both files? I think is should not be in the WEB.Config.

I look in the activity moniter at DASP on my database and there is an entry that implies the WCF service is hitting the database:


Process#: 121 SQL2008_1234_MyDB_user master runnable 0 SELECT .Net SqlClient Data Provider 0 NOT WAITING 0 2 10/26/2011 2:01:33 PM 10/26/2011 2:01

The TIME changes for each access attempt but no data is sent back. This works with the same code just fine in MS VS2008.

I don't understand. Is this a configuration issue on DASP? The above web.config file is in a subdirectory from the ROOT of my DASP website. The webpages on the root access and return data just fine. But the subdirectory with the application installed with a WCF service does not return data, and the activity monitor inplies that the database is being accessed, but the error message says it is not found. ???????

[fredziff]

http://blogs.msdn.com/b/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx

This article suggests that the error is very specific, Error Locating "Server/Instance" Specified. So.... is it just that my connection string is wrong. If so why does the Activity Monitor for my online database show that it has been accessed by a SELECT command? Why does my connection string in the web config it the root directory work for the website? This web config file is in the subdirectory that contains the WCF Service, and the connection is identicle to the string in the root directory. What is the syntax I am missing?

[Tasslehoff]

My only suggestion would be to double check that the code in your application is referencing the connection string in the web.config correctly.