PBI connection to MS SQL using JDBC connection string

[Anonymous]

Hello Team,

SQL server Data base is a managed DB and login credentials are not provided, rather connection is being established using AD-ENT login and IAM password,

Looking forward for a way to see how AD-ENT login and IAM password can be used to make a connection from Power BI to MS SQL server.

Also looking for options for connecting to PowerBI using JDBC connect string,

Thanks in advance!

[Syndicate_Admin]

To rotate a key every 60 days in an Azure Function app using PowerShell, you can use the following script as an example:

```powershell
param($Timer)

$KeyVaultName = ""
$SecretName = ""
$RotationDays = 60

$currentDateTime = Get-Date
$keyRotationDateTime = Get-AzureKeyVaultSecret -VaultName $KeyVaultName -Name $SecretName | Select-Object -ExpandProperty Attributes | Select-Object -ExpandProperty Updated

# Convert the last rotation date to a DateTime object
$lastRotationDateTime = [DateTime]::Parse($keyRotationDateTime)

$daysSinceRotation = ($currentDateTime - $lastRotationDateTime). Days

if ($daysSinceRotation -ge $RotationDays) {
# Generate a new key value
$newKeyValue = New-Guid

# Update the key value in Azure Key Vault
Set-AzureKeyVaultSecret -VaultName $KeyVaultName -Name $SecretName -SecretValue $newKeyValue

Write-Host "Key has been rotated successfully."
} else {
Write-Host "No action required."
}
```

Ensure you have configured your Function app with a Timer trigger that triggers the script periodically. Adjust the values of `$KeyVaultName` and `$SecretName` with the appropriate names for your Key Vault and Secret.

The script checks the number of days that have passed since the last key rotation. If it exceeds the specified rotation period (60 days in this example), a new key is generated and updated in the Azure Key Vault. Otherwise, the script outputs that no action is required.

Remember to publish and configure your Azure Function app with the appropriate settings according to your requirements.

[v-zhangti · Accepted solution]

Hi, @Anonymous 

To connect from Power BI to MS SQL server using AD-ENT login and IAM password, you need to use the Microsoft ODBC Driver 13 or later. This driver supports Azure Active Directory (AAD) authentication, which is required for AD-ENT login and IAM password.

Here are the steps to follow:

• 1. Download and install the Microsoft ODBC Driver 13 or later on your computer.
• 2. In Power BI Desktop, on the Home tab, select Get data > ODBC.
• 3.In the From ODBC dialog box, select the DSN (Data Source Name) that corresponds to your SQL server database, or enter a connection string manually.
• 4. In the SQL Server Authentication dialog box, select Azure Active Directory - Password as the authentication method, and enter your AD-ENT login and IAM password.
• 5.Select Connect to access your SQL server database.

You can also use a JDBC connection string to connect from Power BI to MS SQL server2, but you need to make sure the Data Connectivity mode is set to Import.

Connect from Power BI to Azure SQL Database using AAD authentication - Microsoft Community Hub

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

Best Regards,

Community Support Team _Charlotte

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