Need Help connecting to database in WCF

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.ThrowIfFaultUnderstood(Message reply, MessageFault fault, String action, MessageVersion version, FaultConverter faultConverter)
at System.ServiceModel.Channels.ServiceChannel.HandleReply(ProxyOperationRuntime operation, ProxyRpc& rpc)
at System.ServiceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs, TimeSpan timeout)
at System.ServiceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs)
at System.ServiceModel.Channels.ServiceChannelProxy.InvokeService(IMethodCallMessage methodCall, ProxyOperationRuntime operation)
at System.ServiceModel.Channels.ServiceChannelProxy.Invoke(IMessage message)

Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)
at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(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

 

The same connection string works on my website but not in the service

Here is my connection string in the WEB.Config that is located in the WCF service subdirectory:

<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.MyServiceNameCon nectionString"
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. ???????

http://blogs.msdn.com/b/sql_protocol...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?

 

Found my error

Thanks for letting me talk to myself on this blog... it helped, LOL.

I accessed the connection in my service code with a connection string name that didnt match the name in the Web.config file.

In the Service Code...

Using cnn As New SqlConnection( _
MyServiceLibrary.My.Settings.MyConnectionString1)
Using cmd As New SqlCommand( _
"Select Username, Password " & _
"FROM Customers " & _
"Where Username = @username AND Password = @password", cnn)


And.... in the Web.config...


<add name="MyConnectionString2" 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>

Only one question remains... does this mean I only need the connection string in the web.config? Should I remove the connection string from the app.config in the service?



OOPS!

Thanks