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:
- 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:
Best Answer
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:
telnet
to the SQL host on port number 1433 and 1434Data Source=clever-sql1\MSSQLSERVER02;Initial Catalog=cleverdataout
Scenario 2. If you want use port number in connection string:
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