SSRS configuration issue - cannot connect to SQL Server
-
I had some issues with a local (development computer) Report Server where the configuration manager, Report Builder apps, etc. would not connect to the SSRS. The service was running and the DB's were accessible through SSMS.
After not getting any help I decided to uninstall SSRS and reinstall. During the install process, at the Database step of the configuration manager, I am trying to connect to my existing SQL server instance and the prior SSRS databases. The Server Name is correct and the Authentication Type, Username and Password are all correct. As I mentioned earlier, I can connect through SSMS as well as web applications, so I know that the service is running and the connection information is correct.
When I enter this information and click the [Test Connection] button I get the error message [Could not connect to server: A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible.]
I checked to make sure that TCP/IP is running and that I can connect through TCP/IP and I can. At this point I have no idea why the SSRS Configuration Manager will not connect to SQL server. I can't figure out where to go next.
Just to eliminate any obvious answers, SQL Server is running, I can connect to it both through SSMS as well as web applications, SSR Service is running and the SQL Browser service is running.
Any help would be greatly appreciated as at this point, I can't create new or edit existing reports that I am trying to support.
Thanks!
Bob
-
Bumping this to see if anyone might have some fixes to try.
Thanks!!
-
Have you verified that there is no firewall issue in the way?
Is SSMS installed on the SSRS server where you tested it successfully or is it on another machine?
Can the SSRS server successfully resolve the SQL Server via ping or a DNS lookup both as the NetBIOS and FQDN?
Can you use PowerShell and do a TNC check?
TNC MySQLServer 1433
Do you get a successful reply?
-
Thanks for the reply!
I disabled the Firewall and my local machine malware monitor and still cannot connect.
Yes, SSMS is running correctly on the same machine. This is all my local (development) computer.
I had done the ping testing previous to posting this and I can ping the SQL server both by name and by IP Address.
I tried testing the TNC command and go the following error...
Cannot validate argument on parameter 'CommonTCPPort'. The argument "1433" does not belong to the set "HTTP,RDB,SMB,WINRM" specified by the ValidateSet attribute.
Any other suggestions? Thanks in advance.
-
Not you're local firewall, could be the firewall on the SQL Server, could be a network level firewall too, there's multiple places a firewall could be.
I missed the -Port keyword, what is returned from this, assuming that is that SQL server is running on 1433, otherwise put in the right port number
TNC MySQLServer -Port 1433
-
I'm in a standalone situation so there are no servers involved. I don't believe there's a Firewall for my local SQL server instance. I've never heard about that before. How would I check this?
I did the TNC test with -PORT and I am seeing the following error.
WARNING: TCP connect to (fe80::1947:9b37:8bcc:1ecc%10 : 1433) failed
WARNING: TCP connect to (192.168.0.219: 1433) failed
WARNING: TCP connect to (192.168.50:50 : 1433) failed
In the results it shows that the PingSucceeded is True but TcpTestSucceded is False.
-
Something is in the way of your machine and the SQLServer.
Best thing would be to work with your Network Admins & DBA's to ensure your workstation can connect over the network and has the necessary things in place to do it.
-
Thanks for the help.
As I said, this is all running local so there is no network communication going on. It's all on the local machine. I can unplug from the network and turn off WIFI and I can still connect to the SQL Server database. The only app that cannot connect to SQL server is the SQL Server Configuration Manager.
This is a me, myself and I organization so I have no one else to turn to. That's why I've been trying these forums.
Also, prior to the issue mentioned in my first post, SSRS was running fine. The issues caused me to uninstall it and try to reinstall it which is where I am right now.
-
OH, sorry with you saying TCP/IP thought it was going over the network.
Probably try forcing a protocol in the SSRS config manager, like TCP:MySQLServer or NP:MySQLServer in the SSRS connection string
-
No problem. During my investigation, one of the troubleshooting threads for Microsoft said to test local TCP/IP.
How do I set the connection string in the SSRS Configuration? It doesn't look like that's an option (see image).
-
Add TCP: to the beginning of the servername, or NP: if wanting to force named pipes etc.
-
Okay, using NP:ServerName worked for that part. I was able to connect to SQL Server and choose my existing Report Server database.
However, in the step that follows I'm getting this error.
This was in the step that was asking for an existing account that the report server will use to connect to the report server database. I tried using the system administrator login as well as a regular user login. Both login credentials were entered correctly.
Any suggestions on where to go from here?
-
That's the same problem then, all your connection managers / data sources are having the same issue, so will need to force the connection property NP:
Looks like whatever you done has borked the TCP/IP communication part.
-
Just verify in SQL Server Configuration manager that SQL has actually got TCP/IP enabled in all the right areas, as there's the part for the engine and for the client tools to use.
Then ensure you restart the SQL instance.
SQL Server Network Configuration and the native client configuration area pieces.
-
From what I can see TCP/IP and Named Pipes are both configured and ready in SQL Server (see below).
I'm still getting the error message above and it does not let me continue.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply