question

AdrianParker-5749 avatar image
0 Votes"
AdrianParker-5749 asked Joyzhao-MSFT answered

SSRS Credentials for remote DB

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

sql-server-reporting-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Joyzhao-MSFT avatar image
0 Votes"
Joyzhao-MSFT answered AdrianParker-5749 commented

Hi @AdrianParker-5749 ,

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

I tried connecting to different DBs and different machines, the only connection that would work when called from a client app on a remote machine was when it was pointed to a DB on the same machine as the report server.

How do I find out what authentication it is set up to use? I just installed the report server and thought it would just work.

What do I need to set to make it work on a network that doesn't have domains or controllers etc, just separate virtual machines?

Machine 1 "sql2019w16" contains SQL 2019 report server and database.
Machine 2 "sql2014w7" contains sql2014 database
Machine 3 "dev2019" contains visual studio 2019 with a web application that has an asp.net ReportViewer control.

dev2019 connects to sql2019w16 which can then connect to a DB on sql2019w16, but not to a DB on sql2014w7

0 Votes 0 ·
Joyzhao-MSFT avatar image
0 Votes"
Joyzhao-MSFT answered

Hi @AdrianParker-5749 ,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.