topic Re: How to enter SQL server credentials via API? in Developer

How to enter SQL server credentials via API?

robzib — Wed, 03 Aug 2022 06:44:17 GMT

I have workspace1 with one report and one dataset that's targeting db1. I managed to create workspace2 with api, moved single report and dataset from workspace1 to workspace2 and I've also updated data source to target db2 with UpdateDatasourcesInGroup endpoint. They are both on the same server.

Is there a way to enter server credentials without me going to PowerBI and manually entering it? I see that it used to be possible via connection string property but it's now obsolete.

Edit: Just to be clear I need to set username and password to my SQL instance

Re: How to enter SQL server credentials via API?

v-chenwuz-msft — Fri, 05 Aug 2022 07:07:49 GMT

Hi @robzib ,

The first step is to add the gateway connection information. But, to encrypt credentials, see Configure credentials programmatically for Power BI and review the EncryptCredentials .NET Core, Java and Python examples.

POST https://api.powerbi.com/v1.0/myorg/gateways/{gatewayId}/datasources

Gateways - Create Datasource - REST API (Power BI Power BI REST APIs) | Microsoft Docs

Part 2, get the newly added datasource from the result of step 1.

GET https://api.powerbi.com/v1.0/myorg/groups/{groupId}/datasets/{datasetId}/datasources

Datasets - Get Datasources In Group - REST API (Power BI Power BI REST APIs) | Microsoft Docs

Part 3, get the connection information from part 2 and use Update Datasources In Group.

GET https://api.powerbi.com/v1.0/myorg/groups/{groupId}/datasets/{datasetId}/datasources

Datasets - Get Datasources In Group - REST API (Power BI Power BI REST APIs) | Microsoft Docs

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: How to enter SQL server credentials via API?

robzib — Thu, 18 Aug 2022 12:12:23 GMT

Hi @v-chenwuz-msft

thank you for your reply. I forgot to mention that my databases are in Azure. From what I understand, gateways are used when we have on-premise databases, or can we use them as well with Azure?

Re: How to enter SQL server credentials via API?

v-chenwuz-msft — Fri, 19 Aug 2022 02:25:37 GMT

Hi @robzib ,

No, you can use them with Azure's Vnet gateway and do not need to install the gateway on the Azure VM.

Virtual network (VNet) data gateway architecture | Microsoft Docs

Use virtual network (VNet) data gateway and data sources in Power BI | Microsoft Docs

If you are using Azure SQL database, then you don't need a gateway. Please refer to the following post for the same issue.

Solved: Re: Gateway for Azure SQL Server - Microsoft Power BI Community

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: How to enter SQL server credentials via API?

robzib — Mon, 22 Aug 2022 17:29:58 GMT

Hi @v-chenwuz-msft

so I've decided to test gateways and see if I can make them work with the help of your links, and I'm stuck at Add data source step. I think the issue is connection details because I'm getting Bad request every time. Parameters that I'm sending are:

Data Source Type: "SQL"

Connection details: "{"server":"dell0\\dbi","database":"dbi6452"}"

Credential type: "Basic credentials"

I'm trying to do that from EncryptCredentials.sln example you attached. Google returned a couple of threads with same issues and I can't find a working example. I'm also not sure in what format should connection details be, this is copy and paste from app.powerbi when I do it via GUI over there.

Also, gateway needs to be installed with service principal account or else I can't see it with Rest API, but I managed to get some instructions online about that so that part works now.

Any help is appreciated.