unable to connect to data source (Login failed) when attempting to edit query in SQL Server Report Builder

RhoSysAdmin
RhoSysAdmin used Ask the Experts™
on
We have SCCM 2012 using a separate SQL server on the back end. I can edit reports, create new datasets using only the "SCCM Administrator" user locally and remote.

I had an issue editing the query for the datasets remotely until importing the SQL server's certificate to the local desktop's certificate store. But that much is resolved now.

I have (domain) users who have been assigned a "Report Administrator" rule.  They can create and edit reports remotely.  But they cannot edit queries b/c the SQL server Report Builder prompts for credentials to the Data Source when opening the dialog box to edit the query.

The error is "unable to connect to data source", with details showing that the login failed for the domain user.

I've tried adding a SQL login for one of the domain users but it did not work in my initial tests. Maybe I did something wrong?

I'm new to SQL Report Builder. Can someone walk me through what's missing for these domain users to be able to access the Data Source so they can edit queries?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
can you post some screenshots here. and did you enter SCCM Administrator credential for that data source logging, then what happens

Author

Commented:
from my laptop,
- I open my SSRS site in a browser, right click on a report > Edit in Report Builder
- Expand "Datasets" and right-click on any dataset > select Query
- I see a pop-up window to "Enter Data Source Credentials"

IF I use the SCCM Admin credentials that were used when I created the report from the SCCM server, it works.

IF I try to use my own credentials (and I'm a "SCCM Operator" and "Report Administrator"), it fails:

I can create and edit reports, but cannot edit queries unless I use the original credentials that were used.

The data source is an "AutGen_*" - automatically created. When I check the properties of the data source, the "Credentials" section is greyed out but "Do not use credentials" is selected.

Any of my report users can run these reports. Where we're having trouble is granting a couple of SCCM administrators access to create their own reports, which requires the ability to connect to the data source on the SQL server so they can write their queries.

PDF of screenshots attached.
SSRS_ScreenShots.pdf
We are developing reports in Visual Studio with SSDT, I'll install Report Builder and then I can guide you.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
You're using the SQL Server authentication fields to provide a Windows domain authentication and that won't work.
You'll need to use the "Use the current Windows user" option and for that you need to open your browser with another Windows user credential (right-click on the Browser icon and choose "Run as a different user..." and provide the correct credentials).

Author

Commented:
I've tried ticking the "Use the current Windows user" box and entering my domain credentials. I'm running this from IE as myself.

Do I need a SQL login for my domain account on the SQL server?  If so, what permissions should that account have to my SCCM database?  It tried setting up a SQL login for myself earlier and it didn't work. That doesn't mean I did it correctly of course.
Hi

That windows user should have SQL Server access for your database. you can check that from SQL Server management studio by connecting to SQL Server, with same credential.

and sql server port [default 1433 has to open] in your case port is already open. but your windows user does not has sql server access privileges.

and you can use SQL server user credential instead of Windows,  if your Database server is on security mixed mode. for that you have to clear Use as windows credential check box.  

but the recommended way is grant SQL Server access to windows user.
it seems the best option is "use the current windows longing" option as Vitor 's comment.  test database access from SSMS after seting up windows user access in SQL Server. I gave DB owner access to WIndows user.

Author

Commented:
I struggled with this initially b/c I created a "database user" directly under the SCCM database instead of creating a "New Login" under Security > Logins.  Once I created the SQL user under Security > Logins, it worked.

For user mappings, I only granted public and smschm_users database role memberships for the SCCM database. This is all that the SCCM admin role account was granted. It worked for my domain account as well.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial