Today I deployed our Sitecore development project to the staging environment, and once I completed all of the regular 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 the max pool size was reached.
After searching online about this issue, I found that the error is coming from the SQL server connection. some of the links mentioned that this error is caused by a slow SQL connection, or the SQL server is not very close enough to your web server.
In my case, we have two separate 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 connection 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 investigating 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 enables 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 following 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.