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:
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:
- Checked the SQL configuration and made sure named pipes and TCP are enabled
- Can confirm that remote connections are possible via the management studio
- Disabled the firewalls on my PC and the server
- 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: