Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have a VPS windows 2008 with SQL Server Express installed on it. I want to access the SQL Server Express database from my PC. I can access it when I disable windows firewall, otherwise I cannot.

I used this batch from Microsoft to solve the problem but nothing happened.

@echo =========  SQL Server Ports  ===================
@echo Enabling SQLServer default instance port 1433
netsh firewall set portopening TCP 1433 "SQLServer" 
@echo Enabling Dedicated Admin Connection port 1434
netsh firewall set portopening TCP 1434 "SQL Admin Connection" 
@echo Enabling conventional SQL Server Service Broker port 4022  
netsh firewall set portopening TCP 4022 "SQL Service Broker" 
@echo Enabling Transact-SQL Debugger/RPC port 135 
netsh firewall set portopening TCP 135 "SQL Debugger/RPC" 
@echo =========  Analysis Services Ports  ==============
@echo Enabling SSAS Default Instance port 2383
netsh firewall set portopening TCP 2383 "Analysis Services" 
@echo Enabling SQL Server Browser Service port 2382
netsh firewall set portopening TCP 2382 "SQL Browser" 
@echo =========  Misc Applications  ==============
@echo Enabling HTTP port 80 
netsh firewall set portopening TCP 80 "HTTP" 
@echo Enabling SSL port 443
netsh firewall set portopening TCP 443 "SSL" 
@echo Enabling port for SQL Server Browser Service's 'Browse' Button
netsh firewall set portopening UDP 1434 "SQL Browser" 
@echo Allowing multicast broadcast response on UDP (Browser Service Enumerations OK)
netsh firewall set multicastbroadcastresponse ENABLE

Thanks

share|improve this question

migrated from stackoverflow.com May 17 '12 at 15:10

This question came from our site for professional and enthusiast programmers.

add comment

2 Answers

Sounds like SQL Server is not installed using the default ports. I recall SQL Express used to do that, because it installed a named instance rather than the unnamed default.

Open the SQL Server Configuration Manager and check the following settings:

  1. Expand "SQL Server Network Configuration" in the left pane, then the "Protocols" node for your instance (probably SQLEXPRESS): is TCP/IP enabled?

  2. Right-click/Properties the TCP/IP node, select the IP Addresses tab; find the IPn section that matches the network interface you want to connect on (or scroll to the IPAll section for any/all interfaces): is there a number in the TCP Dynamic Ports field? You will want to clear this field and, based on the batch script you ran, set the TCP Port field to "1433" (no quotes)

  3. Click on "SQL Server Services" in the left pane: confirm that the "SQL Server Browser" service is running/automatic. Change it to this, if not.

Did you vet the batch script before you ran it? It has opened far more ports than I would usually for a 'public-facing' SQL Server -- TCP/80 and TCP/433 are unnecessary unless you are running IIS or Reporting Services, TCP/1434 is unnecessary unless you want to be able to access the server, over the wire, in emergencies, and TCP/135 is RPC, which I would say you certainly don't want people to be able to access across the internet.

If, by VPS, you mean you have a direct-connected, internet-facing server that you access from your remote location, you really want to look at getting a VPN or SSH service to it, that you can enable this access over, while leaving much of this still blocked from the internet.

share|improve this answer
1  
This is the correct answer. I know from experience that the SQL tcp listener is not "on" by default. Also, the "sa" account is disabled by default and SQL authentication is also not enabled by default. You have to fix all of those. Use "netstat -an" to verify. –  djangofan May 18 '12 at 21:49
add comment

netsh firewall set portopening TCP 1433 "SQLServer"

wont work - 1433 TCP is default instance. You have to hardcode the port and then use that OR open the firewall (advanced firewall can do that) for the executable (so it gets all it needs).

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.