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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

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
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors