Today I deployed our Sitecore development project to the staging environment, and once I completed all of the regular of Sitecore deployment steps. I faced the following error:
Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool. This may have occurred because all pooled connections were in use and max pool size was reached.
After searching online about this issue, I found that the error is coming from the SQL server conection. some of the links mentioned that this error caused by a slow SQL conection, or the SQL server is not very close enough to your web server.
In my case, we have a two spareate servers one for the web and the other for the SQL.
I tried many ways to solve this issue like updating the timeout in the conection string, altering the min and max pool size of the connection string, changing the SQL server timeout settings, altering the SQL permissions. But none of these fixed the issue.
So I started investigation the connection and why it is slow. Later on I found that the SQL server is blocked by the windows firewall and it is causing this issue.
So I did the following steps to fix this issue:
1- Enabled TCP/IP on the SQL Server machine, and ensure that the SQL server is enable remote connection.
2- Configure SQL server to enable TCP/IP connection.
3- Configure the SQL server port, it is by default 1433.
4- Configure the Windows Firewall to Allow SQL Server Access for the specified port, I used the follwoing command:
netsh advfirewall firewall add rule name = SQLPort dir = in protocol = tcp action = allow localport = 1433 remoteip = localsubnet profile = DOMAIN
After these steps Sitecore is up and running.