Sample Header Ad - 728x90

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

0 votes
1 answer
3085 views
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. enter image description here 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. enter image description here 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: enter image description here
Asked by cleverpaul (201 rep)
Aug 2, 2019, 02:18 AM
Last activity: Aug 2, 2019, 08:23 AM