SQL Server is configured properly; however, one particular BillQuick user (client) cannot connect to the SQL Server using the User ID and password.
This could mean that there is a block on the TCP port somewhere on the client or server system, or the network itself.
The following steps can resolve most SQL connectivity issues:
Step 1 - Ping the Server:
The first step toward troubleshooting this issue is to try and ping a SQL Server. Ping might be blocked by network administrators but if it has been known to work previously, it's a good step to verify that the server is up and the client can connect to it. Open an elevated command prompt and ping the server name or IP address. Also note the IP address.
Step 2 - Configure the client to connect directly to a custom TCP port for that server's IP
Open SQL Server Configuration Manager and navigate to the SQL Server Network Configuration for the instance that is running your BillQuick database.
Click on TCP/IP and scroll down to IPAll and remove any TCP Dynamic Ports and add a TCP Port of your choice. The default port on which the SQL usually runs on is 1433, so try an available open port like 1440.
Step 3 - Highlight the instance where BillQuick is installed using SQL Server Services
Highlight the instance where BillQuick is installed using SQL Server Services; right-click on it and restart services.
Step 4 - Open the necessary firewall ports on your server for SQL Server and the custom TCP Port.
Open Windows Firewall > Advanced Settings > Inbound Rules and click New Rule.
It opens the New Inbound Rule Wizard. Select Program rule type and then choose the program path for sqlserv.exe.
Next, add another new inbound rule for the TCP port.
Add the SQL browser through the firewall located in Program Files(x86)\Microsoft SQL Server\90\Shared to your Inbound Rules.
Note: At the time of publishing this KB article, as per the Microsoft documentation, the default port for SQL Server browser communication was UDP Port 1434. Please configure this port on your firewall and use the above method to include this Windows firewall port exception as well.
Step 5 - Reconnect
In the event you still have connectivity issues, further network troubleshooting must be done. BQE Consulting Services can assist in tracing these problems.
Open BillQuick; connect to SQL Server or SQL Express and enter your TCP connection string as follows:
TCP:IP Address\Instance Name, TCP Port
After you have entered your SQL User ID and Password, select your Database Name from the drop-down and click OK.
This should resolve the issue.