SSRS Credentials for remote DB

Question

I'm a developer trying to add reporting services to our web software.. but have hit a snag.

I have 2 virtual machines, one with sql 2014 DBs on and a new one that has SQL 2019 with reporting services, both set up to take user/password credentials, not in a domain.

I created a simple report and it links to a 2019 db on the same machine and runs fine.

I change the datasource to point to an sql 2014 db and again running it from the reporting server visual studio project it works fine and does connect to the remote virtual's DB.

I then created an asp.net website on a completely different machine and added a reportviewer control to a page, which I point at the reporting server. if the reporting server points to a 2019 DB it works ok.. but when I change the datasource in the report to point to the 2014 db virtual, it gets a credential error. I've even tried using the sa user on the 2014 DB and again, it works fine when called from the report server but not remotely from my asp.net app.

I have tried passing the connection string from the client website to use in the report datasource and again that works if using the 2019 db but not the 2014 one.

What am I missing?

Hope someone can help
Adrian

Answer 1

Hi @Adrian Parker ,

set up to take user/password credentials, not in a domain.

What authentication are you setting up? Have you set up a custom authentication?
When you change the datasource to point to an sql 2014 db showed that the authentication failed, did you try to change the data source?

Regards,
Joy

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @Adrian Parker ,

Thank you for your specification.

What error message are you getting?

For Machine 1 connecting Machine 2, you could set Database Credentials as talked in Data Source Configuration and Network Connections

For Machine 3 connecting to Machine 1, you need to have custom authentication if no windows authentication is allowed. I would have to say this is not an easy one to get through. If your task mainly focus on developing the ASP.net app, and you don’t have extra time to deal with the custom authentication, why not just set up a domain (dc on Machine 1)and use window authentication in this three machine? It could be much easier and safer.

If this is not a choice, you could change the custom authentication of SSRS: Configure Custom or Forms Authentication on the Report Server. There are also some examples of this in Github, I think you could easily find them by google.

Best Regards,
Joy

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.