SQL Server Issue Connecting

Forums › LAN (ethernet/Wifi/routers/Bluetooth) › SQL Server Issue Connecting

1 | 2 | 3

BDFL - Memuneh
80654 posts

Uber Geek
+1 received by user: 41050

Administrator

ID Verified

Trusted

Geekzone

Lifetime subscriber

#411312 30-Nov-2010 18:19

Since 2 and 3 failed I'd say the configuration is listening only from 127.0.0.1, not in all addresses.

Open SQL Server Configuration Manager, under "SQL Server Network Configuration" double-click "Protocols for MSSQLSERVER" and check that "Listen ALL" is "Yes".

Referral links: Quic Broadband (free setup code: R587125ERQ6VE) | Samsung | AliExpress | Wise | Sharesies

Support Geekzone by subscribing (browse ads-free), or making a one-off or recurring donation through PressPatron.

nzkc

1634 posts

Uber Geek
+1 received by user: 1041

#411326 30-Nov-2010 18:54

Also check your firewall is not blocking (that's my suspicion for #2). Make sure you get #2 working before you even try #3.

kiwis

837 posts

Ultimate Geek
+1 received by user: 12

#411333 30-Nov-2010 19:31

Listen All is set to Yes under the TCP/IP Protocol

How can I check if my router has a firewall? I've got a Netcomm NB6Plus4Wn model

kiwis

837 posts

Ultimate Geek
+1 received by user: 12

#411354 30-Nov-2010 20:23

Do I need to enable this?

NAT Type:

Port Restricted Cone NAT

Full Cone NAT

The internal host with private address and port can send packets to certain Internet host through public address and port of this device’s WAN interface. NAT session is then established by the mapping of local private address/port to Internet public address/port through public address/port of this device’s WAN interface. When Port Restricted Cone NAT is configured, only mapped Internet host can access that internal host through the mapped public address/port of this device’s WAN interface. Other packets from Internet will be blocked due to there are no matched NAT sessions created in advance.

kiwis

837 posts

Ultimate Geek
+1 received by user: 12

#411361 30-Nov-2010 20:34

Also this says it's open
http://www.canyouseeme.org/

IS there an outbound firewall rule blocking it?

PenultimateHop

637 posts

Ultimate Geek
+1 received by user: 2

Trusted

#411371 30-Nov-2010 20:46

Lots of ISPs in the world have filtered port 1433 due to the SQL Slammer worm in 2003. Both the worm and laziness are relatively prevalent on the Internet so you may find that if you're connecting to SQL servers across the Internet that the port will be filtered (potentially by any of the operators your packets traverse).

Once you've ruled out any local firewall and port-forwarding issues, try to use a different port, or better yet, a VPN.

Geekzone

Want to support Geekzone and browse the site without the ads? Subscribe to Geekzone now (monthly, annual and lifetime options).

kiwis

837 posts

Ultimate Geek
+1 received by user: 12

#411372 30-Nov-2010 20:53

I'm with Vodafone (NZ) does anyone know if they block port 1433? there is nothing on their website.

nzkc

1634 posts

Uber Geek
+1 received by user: 1041

#411383 30-Nov-2010 21:17

Don't worry about your router until you can connect from another machine on the LAN. You'll be chasing your tail if you haven't sorted things in the order I listed previously.

If your ISP does filter it (I doubt they will), you can always use NAT to have another port (e.g. 8080) remapped to 1433. I am presuming your router supports this - many do.

kiwis

837 posts

Ultimate Geek
+1 received by user: 12

#411398 30-Nov-2010 21:45

So from my laptop on the same wireless network i get this afer doing

"Telenet 192.168.1.4 1433"

The cursor flashes on the top left, is that good or bad because on my machine i get the same thing when i do "telnet localhost 1433"

Regs

4066 posts

Uber Geek
+1 received by user: 206

Trusted

Snowflake

#411399 30-Nov-2010 21:46

check the following articles and make sure you follow the instructions to get it working on your LAN:

sql express 2005:
http://www.linglom.com/2007/08/31/enable-remote-connection-to-sql-server-2005-express/

sql express 2008:
http://www.linglom.com/2009/03/28/enable-remote-connection-on-sql-server-2008-express/

following these you will also want to check the TCP/IP port and set it manually to 1433. there are some instructions in this blog under 'step 2: to browse or not to browse":
http://blogs.msdn.com/b/sqlexpress/archive/2005/05/05/415084.aspx

Sales Engineer
Snowflake
www.snowflake.com

about.me/nzregs
Twitter: @nzregs

nzkc

1634 posts

Uber Geek
+1 received by user: 1041

#411408 30-Nov-2010 21:56

kiwis:
The cursor flashes on the top left, is that good or bad because on my machine i get the same thing when i do "telnet localhost 1433"

That's good. It has connected. You would have got a timeout error if it did not connect.

Re Regs comment about fixing the port, you shouldn't have to do this if this is the only instance on that machine you'll run. SQL Server checks to see if 1433 is in use at start up. If its not in use it grabs it and uses that, otherwise it uses a random free port. That said, forcing it to 1433 wouldn't be bad practice since you want to now NAT through your firewall as you'll want it to use the same port always.

Off topic, but for those interested: Note that you don't have to use 1433, however, your clients will either need to know which port to use (there are a couple of ways to do this) or they'll need to be able to see the SQL Browser which in all likelihood they wont be able to (this uses port 1434 by the way).

Lenovo

Shop now for Lenovo laptops and other devices (affiliate link).

kiwis

837 posts

Ultimate Geek
+1 received by user: 12

#411415 30-Nov-2010 22:05

I've tested my application from the laptop and it works fine, i just can't connect from my external IP from an external location.

So it has to be my router blocking it, I've got port forwarding onto 192.168.1.4 on port 1433 and i'm connected via a erthernet cable.

What else do i need to do?

Also what does this mean? "since you want to now NAT through your firewall as you'll want it to use the same port always."

nzkc

1634 posts

Uber Geek
+1 received by user: 1041

#411417 30-Nov-2010 22:09

kiwis:
Also what does this mean? "since you want to now NAT through your firewall as you'll want it to use the same port always."

When you set up the NAT (so that its working ;-)) you'll be telling your router forward packets from the Internet on this port to this address/port in my LAN. As such you dont want the destination IP address and port number changing otherwise your NAT will be broken.

Now onto your NAT issue. Does your router have any logs? Also do you happen to know if you've enabled TCP or UDP or both for port 1433?

kiwis

837 posts

Ultimate Geek
+1 received by user: 12

#411420 30-Nov-2010 22:14

Whats the difference in NAT and Port Forwarding?

I have this option

Enable VPN ALG

VPN ALG allows two or more simultaneous VPN connections through this router.

Enable SIP ALG

SIP ALG allows two or more simultaneous VoIP phone calls made by VoIP clients through this router.

Or what i have in post 411354.

Yes it does have logs, what are we looking for?

I enabled both (UDP/TCP) for port forwarding?

nzkc

1634 posts

Uber Geek
+1 received by user: 1041

#411424 30-Nov-2010 22:25

Its port forwarding you want.

I believe I have the same router as you and this is what I would set up (if I were doing it):

The IP address is what I think you said your SQL Server was running on.

I am unsure if the router supports a local loopback (i.e. if you can use the WAN address on your router to test). You may actually have to be completely out on the Internet. You could have your friend test (again I go back to the telnet test initially).

1 | 2 | 3