PowerBI and CouchBase

Hi,

I tried to follow the steps described by CB engineer here to see whether we can use PowerBI as our initial tool for reporting and BI.

The post describes the structure of the REST API URL, but it fails to explain how the integration can be done if the bucket / server require authentication.

I tried using this post with multiple options for user:password combination, such as:

local:server_user:server_password (i.e. local:Admin:xxxxx)
server_user:server_password (i.e. Admin:xxxxx)
server_user:bucket_password (i.e. Admin:yyyyy)

For all 3 options PowerBI fails connecting, reporting the following errror:
Authorization Failed Keyspace BUCKET_NAME

Can someone please explain me how to properly connect to the N1QL Query REST API with authentication credentials (either on request or HTTP Headers level)???

Thank you in advance,
Alex

Hi @prasad, @johan_larson, please help with this.

Hi @alex1 ,
Here is an example to pass credentials to N1QL:
http://developer.couchbase.com/documentation/server/4.0/n1ql/n1ql-rest-api/exauthrequest.html

If you have password protected bucket, you can use:
curl -v http://travel-sample:hello@localhost:8093/query/service -d “statement=SELECT * from `travel-sample\ LIMIT 5`”

-Prasad

Hi Prasad,

  1. What local: in this example stands for?

  2. We have password-protected buckets. In the CURL example - does “hello” stand for the bucket’s password?

Thanks!
Alex

  1. Its documented in the REST reference. Look for “Authentication parameters” http://developer.couchbase.com/documentation/server/4.0/n1ql/n1ql-rest-api/executen1ql.html

“local:” means, what follows is a bucket name. If the prefix is “admin:” then it indicates the admin user. For example:

a) this uses bucket (local:) as user and password:
curl -v http://localhost:8093/query/service -d 'statement=SELECT * fromtravel-samplelimit 1&creds=[{"user":"local:travel-sample", "pass":"hello"}]'

b) this uses administrator/password, and hence doesn’t need bucket password.
curl -v http://localhost:8093/query/service -d ‘statement=SELECT * from travel-sample limit 1&creds=[{“user”:“admin:Administrator”, “pass”:“password”}]’
* Connected to localhost (::1) port 8093 (#0)
> POST /query/service HTTP/1.1
> User-Agent: curl/7.37.1
> Host: localhost:8093
> Accept: /
> Content-Length: 102
> Content-Type: application/x-www-form-urlencoded
>
* upload completely sent off: 102 out of 102 bytes
< HTTP/1.1 200 OK
< Content-Length: 615
< Content-Type: application/json; version=1.6.0
< Date: Thu, 20 Oct 2016 22:14:12 GMT
<
{
“requestID”: “b056ebcc-e71e-4c31-af19-a2995b216940”,
“signature”: {
"": ""
},
“results”: [
{
“travel-sample”: {
“callsign”: “MILE-AIR”,
“country”: “United States”,
“iata”: “Q5”,
“icao”: “MLA”,
“id”: 10,
“name”: “40-Mile Air”,
“type”: “airline”
}
}
],
“status”: “success”,
“metrics”: {
“elapsedTime”: “19.117157ms”,
“executionTime”: “19.077803ms”,
“resultCount”: 1,
“resultSize”: 300
}
}

  1. yes, “hello” is bucket password.

hth,
-Prasad

1 Like

Thank you so much! It worked.

1 Like