Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The 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.

Reply
sanoj
Frequent Visitor

Custom Connector to connect the DB with ODBC driver using AZURE AD Guest User

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?

1 REPLY 1
sanoj
Frequent Visitor

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
])

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Top Solution Authors
Top Kudoed Authors