How to enter SQL server credentials via API?

[robzib]

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

[v-chenwuz-msft]

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.

[robzib]

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.

[robzib]

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?

[v-chenwuz-msft]

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.