I know there have been many posts made on this subject; however, despite the resources available, I have not been able to get this to work! I've spent time working on this here and there over the last couple of months only resulting in frustration. I am finally posting to solicit help for my situation to figure out what is wrong with my SQL Server or firewall configuration.
Two machines connected to a router, one wireless, one wired. Both have SQL Server Express and the Management Studio tools installed. From either machine I can connect to its local SQL Server Express instance.
I have enabled Shared Memory, Named Pipes, TCP/IP, and VIA in the SQL Server Configuration Manager. (BTW what is VIA?)
I have added sqlsrvr.exe and sqlbrowser.exe to my Windows Firewall exceptions. I have tried disabling Windows Firewall. One of the machines is configured as DMZ in the router configuration.
Please, someone offer up some brilliant diagnostic steps I am missing to expose what is wrong in my setup and make this work... Thank you!
BTW, here is the error message. You can see why, in light of what I mentioned above, this error message is infuriating to me by now... !
Cannot connect to \\remotemachine\sqlexpress.
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 5 - Invalid parameter(s) found) (Microsoft SQL Server, Error: 87)
HResult 0x57, Level 16, State 1
Named Pipes Provider: Invalid parameter(s) found [87].
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
shing a connection to the server. When connecting to SQL Server 2005, this failu
re may be caused by the fact that under the default settings SQL Server does not
allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.
Can you connect to the system by configuring an ODBC Connection from the ODBC Manager in the control panel. Doing this will help to verify where the problem is... if it goes through there is a problem in the way that you app is connecting to the database.
I see that you are connecting by names pipes... when trying the connection try by using tcpip to connect.
|||Thanks for the response. I get the same results from the ODBC Manager as I do from the Management Studio, command line, or from an application. Can't connect, same error message.
I can't believe I forgot to mention this in my original post, but even when I try to connect to the local SQL Server instance using it's IP address 192.168.0.98 instead of 127.0.0.1... I get the error message above. Hope this sparks some ideas out there. Thanks!
|||Did you check the surface area configuration? By default, SQL Server 2005 (even if it's just express edition) is secured so you need to configure access.|||bass_player wrote:
Did you check the surface area configuration? By default, SQL Server 2005 (even if it's just express edition) is secured so you need to configure access.
The surface area configuration shows that it is configured for local and remote connections using both TCP/IP and named pipes. Thanks though. I am baffled.
|||Maybe this has something to do with it?
Under SQL Server Configuration Manager TCP/IP Properties, it shows IP1, IP2, IP3 and IPAll. IP3 is an old IP address from when the machine was configured to use DHCP (can this entry be deleted somehow?). IP2 is 127.0.0.1, which I can connect on. IP1 is 192.168.0.98 which I want to connect with. IP1's "Active" property seems to keep getting set to "No", even after I set it to "Yes" and restart SQL Server. What do you think about this?
|||I connected. I am an idiot.
Once I tried with the correct freakin' IP address AND explicitly with the port, I can connect.
It's 192.168.1.98 not 192.168.0.98. That entry in the TCP/IP Properties (which was 0) was faking me out.
Woohoo! :)
Sorry folks. Thanks for the responses.
No comments:
Post a Comment