Authentication selection for connection sharing

[viswaaa]

Hi All

My org sales team wants to create their own reports from our SQL Server database.

We have created some views in our SQL server db and gave access to only those views.

Sales is going to create their own work space and now I need to give access to the connection that I created.

So here my question is when creating a connection I took basic authentication.

Is this authentication method okay for sharing the connection with sales users? or do we need to select Windows authentication?

Please suggest

[Kedar_Pande]

@viswaaa 

1. If you used Basic Authentication (username and password), the credentials are stored with the connection. This is generally not ideal for sharing, as it can lead to security risks if credentials are shared directly or exposed.
2. Windows Authentication (or Active Directory) is typically more secure and is the preferred method

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[rajendraongole1]

Hi @viswaaa -  If feasible, you can use Windows Authentication with an on-premises data gateway to simplify management and enhance security. However, if only Basic Authentication is available, ensure that proper security practices are followed, such as using strong passwords and secure connections.

For sharing data sources with multiple users (like your sales team), Windows Authentication is generally more secure and easier to manage over time. It also integrates well with Power BI's data refresh features through the On-premises Data Gateway, which can leverage Windows Authentication without storing passwords directly.

If you must use Basic Authentication (e.g., if some users are external or do not have AD accounts), ensure that you enforce strong password policies and consider encrypting communications with SSL/TLS.

ref links:

Tutorial: Connect to on-premises data in SQL Server - Power BI | Microsoft Learn

Manage a SQL Server data source - Power BI | Microsoft Learn

[viswaaa]

Hi All,

Thanks for your replies.

If I use windows authentication do I need to give my windows credentials to connect to the database and then share this connection to other users ?

Please suggest

[shafiz_p]

Hi @viswaaa  For windows authentication, you need to provide, server name, database name, user name and password.

You can find windows user name by executing sql statement : Select SYSTEM_USER;
or using task manager. Password is your login password.

[viswaaa]

Hi @shafiz_p ,

I have 2 questions here

1.)Can I still use this windows authentication for our existing connections and share the same connections to users.Will this work ?

2)If I use windows authentication and I left organizationtomorrow.

I think this credentials has to be change right ?

[shafiz_p · Accepted solution]

Yes, you can switch/use to Windows authentication for your existing connections. In Power BI Service, update the data source settings to use Windows authentication. Users will connect using their own Windows credentials, so they don’t need your credentials.

Use a dedicated service account for the gateway to avoid disruptions when employees leave. This account should have the necessary permissions and won’t be affected by individual employee changes. Meaning the gateway should run under a service account that has access to the SQL Server database.

If your personal credentials were used, an admin will need to update the data source settings with new credentials.

Hope this helps!!