Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
shashank
Helper III
Helper III

power query connecting to sql server(user id and password) is not working on another user computer

Hi Team,

 

i need your help.

 

i have created one model which is using power query, in this model i'm connecting to sql server using user id and password authentication.It works fine for me but gives authentication error when used by another user.How can we fix that ?

1 ACCEPTED SOLUTION

Hi @shashank,

Regarding to the Power Query issues, I would recommend you post the question in the Power Query forum to get dedicated support.

In addition, you can consider to use Power BI Desktop to connect to SQL Server in import mode, then create model and reports. This way, when you send the Power B Desktop to other users, they don’t need to enter credentials.

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

8 REPLIES 8
BhaveshPatel
Community Champion
Community Champion

Are you using the same file to connect to SQL Server using the different user names? If yes, You should try and create parameters for connecting to separate instance of SQL Server

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

Hi Bhavesh,

 

type of security in sql server is user name and password, i supplied the same intially when i was connecting to sql server.

another user is supposed to use same credential but i'm assuming that credentials are saved somewhere in power query and another user will not provide any credential while reunning that model, as another user is business user who has no idea regarding those credentials.please correct me if my understanding is wrong..

PowerQuery saves all authentication data to an encrypted file on your local hard drive instead of the PowerBI File. 

Thanks & Regards,
Bhavesh

Love the Self Service BI.
Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to give Kudos.

What is the way of sharing such model which connects to sql server and we do not want user to enter any credentials, any suggestions.

Hi @shashank,

I assume that you connect to SQL Server using Direct Query mode in Power BI Desktop. I test this scenario, in Direct Query mode, the credential used to connect to SQL Server isn’t saved in Power BI Desktop file after I send the PBIX file to my colleague. She got the following screenshot which prompts her to enter credential.
1.PNG

In order to view the report, I should add her as a login in SQL Server or tell her the SQL login and password I have used to connect to SQL Server in Power BI Desktop.

However, when connecting to SQL Server using import mode in Power BI Desktop, the credential used to connect to SQL Server is saved in the PBIX file, and in this scenario, the user who gets the PBIX file can view reports without entering any credentials.

In a word, you would need to switch from Direct Query mode to import mode in your Power BI Desktop file, then send the PBIX file to your user. If you persist to reserve Direct Query mode, you would need to publish the reports to Power BI Service, and create a dashboard for the reports and share the dashboard to user. Please note the user would require pro license to view the reports.

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Lydia,

 

Thanks for your inputs.

 

Scenaraio here is little bit different.

 

I'm not using power BI desktop.what i'm doing is.

 

1-Creating a model only in Excel.

2-Connecting sql server through power query add-in in 2013 version.

3-importing data , doing some clean up then calculation.

4-Throwing that data on one excel sheet and use that in other charts and pivots.

 

I want to share that excel model to other users but want they should not be prompt for any password, any suggetions ?

 

Regards

Shashank

Hi @shashank,

Regarding to the Power Query issues, I would recommend you post the question in the Power Query forum to get dedicated support.

In addition, you can consider to use Power BI Desktop to connect to SQL Server in import mode, then create model and reports. This way, when you send the Power B Desktop to other users, they don’t need to enter credentials.

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Lydia,

 

Thanks a lot for your inputs.

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors