Sql Server 第一次连接超时

SQL SERVER数据库遇到一个问题，就是每次第一次连接，总是报超时。不论是用SSMS还是项目连接，都会报这个错，但第二次连接的时候就马上好了。

即便你把服务器的连接超时设置为0（无限制），还是会报超时错误。

GOOGLE:sql server connection timeout first time

说是数据库端口与防火墙设置问题

 

http://serverfault.com/questions/179898/sql-server-timeout-on-first-attempt

----------------------------------------------

I think I found the solution, at least in my case it's working. I am using instance name and this automatically imply a dynamic port for the sql server service. I've changed the settings from dynamic to a fix port and then opened the firewall on that port.

SQL Server Configuration Manager --> SQL Server Network Configuration --> Protocols for 'InstanceName' --> TCP/IP --> Properties --> IP Addresses --> IP All -->

Here you see two options:

• TCP Dynamic Ports: 51250 (randomly generated)
• TCP Port: empty - I put here 1433 and then I opened the firewall (in case it was not already opened). You can put whatever port you want (I put 1433 because it was the only instance. In case of multiple instances you should choose for each instance a different port and then open them in firewall)

The script used to easy your task of openning the ports I've downloaded from MS and I am reproducing it here (comments are in german but they should be obvious):

@echo =========  Ports des SQL-Servers  ===================
@echo Aktivieren von Port 1433 für die SQLServer-Standardinstanz
netsh firewall set portopening TCP 1433 "SQLServer" 
@echo Aktivieren von Port 1434 für dedizierte Administratorverbindungen
netsh firewall set portopening TCP 1434 "SQL-Administratorverbindung" 
@echo Aktivieren von Port 4022 für den konventionellen SQL Server-Service Broker  
netsh firewall set portopening TCP 4022 "SQL-Service Broker" 
@echo Aktivieren von Port 135 für Transact-SQL-Debugger/RPC 
netsh firewall set portopening TCP 135 "SQL-Debugger/RPC" 
@echo =========  Ports für Analysedienste  ==============
@echo Aktivieren von Port 2383 für die SSAS-Standardinstanz
netsh firewall set portopening TCP 2383 "Analysedienste" 
@echo Aktivieren von Port 2382 für den SQL Server-Browserdienst
netsh firewall set portopening TCP 2382 "SQL-Browser" 
@echo =========  Verschiedene Anwendungen  ==============
@echo Aktivieren von Port 80 für HTTP 
netsh firewall set portopening TCP 80 "HTTP" 
@echo Aktivieren von Port 443 für SSL
netsh firewall set portopening TCP 443 "SSL" 
@echo Aktivieren des Ports für die Schaltfläche 'Durchsuchen' des SQL Server-Browserdiensts
netsh firewall set portopening UDP 1434 "SQL-Browser" 
@echo Zulassen von Multicast-/Broadcastantwort auf UDP (Aufzählung der Browserdienste OK)
netsh firewall set multicastbroadcastresponse ENABLE

Hopefully this will help other desperate people like I was :)