13

I have setup a SQL Server 2008 Express instance on a dedicated Windows 2008 Server hosted by 1and1.com. I cannot connect remotely to the server through management studio. I have taken the following steps below and am beyond any further ideas. I have researched the site and cannot figure anything else out so please forgive me if I missed something obvious, but I'm going crazy. Here's the lowdown.

The SQL Server instance is running and works perfectly when working locally.

In SQL Server Management Studio, I have checked the box "Allow Remote Connections to this Server"

I have removed any external hardware firewall settings from the 1and1 admin panel

Windows firewall on the server has been disabled, but just for kicks I added an inbound rule that allows for all connections on port 1433.

In SQL Native Client configuration, TCP/IP is enabled. I also made sure the "IP1" with the server's IP address had a 0 for dynamic port, but I deleted it and added 1433 in the regular TCP Port field. I also set the "IPALL" TCP Port to 1433.

In SQL Native Client configuration, SQL Server Browser is also running and

I also tried adding an ALIAS in the

I restarted SQL server after I set this value.

Doing a "netstat -ano" on the server machine returns a

TCP 0.0.0.0:1433 LISTENING UDP 0.0.0.0:1434 LISTENING

I do a port scan from my local computer and it says that the port is FILTERED instead of LISTENING. I also tried to connect from Management studio on my local machine and it is throwing a connection error. Tried the following server names with SQL Server and Windows Authentication marked in the database security.

ipaddress\SQLEXPRESS,1433

ipaddress\SQLEXPRESS

ipaddress

ipaddress,1433

tcp:ipaddress\SQLEXPRESS

tcp:ipaddress\SQLEXPRESS,1433

Ben Pilbrow
  • 11,995
  • 5
  • 35
  • 57
FAtBalloon
  • 279
  • 1
  • 3
  • 12
  • I'd bet there is enough information in the answers so far to solve this completely. Let us know where you are stuck. – djangofan Jun 09 '10 at 21:05

10 Answers10

5

First, in management studio, check management, sql server logs\current - you want to search for a message saying 'Server is listening on ['any' 1433].' If not, go to start, all programs, SQL server 2008, configuration tools, sql server configuration manager. Select 'sql server network configuration\protocols for MSSQLServer\SQLExpress'. Ensure TCP/IP is enabled. It should be based on the output of netstat -ano, but...

most importantly - from a remote client, have you tried to login via 

sqlcmd -S1.2.3.4\SQLExpress -U sa -P Password
?
JohnW
  • 501
  • 3
  • 8
  • This is what lead me to resolve a very similar issue. A security pack we had installed randomized the SQL Server port to a high 50000 range port. This showed up in the log. Once we opened up and connected to this port we never would have guessed, we were in. Thanks. – Chris Moschini Feb 01 '12 at 07:43
5

I think i can tell you exactly what the issue is, I spent over 48 hours trying to sort this. didnt find anything on the net. also happen to be with 1and1

look at these settings:

IP security policies.....

which opens box...............Packet Filter Properties near the bottom of the list there is a box ticked called :

'Close MSDE (TCP/UDP)' (I am asuming that MSDE = Microsoft SQL Database Engine?) Select it Press Edit...

which opens box............... Edit Rule Properties Select (again) >> 'Close MSDE (TCP/UDP)' Press Edit...

which opens box................IP filter List then you will see a list of ports tcp 1433, udp 1434 {Thats our list of ports all down as a blocking rule.....}

I think what needs to be done from here is either....

close that screen ..IP filter List on the screen Edit Rule Properties there is a tab Filter action, could just change that from Block to permit? (maybe changing it to permit, will allow us to tick the "Block All" option again - which sounds safer, but the support guys said there is a know bug, so might not work)

or

on the Packet Filter Properties

just untick the the rule 'Close MSDE (TCP/UDP)'

you might have to untick the rule 'Block ALL' to get it running

its probably to late for this to help you, but hopefully will help someone else with the same issue.

Lexius
  • 66
  • 1
  • 2
  • 1
    Thank you! That was exactly what the problem was! You're a genius, thanks for posting that. I would mark it as the answer but don't have any reputation points (signed up for an accouont simply to thank you). –  Dec 17 '11 at 08:59
  • I am surprised that those windows settings refer to SQL 2008 as "MSDE". That is a really old school name for sql server express. – djangofan Jan 23 '12 at 20:13
  • 3
    Hey, I am facing same kind of error and i am unable to find the page where we can actually change these settings. Can you give the link or an image that shows the settings ? – Vivekh Mar 25 '15 at 09:40
  • Where exactly are these properties? – Alisson Reinaldo Silva Sep 20 '16 at 23:48
1

You can also try www.firebind.com. It can test whether there is any block of TCP port 1433 in the outbound direction towards 1and1.

http://www.firebind.com/1433 will test this immediately.

1

I had the same headache connecting with SSMS from client PC to remote SQL Server. It looks like local firewall was blocking inbound server connection. Problem was solved by assigning inbound rule for SSMS for client PC firewall. The only place I found how to do that was https://msdn.microsoft.com/en-us/library/cc646024(v=sql.120).aspx

1.In Windows Firewall with Advanced Security, in the left pane, right-click Inbound Rules, and then select New Rule in the action pane.

2.In the Rule Type dialog, select Program, and then click Next.

3.In the Program dialog, select This program path and for SQL Server Management Studio enter the full path to ssms.exe. By default, it is installed in C:\Program Files (x86)\Microsoft SQL Server\120\Tools\Binn\Management Studio\ssms.exe

This helped me. Hope you too.

Community
  • 1
Serguei
  • 11
  • 1
0

Are you able to telnet to port 1433 from your workstation? This is an easy way to determine if you have network connectivity on that port. It could be that your provider is blocking the connection with their equipment somewhere along the line.

The fact that you're seeing the port as filtered makes me think they are blocking somewhere down the line. You may want to check with them as they may not want to allow SQL Servers to be connected to from remotely or they are blocking well-known ports. tcp/1433 is a well-known port and there are a few SQL Server-related worms that target it directly.

K. Brian Kelley
  • 9,004
  • 31
  • 33
0

type 'netstat -an' on the server machine to see if port 1433 is actually listening. also, make sure the user account you are using is enabled and also that "SQL Authentication" is enabled. take care of the "SQL Configuration Manager" settings as well. Also, allow the port 1433 as an exception in your WIndows Firewall. Basically, if you haven't told your SQL server to allow remote connections then its not going to.

djangofan
  • 4,172
  • 10
  • 45
  • 59
  • As mentioned in the question, "Doing a "netstat -ano" on the server machine returns a TCP 0.0.0.0:1433 LISTENING UDP 0.0.0.0:1434 LISTENING" As mentioned in the question, "In SQL Server Management Studio, I have checked the box "Allow Remote Connections to this Server" As mentioned in the question, the sql configuration manager settings are enabled. As mention in the question, Windows Firewall is disabled. – FAtBalloon Apr 05 '10 at 15:03
  • If its a remote SQL instance then how did you manage to do the netstat command? I'm just saying that it seems you are confusing the remote instance with the local instance. – djangofan Apr 05 '10 at 16:16
0

Is the SQL server agent running? if it isn't, which version of SQL server do you have?

Review the difference between different versions.
http://www.microsoft.com/sqlserver/2008/en/us/editions-compare.aspx

If you have Express edition or Web edition, they are disabled and cannot be run.

Embreau
  • 1,277
  • 1
  • 9
  • 10
0

Crazy idea, is your username and password correct? Are you logging in using Windows authentication or SQL Server?

Nai
  • 743
  • 1
  • 6
  • 24
0

Look for the conectivity on your SQL-Express. Activate TCP/IP. Be sure the port is configured to 1433 on your SQL-Express. Do you installed an named instance?

This Port has to be forwarded at 1and1 to your SQL-Server Instance.

By the way check your site about the port 1433. If your provider blocks it you have no chance.

Ice
  • 404
  • 1
  • 7
  • 16
0

What worked for me:

http://blogs.msdn.com/b/sqlexpress/archive/2005/05/05/415084.aspx

Specifically, I found that assigning the desired port in the IPALL section of the TCP/IP settings was the problem. Previously it had been blank, and I hadn't thought that I'd need to enter a value in here when I went to delete the "dynamic port" bits.