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

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?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pushpakumara MahagamageVPCommented:
can you post some screenshots here. and did you enter SCCM Administrator credential for that data source logging, then what happens
RhoSysAdminAuthor 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.
Pushpakumara MahagamageVPCommented:
We are developing reports in Visual Studio with SSDT, I'll install Report Builder and then I can guide you.
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Vitor MontalvãoMSSQL Senior EngineerCommented:
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).
RhoSysAdminAuthor 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.
Pushpakumara MahagamageVPCommented:

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Pushpakumara MahagamageVPCommented:
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.
RhoSysAdminAuthor 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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.