I cannot get my data source to connect to an SQL Server named instance using SSRS Report Builder

Question

I have a report in SSRS and I want to update the data source to use a named instance instead of the default instance. The server host will remain the same.

I thought this would be easy. I simply changed this:

clever-sql1

to this:

clever-sql1\MSSQLSERVER02

When I click "Test Connection" in the Connection Properties, the test succeeds.

Next, I click "OK" and "OK" again to close the Connection properties. I can see my connection string has been generated, and I click "Test Connection" again, but this time, the connection fails.

These are the things I have tried to fix the problem:

1. Checked the SQL configuration and made sure named pipes and TCP are enabled
2. Can confirm that remote connections are possible via the management studio
3. Disabled the firewalls on my PC and the server
4. I discovered that the named instance does not use default SQL port, but uses port 8000 (which is open on the firewalls). I tried specifying the port number in the connection string like this:

Data Source=clever-sql1\MSSQLSERVER02,8000;Initial Catalog=cleverdataout

This generated a different error:

Answer 1

I discovered that the named instance does not use default SQL port, but uses port 8000 (which is open on the firewalls). I tried specifying the port number in the connection string like this:

Yes, but the named instance ports are dynamic and managed internally by SQL browser service which use the port number 1434. In your case port# 8000 should not be used in connection string.

However, Please follow any one of following scenario to troubleshoot the issue:

Scenario 1. If you want use named instance in connection string:

1. make sure SQL Browser service running where SQL named instance installed.
2. Open 1434 (SQL Browser) port in firewall (where SQL service running)
3. Make sure you are able to do telnet to the SQL host on port number 1433 and 1434
4. Your connection string looks like: Data Source=clever-sql1\MSSQLSERVER02;Initial Catalog=cleverdataout

Scenario 2. If you want use port number in connection string:

1. Change SQL port from configuration manager

2. Restart sql service (named instance)
3. Open the port 1435 (configured in step:1) at windows firewall
4. Make sure you are able to do telnet to the host on custom port 1435 (configured in step:1)
5. Your connection string looks like: Data Source=clever-sql1,1435;Initial Catalog=cleverdataout

In any scenario, verify the user (used in connection string) have appropriate access on SQL server