SQL Physical Connection is not usable

Question

My application moved to a new DEV SQL Server and I'm trying to update my EF model. The database through Management Studio 2014 is able to connect (though truthfully, the connection has had some interruptions today). However, i'm trying to update my EF model by adding 2 tables to it through the "Update Model From Database" (so obviously not using code first here). I get the error:

SQLException: A transport-level error has occurred when receiving results from the server. (provider: Session Provider, error: 19 - Physical connection is not usable).

Sometimes, it errors on the first screen (Reading database information, please wait..., and times out). Sometimes, I can get through the entire Update Wizard and click Finish, only to encounter the problem then. Most errors related to this have occurred at runtime; I haven't found anything explicitly for fixing this problem at design-time... Any ideas? Maybe I need a permission on the database?

Solution 1

You indicate that 'the connection has had some interruptions today' which indicates that there's something wrong between your client and the SQL box - I'd focus on resolving those issues before trying to work on your model.

The error above generally indicates that there's something wrong with the connection to the database server.

I'm assuming that the Dev SQL Server is running on a Windows Server OS? If not, you may well be running into the limits on the number of simultaneous incoming TCP connections imposed upon desktop versions of Windows. This is often exacerbated if you are one of several developers trying to connect to the Dev SQL box.

Other reasons you may see this issue include:

1. Unreliable connectivity between your code and the SQL box. Check cabling, switches, etc.
2. The SQL machine may be unreliable - check event log for unexpected reboots, HDD issues, etc.
3. The SQL machine may have been rebooted while you were trying to reconnect.
4. The SQL machine may have been under maintenance from your ops team?
5. The SQL machine may have been applying updates?
6. If the machine is a SQL cluster (unlikely for Dev server), it may have been failing over.

Solution 2

I'm writing my solution here for anyone that might have run into a variation of this problem. Today I was not able to connect to our staging environment at work (I work from home) but my colleagues were (they work from their homes also).

After much researching and trying random things I reset my modem which fixed my problem. I was getting the same sql error. Not sure of the exact cause but if this helps anyone else then great.