SQL 2008 R2 – Connection issues from application – connects 2nd time

Had a problem where an application had issues connecting to another applications SQL database.
The application would freeze and after a delay come up with SQL related errors.
Troubleshooting was difficult as some users would not freeze and it only happened in certain areas of the application.

However when testing the connection with Excel I found that it would fail the 1st time connecting and then work the 2nd time. It did not matter if I used Windows Authentication or SA.
To test in Excel:
In Excel > data tab > from other sources > from SQL server > enter servername\instance\name > select log on credentials.

Excel connect to SQL

After clicking Next it would time out, but would then work if I tried to connect again.

I then searched online and found the following post:

http://kromey.us/2011/06/microsoft-sql-server-2008-times-out-on-first-connection-attempt-447.html/comment-page-1#comment-7787

I then opened SQL Server Configuration Manager > SQL Server Network Configuration > Protocols for DATABASE > dbl click TCP/IP > IP Addresses Tab > found IPAll > TCP Dynamic Ports was 61288 (port numbers can be different to this)

SQL IPAll port setting

 

Opened Windows firewall and added a inbound rule for this port number.

Tested in Excel and found it would connect 1st time now using Windows or SA authentication.

Tested in application and found connection issues resolved.