Cannot execute N1QL queries with Couchbase .NET SDK 3.0

.NET SDK
, ,
1

Hello,

I am currently moving our codebase over from .NET SDK 2.7 to .NET SDK 3.0. I am trying to run the following code:

            var result = await cluster.QueryAsync<Accounts>("SELECT DISTINCT b.* FROM `My_Bucket` b 
WHERE b.docType='accounts'");

            if (result.MetaData.Status != QueryStatus.Success)
            {
                throw new ApplicationException("One or more errors were returned from the query.");
            }

            List<Accounts> accounts = new List<Accounts>();
            await foreach (Accounts account in result)
            {
                accounts.Add(account);
            }

When I step through the code the result.MetaData.Status has the status of “Success”, but once I enter the async foreach loop it does not iterate and jumps to the next line of code which asserts that there were 0 results.

When I investigate the result object again from the query it now says the Status is fatal with the following error message:
“User does not have credentials to run SELECT queries on the My_Bucket bucket. Add role query_select on My_Bucket to allow the query to run.”

I never ran into this issue in 2.7 and I am not sure what has changed. The user role I am using is able to execute N1QL queries. I am able to retrieved documents by ID, as well as update and create new ones. I just cannot execute queries in SDK 3.0 for some reason.

Any help would be greatly appreciated.

2

It has to do with streaming results from the server. The server can’t know, in some circumstances, that there is an error until after the results are streamed. I’d recommend moving the error check after the iteration.

That said, I’m surprised that’s the case for this error. I would expect it to find the error in the JSON object before the empty collection of result rows. Can you run the query on your server with curl or Postman and put the raw JSON response here? It’s possible we have a case where we need to tweak the SDK, I’d frankly expect it to throw an exception for this error case.

3

I see. I will try to see if I can get the JSON response from the server. But for the actual error message itself. Would there be any reason why I can run queries with the same account in .NET SDK 2.7 but not .NET SDK 3.0? I can login to the cluster and run the query in the query editor with no issues. I just can’t seem to do it through the SDK.

4

@RichC94 -

No I don’t think so - the credentials are simply passed to the server as before. What Couchbase Server version are you using?

-Jeff

5

I am using Enterprise version 6.5.

6

If it helps, the reason the query is failing is authentication, the user logged in via the SDK doesn’t have access to run queries on the bucket “My_Bucket”. That should fix the error, though it still doesn’t fix your concern about how the error is returned via the SDK.

1 Like
7

Understood. I am working with my database team to see if something has changed with the user account’s roles. For some reason in SDK 2.7 the roles were sufficient enough to run N1QL queries. Perhaps something has changed in Couchbase server 6.5 and SDK 3.0. As for how the error is handled, I will simply move the validation after I have iterated over the stream.

8

Even with the added roles, I still seem to be getting the same error message. These are the roles I have on the account:

  • Views Reader
  • Query Update
  • Query Select
  • Query Insert
  • Query Delete
  • Data Writer
  • Data Reader
  • Application Access
9

There should be some details on the server side in the logs about what permission is missing.

10

@RichC94

Additionally, I’ve done some research into why you weren’t getting an exception, as you would expect, before iterating the collection. This appears to be an interesting difference in how Couchbase Server is returning results when an authentication error occurs.

When most errors occur, they appear in an errors array in the JSON response, and there is no results array. However , this particular authentication error appears after an empty results array. Since we’re streaming the results, we can’t see the errors until after you iterate.

I’m not quite sure why this error returns differently than others, but regardless I think it’s a case we should account for. I’ve filed an issue to track this improvement: https://issues.couchbase.com/browse/NCBC-2657

11

Thanks for the help everyone. It looks like the issue is that the development environment is not using TLS. I Had to set it to false in the ClusterOptions class. Now I am able to execute queries. I will work with my database team to rectify that.

EDIT: It looks TLS is enabled. But for some reason the SDK has a problem talking to port 18093. When TLS is enabled I can talk to Couchbase on 18091 but I get the error when talk to 18093. This may explain why I can get documents by their key, but not by a N1QL query when TLS is enabled.

12

Want to post an update as I solved my issue finally. I was originally using this part of the documentation: https://docs.couchbase.com/dotnet-sdk/current/howtos/sdk-authentication.html#authenticating-a-net-client-by-certificate thinking that I needed to point or upload the certificate that was used by the server. This was causing my issue. When I removed the code using the certificate store, the SDK was able to find the certificate and I was able to query without issue. Not sure why this is the case, but my code is now working.

2 Likes