Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
We are creating a Customer Connector for Power BI Desktop using a modified source code of SqlODBC sample from the Git repository. Integrated OAuth authentication with this project.
ODBC Driver is Config_DriverName = "ODBC Driver 18 for SQL Server"
But we are not able to prepare the ODBC connection string properly for OAuth authentication. We tried different combinations but Login fails consistently.
UsernamePassword authentication is working fine.
The code section is provided below.
CredentialConnectionString =
if (Credential[AuthenticationKind]? = "OAuth") then
[ database = "DBName" ]
else
error Error.Record("Error", "Unhandled authentication kind: " & Credential[AuthenticationKind]?),
Our requirement is to Login using an Organization Account(AAD Guest user) and import the tables and views coming under the “Analytical” schema. Please suggest how to prepare CredentialConnectionString for OAuth authentication. Or if there is a better way to do this?
In the custom connector, this is how we are trying to connect to the Azure SQL server using the ODBC data source with an OAuth access token.
1) First we will authenticate the user using with AZURE AD guest user account and get the access token.
2) Then we will try to connect to the Azure SQL server using the ODBC data source with the access token we got in the first step. Below is the code snippet we are using to connect to the Azure SQL server using the ODBC Data source. Our doubt is how to pass the access token in the below-provided code ? And also the elements of the credential connection string ?
OdbcDatasource = Odbc.DataSource(ConnectionStringNoNulls, [
// A logical (true/false) that sets whether to view the tables grouped by their schema names
HierarchicalNavigation = true,
// Allows upconversion of numeric types
SoftNumbers = true,
// Allow upconversion / resizing of numeric and string types
TolerateConcatOverflow = true,
// Enables connection pooling via the system ODBC manager
ClientConnectionPooling = true,
// These values should be set by previous steps
CredentialConnectionString = CredentialConnectionString,
SqlCapabilities = SqlCapabilities,
SQLColumns = SQLColumns,
SQLGetInfo = SQLGetInfo,
SQLGetTypeInfo = SQLGetTypeInfo
])