Power bi data source credentials

Power bi data source credentials DEFAULT

Troubleshooting Power BI gateway (personal mode)

The following sections go through some common issues you might come across when you use the Power BI on-premises data gateway (personal mode).

Update to the latest version

The current version of the gateway for personal use is the on-premises data gateway (personal mode). Update your installation to use that version.

Many issues can surface when the gateway version is out of date. It's a good general practice to make sure you're on the latest version. If you haven't updated the gateway for a month or longer, consider installing the latest version of the gateway. Then see if you can reproduce the issue.

Installation

Gateway (personal mode) operates on 64-bit versions: If your machine is a 32-bit version, you can't install the gateway (personal mode). Your operating system has to be a 64-bit version. Install a 64-bit version of Windows or install the gateway (personal mode) on a 64-bit machine.

Gateway (personal mode) fails to install as a service even though you're a local administrator for the computer: Installation can fail if the user is in the computer’s local Administrator group, but Group Policy doesn't allow that username to sign in as a service. Ensure that Group Policy lets a user sign in as a service. We’re working on a fix for this issue. For more information, see Add the Log on as a service right to an account.

Operation timed out: This message is common if the computer (physical machine or VM) on which you’re installing the gateway (personal mode) has a single core processor. Close any applications, turn off any non-essential processes, and try installing again.

Data management gateway or Analysis Services connector can't be installed on the same computer as gateway (personal mode): If you already have an Analysis Services connector or a data management gateway installed, you must first uninstall the connector or the gateway. Then, try installing the gateway (personal mode).

Note

If you encounter an issue during installation, the setup logs can provide information to help you resolve the issue. For more information, see Setup logs.

Proxy configuration: You might see issues with configuring the gateway (personal mode) if your environment needs the use of a proxy. To learn more about how to configure proxy information, see Configure proxy settings for the on-premises data gateway.

Schedule refresh

Error: The credential stored in the cloud is missing.

You might get this error in settings for <dataset> if you have a scheduled refresh and then you uninstalled and reinstalled the gateway (personal mode). When you uninstall a gateway (personal mode), the data source credentials for a dataset that was configured for refresh are removed from the Power BI service.

Solution: In Power BI, go to the refresh settings for a dataset. In Manage Data Sources, for any data source with an error, select Edit credentials. Then sign in to the data source again.

Error: The credentials provided for the dataset are invalid. Please update the credentials through a refresh or in the Data Source Settings dialog to continue.

Solution: If you get a credentials message, it could mean:

  • The usernames and passwords that you used to sign in to data sources aren't up to date. In Power BI, go to refresh settings for the dataset. In Manage Data Sources, select Edit credentials to update the credentials for the data source.

  • Mashups between a cloud source and an on-premises source, in a single query, fail to refresh in the gateway (personal mode) if one of the sources is using OAuth for authentication. An example of this issue is a mashup between CRM Online and a local SQL Server instance. The mashup fails because CRM Online requires OAuth.

    This error is a known issue, and it's being looked at. To work around the problem, have a separate query for the cloud source and the on-premises source. Then, use a merge or append query to combine them.

Error: Unsupported data source.

Solution: If you get an unsupported data source message in Schedule Refresh settings, it could mean:

  • The data source isn't currently supported for refresh in Power BI.
  • The Excel workbook doesn't contain a data model, only worksheet data. Power BI currently only supports refresh if the uploaded Excel workbook contains a data model. When you import data by using Power Query in Excel, choose the Load option to load data to a data model. This option ensures that data is imported into a data model.

Error: [Unable to combine data] <query part>/<…>/<…> is accessing data sources that have privacy levels, which cannot be used together. Please rebuild this data combination.

Solution: This error is because of the privacy-level restrictions and the types of data sources you're using.

Error: Data source error: We cannot convert the value "[Table]" to type Table.

Solution: This error is because of the privacy-level restrictions and the types of data sources you're using.

Error: There is not enough space for this row.

Solution: This error occurs if you have a single row greater than 4 MB in size. Find the row from your data source, and attempt to filter it out or reduce the size for that row.

Data sources

Missing data provider: The gateway (personal mode) operates on 64-bit versions only. It requires a 64-bit version of the data providers to be installed on the same computer where the gateway (personal mode) is installed. For example, if the data source in the dataset is Microsoft Access, you must install the 64-bit ACE provider on the same computer where you installed the gateway (personal mode).

Note

If you have a 32-bit version of Excel, you can't install a 64-bit version ACE provider on the same computer.

Windows authentication is not supported for Access database: Power BI currently only supports Anonymous authentication for the Access database.

Error: Sign-in error when you enter credentials for a data source: If you get an error like this one when you enter Windows credentials for a data source:

Windows credential error message

You might still be on an older version of the gateway (personal mode).

Solution: For more information, see Install the latest version of Power BI gateway (personal mode).

Error: Sign-in error when you select Windows authentication for a data source using ACE OLEDB: If you get the following error when you enter data source credentials for a data source using an ACE OLEDB provider:

Data source credential error message

Power BI doesn't currently support Windows authentication for a data source using an ACE OLEDB provider.

Solution: To work around this error, select Anonymous authentication. For a legacy ACE OLEDB provider, anonymous credentials are equal to Windows credentials.

Tile refresh

If you receive an error when dashboard tiles refresh, see Troubleshooting tile errors.

Tools for troubleshooting

Refresh history

With Refresh history, you can see what errors occurred and find useful data if you need to create a support request. You can view both scheduled and on-demand refreshes. Here's how you get to Refresh history.

  1. In the Power BI nav pane, in Datasets, select a dataset. Open the More options (...) menu, and select Schedule refresh.

    Select Schedule Refresh

  2. In Settings for..., select Refresh history.

    Select Refresh history

    Refresh history information

Event logs

Several event logs can provide information. The first two, Data Management Gateway and PowerBIGateway, are present if you're an admin on the machine. If you're not an admin, and you're using the data gateway (personal mode), you'll see the log entries within the Application log.

The Data Management Gateway and PowerBIGateway logs are present under Application and Services Logs.

Data Management Gateway and PowerBIGateway logs

Fiddler trace

Fiddler is a free tool from Telerik that monitors HTTP traffic. You can see the communication with the Power BI service from the client machine. This communication might show errors and other related information.

Fiddler trace

Setup logs

If the gateway (personal mode) fails to install, you'll see a link to show the setup log. The setup log can show you details about the failure. These logs are Windows Install logs, also known as MSI logs. They can be fairly complex and hard to read. Typically, the resulting error is at the bottom, but determining the cause of the error isn't trivial. It could be a result of errors in a different log. It could also be a result of an error higher up in the log.

Link to the setup log

Or, you can go to your Temp folder (%temp%) and look for files that start with Power_BI_.

Note

Going to %temp% might take you to a subfolder of Temp. The Power_BI_ files are in the root of the Temp directory. You might need to go up a level or two.

Temp folder

Next steps

More questions? Try asking the Power BI Community.

Sours: https://docs.microsoft.com/en-us/power-bi/connect-data/service-admin-troubleshooting-power-bi-personal-gateway

We have an Azure Database which has an Admin UserName and Password

But we have also added the following to the Database In SQL Management Studio

— Create the user with Password for users that authenticate at the database

CREATE USER Org_powerbi WITH PASSWORD = 'password1';

–And Create a role

CREATE ROLE db_Org_read;

–Execute the Stored Procedure addrole member. Here the Org_powerbi user is being added to the above role

EXEC sp_addrolemember 'db_Org_read', ' Org_powerbi';

–We have a schema called org. We are granting the SELECT permission for this schema to the above Role. this means that you can perform Select data Statements

GRANT SELECT ON SCHEMA :: Org TO db_Org_read

So we added a user and granted the user SELECT permissions

When we use this user ID and password in Desktop it works fine. We can refresh the data.

However in Service (Dataset – Schedule Refresh), the login credentials are invalid

Authentication Method – Basic

Choose Basic if you plan to use SQL authentication instead of Windows authentication

Privacy level Setting for this Data Source – Organizational

An Organizational data source limits the visibility of a data source to a trusted group of people. An Organizational data source is isolated from all Public data sources, but is visible to other Organizational data sources.

The Solution

The user account you create needs to be added to the Data Source Credentials in the Power BI Service once the dataset has been uploaded.

Go to Settings

And again. Settings

And Edit Data Source Credentials of the data Set

This will add the user to the Data Source Credentials

Once added you can go back to the Schedule Refresh in the Data set in your app workspace and the Log in Credentials should now work.

Sours: https://debbiesmspowerbiazureblog.home.blog/category/power-bi/power-bi-service/power-bi-service-data-source-credentials/
  1. Houses for sale toutle wa
  2. Dragon ball z wood carving
  3. Used mobile homes sumter sc

Configure credentials programmatically for Power BI

Follow the steps in this article, to configure credentials programmatically for Power BI.

Note

  • The calling user must be a dataset owner, or a gateway admin. You can also use a service principal. For example, the service principal can be the dataset owner.
  • Cloud data sources and their corresponding credentials are managed at the user level.

Update credentials flow for data sources

  1. Call Get Datasources to discover the data sources of the dataset. In the response body for each data source, are the type, connection details, gateway, and data source ID.

  2. Build credentials string according to Update Datasource Examples depending on the credentials type.

    Note

    If you're using cloud data sources don't follow the next steps in this section. Set the credentials using the gateway ID and data source ID obtained in step 1, by calling Update Datasource.

  3. Call Get Gateway to retrieve the gateway public key.

  4. Encrypt the credentials.

    Encrypt the credentials string with the Gateway public key from step 2. Different gateway versions may have different public key sizes. Refer to the following examples in the SDK code, available from the PowerBI-CSharp GitHub repository:

  5. Build credential details with encrypted credentials.

    Use the AssymetricKeyEncryptor class with the public key retrieved in Step 3.

  6. Call Update Datasource to set credentials.

Configure a new data source for a data gateway

  1. Install the On-premises data gateway on your machine.

  2. Call Get Gateways to retrieve the gateway ID and public key.

  3. Build credential details in the same way as described in update credentials flow for data sources, using the gateway public key retrieved in step 2.

  4. Build the request body.

  5. Call the Create Datasource API.

Credential types

When you call Create Datasource or Update Datasource under an enterprise on-prem gateway using Power BI Rest API, the credentials value needs to be encrypted using the gateway's public key.

Note

.NET SDK v3 can also run the .NET SDK v2 examples listed below.

Windows and basic credentials

Key credentials

OAuth2 credentials

Anonymous credentials

Troubleshooting

No gateway and data source ID found when calling get data sources

This issue means the dataset isn't bound to a gateway. When creating a new dataset, for each cloud connection a data source with no credentials is created automatically on the user's cloud gateway. This gateway is used to store the credentials for cloud connections.

After you create the dataset, an automatic binding is created between the dataset and a suitable gateway, which contains matching data sources for all connections. If there's no such gateway or multiple suitable gateways, the automatic binding fails.

If you're using on-premises datasets, create the missing on-premises data sources, and bind the dataset to a gateway manually by using Bind To Gateway.

To discover gateways that could be bound, use Discover Gateways.

Next steps

Feedback

View all page feedback

Sours: https://docs.microsoft.com/en-us/power-bi/developer/automation/configure-credentials
How to set up a Power BI Data Source with Data Refresh

Add or remove a gateway data source

Power BI supports many on-premises data sources, and each has its own requirements. A gateway can be used for a single data source or multiple data sources. For this example, we show you how to add SQL Server as a data source. The steps are similar for other data sources.

Most data sources management operations can be performed by using APIs as well. For more information, see REST APIs (Gateways).

If you don't have a gateway installed yet, see Install an on-premises data gateway to get started.

Add a data source

  1. From the page header in the Power BI service, select SettingsSettings gear icon > Manage gateways.

    Manage gateways

  2. Select a gateway and then select Add data source. You can select the header text ADD DATA SOURCE or hover your cursor next to the gateway entry to reveal the more options menu.

    Add data source

  3. Assign a name to your data source, then select the Data Source Type. In this example, we'll choose SQL Server.

    Select SQL Server

  4. Enter information about the data source. For SQL Server, provide the Server and Database.

    Data source settings

  5. Select an Authentication Method to use when connecting to the data source. For SQL Server, choose Windows or Basic (SQL Authentication). Enter the credentials for your data source.

    Basic authentication settings.

    Note

    If the selected authentication method is OAuth:

    • Any query that runs longer than the OAuth token expiration policy may fail.
    • Cross-tenant AAD accounts are not supported If the selected authentication method is Windows:
    • Make sure that account has access on the machine. If not sure, make sure to add NT-AUTHORITY\Authenticated Users (S-1-5-11) to the local machine “Users” group.
  6. Under Advanced settings, you could configure Single Sign-On (SSO) for your data source.

    Screenshot of advanced settings.

    You can either configure Use SSO via Kerberos for DirectQuery queries, Use SSO via Kerberos for DirectQuery And Import queries or Use SSO via Azure AD for DirectQuery queries for DirectQuery-based reports and Use SSO via Kerberos for DirectQuery And Import queries for refresh-based reports.

    If you use Use SSO via Kerberos for DirectQuery queries and use this data source for a DirectQuery-based report, it will use the credentials of the user that signs in to the Power BI service. For a refresh-based report, it will use the credentials that you enter in the Username and Password fields and the Authentication method chosen.

    When you use Use SSO via Kerberos for DirectQuery And Import queries, you don't need to provide any credentials. If this data source is used for DirectQuery-based reports, it will use the user that's mapped to the (Azure) Active Directory user that signs in to the Power BI service. For a refresh-based report, it will use the dataset owner's security context.

    For more information on Use SSO via Kerberos for DirectQuery queries or Use SSO via Kerberos for DirectQuery And Import queries, see Overview of single sign-on (SSO) for gateways in Power BI.

    If you use Use SSO via Azure AD for DirectQuery queries and use this data source for a DirectQuery-based report, it will use the Azure AD token of the user who signs into the Power BI service. For a refresh-based report, it will use the credentials that you enter in the Username and Password fields and the Authentication method chosen. The Use SSO via Azure AD for DirectQuery queries option will be available only if the tenant admin allows Azure AD SSO via the on-premises data gateway and for the following data sources:

    • SQL Server
    • Azure Data Explorer
    • Snowflake

    For more information on Use SSO via Azure AD for DirectQuery queries please see Azure AD Single Sign-On (SSO) for Gateway.

  7. Under Advanced settings, optionally configure the privacy level for your data source (doesn't apply to DirectQuery).

    Privacy level selections.

  8. Select Add. You see Connection Successful if the process succeeds.

    Connection successful

You can now use this data source to include data from SQL Server in your Power BI dashboards and reports.

Remove a data source

You can remove a data source if you no longer use it. Removing a data source breaks any dashboards and reports that rely on that data source.

To remove a data source, go to the data source and then select Remove from the more options menu. The more options menu appears when you hover your cursor next to the data source name.

Remove a data source

Use the data source for scheduled refresh or DirectQuery

After you create the data source, it's available to use with either DirectQuery connections or through scheduled refresh. You can learn more about setting up scheduled refresh in Configure scheduled refresh.

Note

Server and database names must match between Power BI Desktop and the data source added to the on-premises data gateway.

The link between your dataset and the data source in the gateway is based on your server name and database name. These names must match. For example, if you supply an IP address for the server name, in Power BI Desktop, you must use the IP address for the data source in the gateway configuration. If you use SERVER\INSTANCE in Power BI Desktop, you must use the same in the data source configured for the gateway.

If you're listed in the Users tab of the data source configured in the gateway, and the server and database name match, you see the gateway as an option to use with scheduled refresh.

Gateway connection

Warning

If your dataset contains multiple data sources, each data source must be added in the gateway. If one or more data sources aren't added to the gateway, you won't see the gateway as available for scheduled refresh.

Considerations and limitations

OAuth is a supported authentication scheme only for custom connectors with the on-premises data gateway.

Manage users

After you add a data source to a gateway, you give users and email-enabled security groups access to the specific data source (not the entire gateway). The access list for the data source controls only who is allowed to publish reports that include data from the data source. Report owners can create dashboards and apps, and then share those items with other users.

You can also give users and security groups administrative access to the gateway.

Note

Users with access to the data source can associate datasets to the data source, and connect, based on the security options (either the stored credentials or Single Sign-On) selected while creating a data source.

Add users to a data source

  1. From the page header in the Power BI service, select SettingsSettings gear icon > Manage gateways.

  2. Select the data source where you want to add users.

  3. Select Users, and enter the users and mail-enabled security groups from your organization who will access the selected data source. Select Add, and the added member's name is added to the list of people who can publish reports that use this data source.

    Add user

Remember that you need to add users to each data source that you want to grant access to. Each data source has a separate list of users. Add users to each data source separately.

Remove users from a data source

On the Users tab for the data source, you can remove users and security groups that use this data source.

Remove user

Store encrypted credentials in the cloud

When you add a data source to the gateway, you must provide credentials for that data source. All queries to the data source will run by using these credentials. The credentials are encrypted securely. They use symmetric encryption so that they can't be decrypted in the cloud before they're stored in the cloud. The credentials are sent to the machine that runs the gateway, on-premises, where they're decrypted when the data sources are accessed.

List of available data source types

For information about which data sources the on-premises data gateway supports, see Power BI data sources.

Next steps

More questions? Try the Power BI Community.

Sours: https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-data-sources

Credentials source bi power data

Automated updation of Power BI Data Source Credentials

They say that Data is the new oil. What they mean is that nowadays, data is currency. Hence, going by the human tendency, there will attempt to steal data (popularly called hacking). Hence, data security is a major concern for organizations across the spectrum. One of the security loopholes any organization can have is stale credentials. Let me explain what do I mean by that with an example of a Power BI data source.

Power BI can connect to a variety of data sources. One of them is Azure SQL Database. As a best practice, organizations use SQL authentication with service users. However, over time, if the usernames and passwords are stagnant, there is a possibility of accidental or deliberate leakages. Thus, it is good practice to have the passwords updated at regular intervals.

Nonetheless, from a DevOps standpoint, it would be desirable to have this process automated. And, as far as automation is concerned, what else is better than shell scripting and in the case of Microsoft stack, it is Powershell.

Also read: Conditional formatting in PowerBI

Pre-requisites

  • A windows system with Powershell installed.
  • Powershell module ‘Az’
  • Powershell module ‘MicrosoftPowerBIMgmt’

For more on the module MicrosoftPowerBIMgmt, refer to this link.

Steps to update Power BI Data Source Credentials

  • Extract Authorization Token
  • Get workspace details.
  • Get Dataset Object
  • Find Data Source Object
  • Update credentials

Step 1: Extract Authorization Token.

Before doing anything else, we need to extract authorization to Power BI. The following code will help you achieve the same:

$PbiUserName  = <MyUserName> $PbiPassword = <MyPassword> $PbiSecurePassword = ConvertTo-SecureString $PbiPassword-Force -AsPlainText $PbiCredential = New-Object Management.Automation.PSCredential($PbiUserName  , $PbiSecurePassword) $PBIcredential = New-Object System.Management.Automation.PSCredential($PbiUserName  , $PbiSecurePassword) Connect-PowerBIServiceAccount -Credential $PBIcredential $authToken= Get-PowerBIAccessToken -AsString

Step 2: Get workspace details.

Before one gets to update the data source credentials, it is imperative to connect to the workspace. The following cmdlet will fetch the PowerBI workspace details for you.

$WorkspaceObject = (Get-PowerBIWorkspace -Scope Individual -Name '<My Workspace>')

Step 3: Get Dataset details.

Once you have the Workspace object details, the following cmdlet will fetch the requisite PowerBI dataset details for you.

$PbiDatasetObject = (Get-PowerBIDataset -Name '<My Dataset>' -WorkspaceId $WorkspaceObject.Id)

Step 4: Get Data Source details.

Next, we need to find the underlying data source details for the requisite details. The following cmdlet will fetch the requisite Datasource details for you.

$PbiDataSourcesObject = (Get-PowerBIDatasource -DatasetId $PbiDatasetObject.Id -Scope Individual)

Step 5: Update Power BI Data Source Credentials

Finally comes the most important step, i.e. updating credentials. Here we use the Power BI REST API with the PATCH method:

#Creating Auth headers [email protected]{'authorization'=$authToken} #Getting Datasource and Gateway ID $DataSourceId = $PbiDataSourcesObject.DatasourceId $GatewayId=$PbiDataSourcesObject.GatewayId $DSAuthenticationMethod = "Basic" #Patch API $patch_api_credentials = "https://api.powerbi.com/v1.0/myorg/gateways/{gateway_id}/datasources/{datasource_id}"; $patch_api_credentials_updated = patch_api_credentials.replace("{gateway_id}",$GatewayId).replace("{datasource_id}",$DataSourceId); #User Name and Password $DSUserName = "<SQL User Name>" $DSPassword = "<New SQL Password>" $credProperties = @{ username= $DSUserName ; password = $DSPassword ; } $cred = New-Object PSObject -Property $credProperties $request = @{ credentialType = $DSAuthenticationMethod ; basicCredentials = $credObject } $requestBody = new-object psobject -Property $request $requestBodyJson = $requestBody | ConvertTo-Json; Invoke-WebRequest -UserAgent "" -DisableKeepAlive -Uri $patch_api_credentials_updated -Method Patch -ContentType "application/json" -Body $requestBodyJson -Headers $headers -UseBasicParsing

Pro Tip: Avoid the $ symbol in your credentials

Conclusion

Hope this article helps. Please note that this article is only for information purposes. We don’t claim and guarantees or liabilities whatsoever.


TAGpowerbipowershell

Previous PostHow to detect Data Drift in Azure Machine LearningNext PostWhy is it good to do Feature Transforms for Deep Neural Networks?


Prasad Kulkarni

I am a Data Analytics professional with 5+ years of experience.


Sours: https://www.data4v.com/automated-updation-of-power-bi-data-source-credentials/
Making data source parameters easy in Power BI Desktop

I heard from girls about such toys, but this is the first time I've seen. I licked the head and, without hesitation, began to send him to my house. But the dimensions. Alas, it was not possible to completely place the phallus in itself. But, all the same, such a pleasure: I fell asleep on my parents' bed.

Similar news:

I was still swaddled with my arms twisted back. My thighs were still divorced, there was a dense piece of wood in the ass, the same sprouts were stuck in the vagina, on the nipples and clitoris, but now I was hanging with my stomach down, and the tree resiliently swayed mine. breasts behind the nipples. Well, my mouth was inexorably stretched in anticipation of a green member.



1831 1832 1833 1834 1835