cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kimpanu
Frequent Visitor

Custom Connector works in PowerBI Desktop but not in PowerBI Service

Hi,

 

I have develop an Custom Connector and that is just work fine in PowerBI Desktop, but when i try to add det source in my Gateway i got an problem when i hit the button add. I use OAuth2 flow to authenticate agains the source that is an GraphQL API. my authentication provider is Auth0.

 

I have impl. an TestConnection that is required in PowerBi Service.

 

Here is my code:

 

 

 

// This file contains your Data Connector logic
section MyConnector;

// Auth0 variables
client_id = "xxxxxxxxx";
redirect_uri = "https://oauth.powerbi.com/views/oauthredirect.html";
authorize_uri = "https://xxxxxx.eu.auth0.com/authorize";
token_uri = "https://xxxxxx.eu.auth0.com/oauth/token";
windowWidth = 1200;
windowHeight = 1000;
codeVerifier = Text.NewGuid() & Text.NewGuid();
logout_uri = "https://xxxxxx.eu.auth0.com/v2/logout?client_id=" & client_id;

// My API variables
vUrl = "https://api.myapp.se";
vPath = "graphql";

[DataSource.Kind="MyConnector", Publish="MyConnector.Publish"]
shared MyConnector.Contents = Value.ReplaceType(MyConnectorImpl, MyConnectorType);

MyConnectorType = type function (
    companyID as (type text meta [
        Documentation.FieldCaption = Extension.LoadString("CompanyIDLabel"),
        Documentation.FieldDescription = Extension.LoadString("CompanyIDDescription"),
        Documentation.SampleValues = { "32", "1" }
    ]))
    as table meta [
        Documentation.Name = Extension.LoadString("ApplicationName"),
        Documentation.LongDescription = Extension.LoadString("ApplicationDescription")
    ];

NavigationTable.Simple = (companyID as text) =>
    let
        objects = #table(
            {"Name", "Key", "Data", "ItemKind", "ItemName", "IsLeaf"},
            {
                {"Kunder", "Kund ID", GetCustomers(companyID), "Table", "Table", true},
                {"Orders", "Order ID", GetOrders(companyID), "Table", "Table", true},
                {"Delordrar", "Delorder ID", GetSubOrders(companyID), "Table", "Table", true},
                {"Tidrader", "Tidrad ID", GetTimeRows(companyID), "Table", "Table", true},
                {"Materialrader", "Material ID", GetMaterialRows(companyID), "Table", "Table", true},
                {"Kundfakturor", "Kundfaktura ID", GetCustomerInvoices(companyID), "Table", "Table", true},
                {"Datum", "Datum", GetDateTable(companyID), "Table", "Table", true}
            }),
        NavTable = Table.ToNavigationTable(objects, {"Key"}, "Name", "Data", "ItemKind", "ItemName", "IsLeaf")
    in
        NavTable;

MyConnectorImpl = (companyID as text) =>
    let
        NavTable = NavigationTable.Simple(companyID)
    in
        NavTable;

// Data Source Kind description
MyConnector = [
    TestConnection = (dataSourcePath) => 
        let
            json = Json.Document(dataSourcePath),
            companyID = json[companyID]
        in
            { "MyConnector.Contents", companyID },
    Authentication = [
        OAuth =  [
            StartLogin=StartLogin,
            FinishLogin=FinishLogin,
            Refresh=Refresh,
            Logout=Logout
        ]
    ],
    Label = Extension.LoadString("DataSourceLabel")
];

// Data Source UI publishing description
MyConnector.Publish = [
    Beta = true,
    Category = "Other",
    ButtonText = { Extension.LoadString("ButtonTitle"), Extension.LoadString("ButtonHelp") },
    LearnMoreUrl = "https://app.myapp.se",
    SourceImage = MyConnector.Icons,
    SourceTypeImage = MyConnector.Icons
];

// StartLogin helper method
StartLogin = (resourceUrl, state, display) =>
    let
        params = Json.Document(resourceUrl),
        codeChallenge = Base64UrlEncodeWithoutPadding(Crypto.CreateHash(CryptoAlgorithm.SHA256, Text.ToBinary(codeVerifier, TextEncoding.Ascii))),
        AuthorizeUrl = authorize_uri & "?" & Uri.BuildQueryString(
        [
            client_id = client_id,
            scope = "openid offline_access",
            response_type = "code",
            state = state,
            redirect_uri = redirect_uri,
            code_challenge_method = "S256",
            code_challenge = codeChallenge
        ])
    in
        [
            LoginUri = AuthorizeUrl,
            CallbackUri = redirect_uri,
            WindowHeight = windowHeight,
            WindowWidth = windowWidth,
            Context = []
        ];

// Refresh method
Refresh = (resourceUrl, refresh_token) =>
    let
         result =  TokenMethod("refresh_token", "refresh_token", refresh_token)
    in
        result;

// FinishLogin Method
FinishLogin = (context, callbackUri, state) =>
    let
        parts = Uri.Parts(callbackUri)[Query],
        // if the query string contains an "error" field, raise an error
        // otherwise call TokenMethod to exchange our code for an access_token
        result = if (Record.HasFields(parts, {"error", "error_description"})) then 
                    error Error.Record(parts[error], parts[error_description], parts)
                 else
                    TokenMethod("authorization_code", "code", parts[code])
    in
        result;

// Helper method to parse
Base64UrlEncodeWithoutPadding = (hash as binary) as text =>
    let
        base64Encoded = Binary.ToText(hash, BinaryEncoding.Base64),
        base64UrlEncoded = Text.Replace(Text.Replace(base64Encoded, "+", "-"), "/", "_"),
        withoutPadding = Text.TrimEnd(base64UrlEncoded, "=")
    in 
        withoutPadding;

// Token function.
TokenMethod = (grantType, tokenField, code) =>
    let
        queryString = [
            grant_type = grantType,
            redirect_uri = redirect_uri,
            client_id = client_id,
            code_verifier = codeVerifier
        ],
        queryWithCode = Record.AddField(queryString, tokenField, code),

        tokenResponse = Web.Contents(token_uri, [
            Content = Text.ToBinary(Uri.BuildQueryString(queryWithCode)),
            Headers = [
                #"Content-type" = "application/x-www-form-urlencoded",
                #"Accept" = "application/json"
            ],
            ManualStatusHandling = {400} 
        ]),
        body = Json.Document(tokenResponse),
        result = if (Record.HasFields(body, {"error", "error_description"})) then 
                    error Error.Record(body[error], body[error_description], body)
                 else
                    body
    in
        result;

Logout = (token) => logout_uri;

MyConnector.Icons = [
    Icon16 = { Extension.Contents("MyApp_symbol_16x16.png"), Extension.Contents("MyApp_symbol_20x20.png"), Extension.Contents("MyApp_symbol_24x24.png"), Extension.Contents("MyApp_symbol_32x32.png") },
    Icon32 = { Extension.Contents("MyApp_symbol_32x32.png"), Extension.Contents("MyApp_symbol_40x40.png"), Extension.Contents("MyApp_symbol_48x48.png"), Extension.Contents("MyApp_symbol_64x64.png") }
];

Table.ToNavigationTable = (
    table as table,
    keyColumns as list,
    nameColumn as text,
    dataColumn as text,
    itemKindColumn as text,
    itemNameColumn as text,
    isLeafColumn as text
) as table =>
    let
        tableType = Value.Type(table),
        newTableType = Type.AddTableKey(tableType, keyColumns, true) meta 
        [
            NavigationTable.NameColumn = nameColumn, 
            NavigationTable.DataColumn = dataColumn,
            NavigationTable.ItemKindColumn = itemKindColumn, 
            Preview.DelayColumn = itemNameColumn, 
            NavigationTable.IsLeafColumn = isLeafColumn
        ],
        navigationTable = Value.ReplaceType(table, newTableType)
    in
        navigationTable;

 

 

 

 

Here is the gateway log:

 

 

 

 

DM.EnterpriseGateway Error: 0 : 2022-09-29T13:25:11.8436134Z DM.EnterpriseGateway 6a5a8e84-c16d-4602-8374-6775d0827dec c273944c-b50a-4398-ae07-26174c524917 MDSR dea6680f-5010-4181-9cf0-1bf6fd481fa6 1bd6d3fe-a317-4023-b929-36b8642e03ad 1bd6d3fe-a317-4023-b929-36b8642e03ad 7189CA09 [DM.GatewayCore] Error processing request: [0]Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.OAuthTokenLoginFailedException: Failed to call FinishLogin with OAuth token or failed with StartLogin, exception:

GatewayPipelineErrorCode=DM_GWPipeline_Client_OAuthTokenLoginFailedError
GatewayVersion=
FailureMessage= --->
Inner exception chain: Microsoft.Mashup.OAuth.OAuthException > Microsoft.Mashup.Engine.Interface.ResourceAccessForbiddenException
<pi>Microsoft.Mashup.OAuth.OAuthException: Exception of type 'Microsoft.Mashup.Engine.Interface.ResourceAccessForbiddenException' was thrown. ---> Microsoft.Mashup.Engine.Interface.ResourceAccessForbiddenException: Exception of type 'Microsoft.Mashup.Engine.Interface.ResourceAccessForbiddenException' was thrown.
at Microsoft.Mashup.Engine1.Library.Http.Request.GetResponse(ResourceCredentialCollection credentials, RetryPolicy retryPolicy, SecurityExceptionCreator securityExceptionCreator, Boolean tokenRefreshed)
at Microsoft.Mashup.Engine1.Library.Web.WebContentsBinaryValue.GetResponse(Request request)
at Microsoft.Mashup.Engine1.Library.Web.WebContentsBinaryValue.OpenStream()
at Microsoft.Mashup.Engine1.Library.Web.WebContentsBinaryValue.Open()
at Microsoft.Mashup.Engine1.Runtime.BinaryValue.OpenText(Encoding encoding)
at Microsoft.Mashup.Engine1.Library.Json.JsonModule.Json.DocumentFunctionValue.TypedInvoke(Value jsonText, Value encoding)
at Microsoft.Mashup.Engine1.Runtime.NativeFunctionValue2`3.Invoke(Value arg0, Value arg1)
at Microsoft.Mashup.Engine1.Language.DebugInstruction.Execute(MembersFrame1& frame)
at Microsoft.Mashup.Engine1.Language.MembersFunctionValue1.Invoke(Value arg0)
at Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.Force(Int32 index)
at Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.get_Item(Int32 index)
at Microsoft.Mashup.Engine1.Language.InstructionInvocationInstruction2.Execute(MembersFrame1& frame)
at Microsoft.Mashup.Engine1.Language.Instruction.ExecuteCondition(MembersFrame1& frame)
at Microsoft.Mashup.Engine1.Language.DebugInstruction.ExecuteCondition(MembersFrame1& frame)
at Microsoft.Mashup.Engine1.Language.IfInstruction.Execute(MembersFrame1& frame)
at Microsoft.Mashup.Engine1.Language.DebugInstruction.Execute(MembersFrame1& frame)
at Microsoft.Mashup.Engine1.Language.MembersFunctionValue1.Invoke(Value arg0)
at Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.Force(Int32 index)
at Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.get_Item(Int32 index)
at Microsoft.Mashup.Engine1.Language.RuntimeFunctionValueN.Invoke(Value[] args)
at Microsoft.Mashup.Engine1.Language.DebugInstruction.Execute(MembersFrame1& frame)
at Microsoft.Mashup.Engine1.Language.DebugInstruction.Execute(MembersFrame1& frame)
at Microsoft.Mashup.Engine1.Language.MembersFunctionValue1.Invoke(Value arg0)
at Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.Force(Int32 index)
at Microsoft.Mashup.Engine1.Language.RecordInstruction.RuntimeRecordValue.get_Item(Int32 index)
at Microsoft.Mashup.Engine1.Language.RuntimeFunctionValueN.Invoke(Value[] args)
at Microsoft.Mashup.Engine1.Runtime.Extensibility.ExtensionOAuthFactory.Provider.Microsoft.Mashup.OAuth.IOAuthProvider.FinishLogin(Byte[] serializedContext, Uri callbackUri, String state)
--- End of inner exception stack trace ---
at Microsoft.Mashup.Engine1.Runtime.Extensibility.ExtensionOAuthFactory.Provider.Microsoft.Mashup.OAuth.IOAuthProvider.FinishLogin(Byte[] serializedContext, Uri callbackUri, String state)
at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayOAuthUtils.FinishLogin(IOAuthProvider provider, CredentialData credentialData)
at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.OAuthTokenCache.GatewayOAuthTokenCache.<UpdateCredentialsAsync>d__8.MoveNext()</pi>
--- End of inner exception stack trace ---
([0]Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.OAuthTokenLoginFailedException.StackTrace:)
at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.OAuthTokenCache.GatewayOAuthTokenCache.<UpdateCredentialsAsync>d__8.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayOAuthUtils.<GetOAuthFinishLoginOnGatewayResultAsync>d__7.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayProcessor.<OAuthFinishLoginOnGatewayAsync>d__24.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayProcessorDispatcher.<DispatchImpl>d__1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.GatewayProcessorDispatcher.<Dispatch>d__0.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.Serialization.GatewayDeserializer.<>c__DisplayClass8_0.<<Deserialize>b__0>d.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.PowerBI.DataMovement.Pipeline.GatewayPipelineTelemetry.PipelineTelemetryService.<ExecuteInActivity>d__7`1.MoveNext()
--- End of stack trace from previous location where exception was thrown ---
at System.Runtime.ExceptionServices.ExceptionDispatchInfo.Throw()
at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task)
at Microsoft.PowerBI.DataMovement.Pipeline.GatewayCore.Serialization.GatewayDeserializer.<Deserialize>d__8.MoveNext()

 

 

 

 

Someone that has an solution on my problem?

 

1 ACCEPTED SOLUTION
kimpanu
Frequent Visitor

The problem is now solved, it was the codeVerifier that was incorrect, because it on service was tested with the refresh_token flow that never worked and never been tested in powerBi desktop. I changed it to an static code instead of generetade by guid.

View solution in original post

3 REPLIES 3
kimpanu
Frequent Visitor

The problem is now solved, it was the codeVerifier that was incorrect, because it on service was tested with the refresh_token flow that never worked and never been tested in powerBi desktop. I changed it to an static code instead of generetade by guid.

kimpanu
Frequent Visitor

@v-binbinyu-msft it could be that, but the exact same code works in PowerBI Desktop so why should´t work when depoly to PowerBI Service, so i dont understand to fix it when it work in desktop app?!

v-binbinyu-msft
Community Support
Community Support

Hi @kimpanu ,

As the gateway log shown, please confirm that the token is correct and the data source support OAuth authentication.

vbinbinyumsft_0-1664518261911.png

 

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Helpful resources

Announcements
November 2022 Update

Check it Out!

Click here to read more about the November 2022 updates!

Microsoft 365 Conference â__ December 6-8, 2022

Microsoft 365 Conference - 06-08 December

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors