SQL Server Issue Connecting

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

kiwis

837 posts

Ultimate Geek
+1 received by user: 12

#72633 30-Nov-2010 08:38

Hi
I've got SQL Server Express installed on my machine, and when i connect to it locally via a .net command line it works great, i'm not trying to connect remotetly but can't - i've unblocked port 1433 from the firewall i've got port forwarding on i'm connecred via Ethernet cable from my router i've enabled TCP in the SQL Configeration Manager.

I've read a few posts saying

telnet <ipaddress> <port number>

When I do this I get

Connecting To 110.97.174.215...Could not open connection to the host, on port 143
3: Connect failed

Yes I'm using my external IP

Any ideas?

1 | 2 | 3

BDFL - Memuneh
80646 posts

Uber Geek
+1 received by user: 41030

Administrator

ID Verified

Trusted

Geekzone

Lifetime subscriber

#411001 30-Nov-2010 08:44

Is the remote machine in the same LAN? If so why are you using the external IP?

If they are not in the same LAN, do you have port forward setup in your router?

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.

kiwis

837 posts

Ultimate Geek
+1 received by user: 12

#411002 30-Nov-2010 08:46

The SQL Server is on my own machine, I'm waiting a friend to access this as well and he is not in the LAN and on a different ISP even

freitasm

BDFL - Memuneh
80646 posts

Uber Geek
+1 received by user: 41030

Administrator

ID Verified

Trusted

Geekzone

Lifetime subscriber

#411003 30-Nov-2010 08:47

You said "Yes I'm using my external IP" so that's why I am asking. Is this external IP the one assigned to your router or the one assigned to this machine explicitly?

If this machine is on a LAN with dynamic IP then it will never be reached on this address. We need to know your LAN configuration before even helping.

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.

kiwis

837 posts

Ultimate Geek
+1 received by user: 12

#411004 30-Nov-2010 08:50

Sorry I'm a little lost then, my router has assigned for 192.168.1.4 and my external IP I got from http://www.whatsmyip.org/ and i'm using this IP in my connection string.

freitasm

BDFL - Memuneh
80646 posts

Uber Geek
+1 received by user: 41030

Administrator

ID Verified

Trusted

Geekzone

Lifetime subscriber

#411007 30-Nov-2010 08:54

This is the IP your entire network is known for the OUTSIDE world.

Where are you using this connect string from? INSIDE or OUTSIDE your LAN?

If you are trying to connect from outside your LAN then you have to port forward 1443 to the internal IP address and connections should be to your external IP.

If you are trying to connect from inside your LAN then you have to use its internal IP 192.168.1.4.

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.

kiwis

837 posts

Ultimate Geek
+1 received by user: 12

#411012 30-Nov-2010 09:04

I knew you couldn't when working with Http and it's the same here you can not call your own external IP.

I just changed it to 192.168.1.4 with no port of course and it works!

How could I test to see if it works on my external IP without having to drive up the road try it there then drive home again?

That would become very annoying very quickly.

Apple TV

Stream your favourite shows now on Apple TV (affiliate link).

freitasm

BDFL - Memuneh
80646 posts

Uber Geek
+1 received by user: 41030

Administrator

ID Verified

Trusted

Geekzone

Lifetime subscriber

#411014 30-Nov-2010 09:05

You can't. If your router does't support a loopback you will need access from an external network. Invest in a USB 3G modem for example and use it from another laptop or PC?

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.

kiwis

837 posts

Ultimate Geek
+1 received by user: 12

#411022 30-Nov-2010 09:22

Thought so;

All I should need to do is unblock that port on the firewall and enable port forwarding? Correct?

nate

6473 posts

Uber Geek
+1 received by user: 458

Retired Mod

Trusted

Lifetime subscriber

#411026 30-Nov-2010 09:29

kiwis: Thought so;

All I should need to do is unblock that port on the firewall and enable port forwarding? Correct?

Yep

nzkc

1634 posts

Uber Geek
+1 received by user: 1041

#411043 30-Nov-2010 10:14

By default SQL Server Express is configured to only allow connections from the host machine (if I remember correctly) using shared memory.

You'll need to configure it to allow remote connections and/or enable TCP/IP sockets on the instance.

You can use the SQL Configuration Manager to do this.

Ragnor

8279 posts

Uber Geek
+1 received by user: 585

Trusted

#411068 30-Nov-2010 10:54

What modem/router do you have? It might support local loopback.

Apple TV

Stream your favourite shows now on Apple TV (affiliate link).

Regs

4066 posts

Uber Geek
+1 received by user: 206

Trusted

Snowflake

#411120 30-Nov-2010 12:15

nzkc: By default SQL Server Express is configured to only allow connections from the host machine (if I remember correctly) using shared memory.

You'll need to configure it to allow remote connections and/or enable TCP/IP sockets on the instance.

You can use the SQL Configuration Manager to do this.

All the above will be true, fix that before digging into firewalls :)

It will also probably use a random port for TCP/IP too, so you will need to either fix it to the standard port (1433) or enable the SQLBrowser service and add that to your firewall too.

Sales Engineer
Snowflake
www.snowflake.com

about.me/nzregs
Twitter: @nzregs

kiwis

837 posts

Ultimate Geek
+1 received by user: 12

#411190 30-Nov-2010 13:57

Router: Netcomm (don't have version number with me right now) Do they normally have loop back?

Okay so via my internal IP it works fine at home, it does not work from it's external IP at my friends house.

I've enabled port 1433 which is the default port for SQL server and have allowed incoming firewall for this port and have enabled SQL Browser. What is wrong? something is blocking it...

I have windows 7 Ultimate.

nzkc

1634 posts

Uber Geek
+1 received by user: 1041

#411201 30-Nov-2010 14:15

Confirm that the instance is listening on TCP/IP sockets by telneting into the port.

I'd do the following:

On the host: Telnet localhost 1433

From a different machine on the same subnet: Telnet Internal_IPAddress 1433

From outside of the subnet: Telnet External_IPAddress 1433

If the first one fails: You do not have SQL Server listening on port 1433 or not even using TCP/IP sockets - this can be confirmed by checking the sql server error log (you can run sp_readerrorlog to do this)

If the second one fails: The host firewall is preventing access

If the third one fails: The router/modem firewall/nat is preventing access

Feel free to PM me if you want some more info.

kiwis

837 posts

Ultimate Geek
+1 received by user: 12

#411308 30-Nov-2010 18:13

2. Comes up with a empty screen with a cursor flashing
3. Connecting To 177.104.91.118...Could not open connection to the host, on port 1433: Connect failed

I'lll have to try 1 when i get home but i know i can connection using 192.168.1.4 via my command line so i'm thinking that may work.

1 | 2 | 3