Minimum credentials on SQL Server to connect via Power BI Desktop

Question

Note: This is not a Power BI issue, this is a DB credential issue.

In Power BI Desktop, I am trying to connect to a database, but I only want that user to have SELECT access.

I have one User that is a "sysadmin" under the Server Role and that works. But, that's too many permissions.

I created a new User and gave it Login Properties> Server Role = "public", then went into the database I need and gave it Membership> db_datareader. I thought this would work, but it doesn't. When I log into SSMS as that User and try to query that database, it says "The database {name} is not accessible." In Power BI, the error reads: "We couldn't authenticate with the credentials provided. Please try again".

Power BI Users: Yes, I am on the Database tab, not the Windows tab.

Answer 1

I made a test SQL user, created the test user in a Test database I created, and granted it datareader access using scripts, and this worked without an issue. Switch out the usernames and database names for your app and report in on what happens when you start fresh. It could be as simple as you're using a old user that is orphaned to other issues so starting fresh could help you isolate it.

Note if you are using this for production you'll want to probably remove the 'check_policy=off' which lets you have passwords that don't conform to your Windows policy.

This creates the user in SQL Server:

USE [master]
GO
CREATE LOGIN [test] WITH PASSWORD=N'test', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO

This creates it in the DB and adds it to the datareader list:

USE [test]
GO
CREATE USER [test] FOR LOGIN [test]
GO
USE [test]
GO
ALTER ROLE [db_datareader] ADD MEMBER [test]
GO

• hurleystylee: I'm embarrassed and frustrated at how much time I spent trying to get this to work and the problem had something to do with me using a common username that we use company-wide. When I started from scratch and used these scripts, it worked first time. Thanks, @Ali-Razeghi!
• Ali Razeghi - AWS: Don't even worry about it, stuff like this happens in I.T. and often to the best of us. Just keep trekking. Good luck!