>>>>>>I have an ongoing problem that I have tried and tried to resolve.
>>>>>>
>>>>>>A coworker and I are developing a VB 2008 desktop application using SQL Server 2008 Developer's Edition as a backend. When we install the finished application, it will use SQL Server 2008 Express locally on the same computer.
>>>>>>
>>>>>>At the moment, we have SQL Server 2008 Dev Ed installed on an XP Pro workstation (which is not used for anything else) so we can both make changes to the database as we develop our application.
>>>>>>
>>>>>>This normally works great, but intermittently - for no apparent reason - one of us (or the other, or both) cannot connect. We get the dreaded "a network related or instance specific error occurred while establishing a connection to sql server...." error. I have googled this and researched it and made sure all the standard causes of this error are addressed (firewalls turned off, allow remote connections turned on, TCP/IP turned on, network adapter drivers updated, etc, etc). And the thing is, the connection often works great! But several times a day, it just won't connect. And I may have connected just fine 2 minutes before. Then, ten minutes later, its all working again..... sometimes I restart and its back working.... sometimes my first attempt after rebooting fails.... sometimes disabling my network connection and then re-enabling it makes the problem go away.... sometimes this does not help. If I try and look at a shared folder on that PC with SQL Server when SQL Server refuses to allow a connection, there seems to be no problem (so its not like the workstation with SQL Server has disconnected from the network or gone into power save mode or something - its fine and I can still access its shared folder on the network). When SQL Server refuses to allow me to connect, it doesn't matter how I connect - either using our application or using SQL Server Management Studio - it just absolutely will not let me connect... then another time, it will, without a problem.... this makes no sense!!
>>>>>>
>>>>>>Needless to say I just want to scream! Though thats not likely to help - it might make me feel better! ;o) This just seems so flaky... especially for a database Microsoft seems to really be pushing us all to use! There just does not seem to be any pattern to this connection issue.
>>>>>>
>>>>>>Anyone else see this? Or anyone have a suggestion?
>>>>>
>>>>>IIRC, XP Workstation imposes a low limit on the number of connections (OS not SQL enforced)
>>>>>IAC, how is your app creating/releasing connections?
>>>>
>>>>Thanks for the info - I was wondering about connection limits since its the Developer Edition of SQL Server, but I had not thought about the limits of XP Pro.
>>>>
>>>>After some research, I believe XP Pro limits the number of connections to 10.
>>>>
>>>>However, after thinking this could be it, what happened today leads me to believe this is not the problem. Today I did some testing without using our application at all - just connecting with SQL Management Studio. My co-worker is off this week, so I was the only one trying to connect. First attempt this morning - refused to connect. Five minutes later - no changes - worked fine. Later in the day - after I had worked on a few other things unrelated to SQL - could not connect. Disabled my network adapter, waited 10 seconds, re-enabled and connected right away. I also ran a query on the master database after some googling about connections:
>>>>
>>>>SELECT DB_NAME(dbid)AS ConnectedToDB,
>>>>hostname, program_name,loginame,
>>>>cpu, physical_io, memusage, login_time,
>>>>last_batch, [status]
>>>>FROM master.dbo.sysprocesses
>>>>ORDER BY dbid, login_time, last_batch
>>>>GO
>>>>
>>>>This confirmed to me I was the only one connected - so thats 1/10 possible connections.
>>>>
>>>>Still stumped!! Thanks again for your input though!
>>>
>>>So are you sure the problem isn't exactly as stated in the title i.e simply a hardware or network configuration problem and not specifc to the SQL server ?
>>>Regards,
>>>Viv
>>
>>I am pretty sure its just SQL Server - when SQL refuses to connect, I can still connect to that XP Pro computer and read/write files in any of its shared folders. Also, everything else that uses our network continues to work without a problem or even a delay. For this reason, I have thought its completely SQL Server specific - but maybe I am missing something?!
>
>Just a thought :are you using Windows or SQL Server Authentication?
>I came across a similar problem once with Window authorisation. The problem then turned out to be the length of time it took for the domain server to authenticate the user.........

SQL Server Authentication... we couldn't get Windows Authentication to work (though I did not spend a lot of time on Windows Authentication). We do not use domains on our network.