Using SQL Authentication

[Anonymous]

Just experienced a very strange problem in Power BI Desktop. It seems like it is stuck to Windows Authentication when trying to connect to a SQL Server ??? So how do I connect with SQL Authentication?? This problem exists even when trying to connect to an Azure Database as well so this of course will not work.. Any suggestions??

[tbmccoy]

Hi all,

I can confirm that skipping past the credential management screen is still a behavior in  version 2.40.4554.421 64-bit (October 2016).   This appears to manifest itself when your domain account already has access to the server.

I expect that this behavior was probably intended as a *feature*, but it is in fact a bit of a pain.  The issue is that, by default, my personal domain account (which has elevated privileges in the db) gets used in fetching objects/data for the report.  Like many devs, my default db privileges allow me access to data that is well beyond the scope of any single report.  It's not at all clear to me what will happen when I pass this off to other devs/users to maintain.  I expect it would either introduce a security hole, or stop connecting to the data.  Neither option is good.

As per smoupre's suggestion suggestion, you can change to sqlAuth later, although this is a nuisance at best... 

HTH,

__T

[rpschlak]

As of Version: 2.48.4792.721 64-bit (July, 2017) it is still a nuisance. PowerBI, in erratic fashion, skips past asking for the account name if the current user has access to the DB with Windows credentials. Yes, additional steps in other locations can be taken to fix the issue so that the account with the proper DB grants to tables and procs is active.

Please fix this.

[Bjoern]

@v-sadadi Are you using the enterprise or personal gateway? I guess, otherwise it will not work.

Both of the gateways "open" the source for the use by the web-service. 

[Greg_Deckler]

In File | Options and settings | Data source settings, you can go in and edit the credentials of your existing data sources.

[cous]

Thanks @Greg_Deckler! I have no idea how my credentials got saved in correctly, but that did the trick for me. I was able to connect to my SQL Server DB using SSMS and a Power Pivot, but not Power BI. Had a hunch that it was tied to my credentials.

[v-sadadi]

I Could not refresh the dataset published on the Power BI site with SQL Authentication credentials as we do not have an option for Database credentials on the Power BI site. Please suggest.

[Bjoern]

"I Could not refresh the dataset published on the Power BI site with SQL Authentication credentials as we do not have an option for Database credentials on the Power BI site. Please suggest."

Could you please provide a screenshot of that?

[v-sadadi]

---

@Bjoern wrote:

"I Could not refresh the dataset published on the Power BI site with SQL Authentication credentials as we do not have an option for Database credentials on the Power BI site. Please suggest."

Could you please provide a screenshot of that?

---

Hi Bjoern,

Please find below for the error I get when I try to refresh my dataset which is SQL Authenticated.

"

Something went wrong.

Failed to load data sources. Please try again later.

Activity ID30bcd8c3-756a-19ff-9b0e-2af2494b03cf

Request IDa8b8eef6-b83a-9168-3ec0-34deb1957bde

Status code404

TimeMon Dec 07 2015 15:06:14 GMT+0530 (India Standard Time)

Version11.0.9168.961

Cluster URIhttps://df-msit-scus.analysis.windows.net

"

Please help..

Thanks

Satya

[v-sadadi]

Hi Team,

I get the below error when I try refresh the Dataset published on the Power BI site.

Something went wrong.

Failed to load data sources. Please try again later.

Activity ID3d57f514-e112-c34f-68e8-6200f56160ca

Request IDfe8adc00-5033-1143-d182-17eb1ca83c8f

Status code404

TimeFri Dec 04 2015 12:07:55 GMT+0530 (India Standard Time)

Version11.0.9168.906

Cluster URIhttps://df-msit-scus.analysis.windows.net

Any quick help is highly appreciated.

Thanks

Satya

[Anonymous]

Hmm. Very strange.. Now it works fine !!? Hmm.. Well thanks for ur help anyway 🙂

[ashishrj]

@Anonymous I don't think this a an issue. I tried connecting many times and worked well. For your reference follow the steps below:

After entering server name and database name (optional), select Database and enter username and password and hit connect. Tried connecting even SQL Database on Azure VM and worked. Hope this helps!