0

I have a SQL Server 2008 database on my desktop that I'd like to access on a project developped from my laptop.

I tested the connection to the database from the laptop using the Server Explorer window (Data Connection > Add connection) and everything runs fine. The database is found, and I can access my tables and see the data insde.

I then added an ADO.NET Entity Data Model (= Entity Framework). The integration worked fine: it created the correct classes, I can edit the model, update it regarding the modification I'll pass from the server, ...

Then when I run my project, it needs to read a few data using the classes created by Entity Framework, but it crashes:

Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgment. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=63057;handshake=2;

I looked a bit and found some people having issues with the TCP/IP configuration from the Sql Server Configuration Manager, as explained here. Made the modification, restarted all my services, but it didn't fix my issue.

Does anybody have an idea on how I can fix this (note that I tried with no firewall so it seems not to come from this). Also, if helpful, take into consideration the folowing:

  • the laptop connects through a wifi router (internet box)
  • the project is hosted on the server hardrives
  • the project runs well from the server
Community
  • 1
  • 1
samuel guedon
  • 575
  • 1
  • 7
  • 21
  • First be certain that the connection string used by the project is correct. Projects are able to use a 'lightweight file' type database and it might be trying to connect to this by default rather than your server – Nick.Mc Jan 27 '15 at 23:53
  • The connection string is automatically generated when inserting the ADO.NET Entity Data Model, after testing the connection, so I have no reason to doubt it : __ it anyway works if I run the project from the server. – samuel guedon Jan 27 '15 at 23:58
  • So in this connection string, data source is set to the name of your desktop SQL Server? (not, for instance, localhost?) – Nick.Mc Jan 28 '15 at 00:44
  • @Nick.McDermaid Yes. data source is server\instance. it's anyway automatically generated from the laptop after connecting to the database. Also, it works when run from the server. – samuel guedon Jan 28 '15 at 21:14
  • Just confirming, because there is a lot of confusion around the 'local attach' type connection generated by visual studio (as per the answer suggesting that you detach the DB) From your description I'm running out of ideas. Can you test with a UDL file and see if that sheds any light on it? http://www.sophos.com/en-us/support/knowledgebase/65525.aspx – Nick.Mc Jan 28 '15 at 22:46
  • when i test like that it works fine. By the way, it's pretty much the same thing than creating a data connection from the server explorer in Visual Studio, which works too. – samuel guedon Jan 28 '15 at 23:14
  • Yes it's a connection string like any other, you're just using a different tool to do it.I guess the provider name would be different right? Can you edit the UDL in a text editor and make it look like your VS one? If you have a problem it must be the provider, and perhaps, yes, the provider by default uses named pipes instead of TCP/IP or something like that. – Nick.Mc Jan 29 '15 at 01:14
  • The provider is indeed different. If I put the VS provider (System.Data.SqlClient) on the .udl file, the .udl file can't find the provider (Provider cannot be found. Ensure that the provider has been installed properly). Same if I do the opposite (Unable to find the requested .Net Framework Data Provider. It may not be installed). I understand that they can each works with their own provider but not the other.Also, I don't have any other data provider available for Entity Framework. – samuel guedon Jan 29 '15 at 07:57
  • About the Named Pipe, they are enabled on the server : \\.\pipe\MSSQL$[instance]\sql\query – samuel guedon Jan 29 '15 at 07:58
  • It's still difficult to work out the issue. This link says that ther e might be a few DEV web server sessions open causing the issue. http://stackoverflow.com/questions/3270199/a-connection-was-successfully-established-with-the-server-but-then-an-error-occ/3270243#3270243. When you see this failure is it in 'debug' mode? What about when the web app is actually 'deployed' into IIS on your laptop? – Nick.Mc Jan 29 '15 at 08:28
  • Oh , and it is very rude to double post. http://stackoverflow.com/questions/28006136/issue-accessing-database-on-my-network-connection-timeout. In the other question you have included detailed information that could have saved us both a lot of wasted time. – Nick.Mc Jan 29 '15 at 08:29
  • [See this post for an answer][1] or if an admin can delete this one, thanks. [1]: http://stackoverflow.com/questions/15488922/connection-to-sql-server-works-sometimes – samuel guedon Feb 25 '15 at 23:53
  • [Answer here][1]. If an admin can delete this post, its doubled. [1]: http://stackoverflow.com/questions/28006136/issue-accessing-database-on-my-network-connection-timeout – samuel guedon Feb 25 '15 at 23:54

2 Answers2

1

I found the solution by checking on the option offered in the "Advanced" part of the connection wizard. The Network Library (TCP/IP, Named Pipes, Shared Memory or VIA) can be forced, while it is blank by default (and I guess auto chosen to a wrong option).

I tried all of them, and only the "Named Pipes" worked for me.

The modification can be done manually in the connection string by adding

;network library=dbnmpntw
samuel guedon
  • 575
  • 1
  • 7
  • 21
0

Make sure before you run the project that you have detached the database. Do this by right clicking on the database, which is located in the AppData folder, and click on Detach

Ken Y-N
  • 14,644
  • 21
  • 71
  • 114
Kirtan Parmar
  • 5
  • 1
  • Confusion reigns....... he is trying to attach to a remote database. Why would you detach the remote database? Then he definitely will not be able to connect to it!!!! Why upvote something that is so misleading?!! – Nick.Mc Jan 28 '15 at 05:01
  • As said by @Nick.McDermaid I guess I wrongly explained my issue somewhere. I don't have anything to detach as the db is not attached at all to my project. Entity Framework, as far as I understand, only creates a model mirroring the database into classes (ex: if I have a "region" table it will create a "region" class that access the data). The db itself remains on my server. – samuel guedon Jan 28 '15 at 23:20
  • No you explained it fine...the confusion arises because under various circumstances, Entity Framework uses a connection string to attach to a local MDF file (i.e. the string contains `AttachDbFileName` in it), rather than connecting to an external SQL Server. Most developers are familiar with this and so try to incorrectly troubleshoot issues around this type of connection string. For _this_ question in particular, we do _not_ want to attach to a local MDF file, we want to attach to an external server. – Nick.Mc Jan 29 '15 at 01:20
  • Here's a good example of the two completely different conections strings: http://stackoverflow.com/questions/7687930/db-connection-string-in-web-config-to-use-attached-mdf-database-wont-work. This answer is trying to troubleshoot the first `AttachDbFilename` one, but the question is about the second `Data Source` one. – Nick.Mc Jan 29 '15 at 01:23