Failed to assign SQL SERVER password when creating datasource using API

[xwang]

When I tried to create a datasource using the HTTP API for a sql server, the datasource was created fine, but there is no password there even though it is provided during the API call.

I have been googling the topic for a few days, there is seldom any mention of people doing this. Does any one have a working sample for creating SQL server datasource?

data = {
“name”:“testDB”,
“type”:“mssql”,
“url”:"{0}:{1}".format(db_host,db_port),
“database”:"{0}".format(db_name),
“user”:"{0}".format(db_user),
“access”:“proxy”,
“password”: “{0}”.format(db_pass),
“basicAuth”: False
}

[bkgann]

the password needs to be put into the “secureJsonData” block

data = {
“name”:“testDB”,
“type”:“mssql”,
“url”:"{0}:{1}".format(db_host,db_port),
“database”:"{0}".format(db_name),
“user”:"{0}".format(db_user),
“access”:“proxy”,
“basicAuth”: False,
“secureJsonData:” {
“password”: “{0}”.format(db_pass),
}
}

[xwang]

Thanks for the reply. I forgot to mention I tried placing the password in the secureJsonData part as well it did not populate the password field.

[bkgann]

I posted this to the api:

{
  "name":"test_datasource",
  "type":"mssql",
  "url":"server:1521",
  "access":"proxy",
  "database": "contoso",
  "basicAuth":false,
  "user": "bogus",
  "secureJsonData": {
      "password": "abc123"
  }
}

and it showed up correctly:

[xwang]

I tried the your data but it came up with blank password. Maybe there is a setting somewhere? My grafana is V7.4.0 and I am using basic auth to send in the request.

[bkgann]

i tried with 7.4.0, though i’m using an api token and not basic auth to post to the api:

curl --location --request POST 'http://grafana.staged-by-discourse.com/api/datasources' \
--header 'Content-Type: application/json' \
--header 'Authorization: Bearer eyJrIjoidlFhS3Q4eUJiTVRlTDNYbjhlRkFyR2NjTXRrZVdkSk8iLCJuIjoiYXV0bzIiLCJpZCI6MX0=' \
--data-raw '{
    "name": "test_datasource",
    "type": "mssql",
    "url": "server:1521",
    "access": "proxy",
    "database": "contoso",
    "basicAuth": false,
    "user": "bogus",
    "secureJsonData": {
        "password": "abc123"
    }
}'

The docs only show using an api key: Data source HTTP API | Grafana Labs

[xwang]

Thanks for the help. My task is to auto-config the Grafana server with a script so that nobody need to login to administer it, so I cannot use a API token. All other part works fine with basic auth, including users, dashboards. This is only thing holding up now.

[bkgann]

you have the option of datasource provisioning during startup of the grafana server instead of using the api, if that helps.

[xwang]

Unfortunately I will have no access to the Grafana server except through this API. Thanks a lot.

[bkgann]

i just tried basic auth and that worked also (using postman)

curl --location --request POST 'http://grafana.staged-by-discourse.com/api/datasources' \
--header 'Content-Type: application/json' \
--header 'Authorization: Basic YWRtaW46YWRtaW4=' \
--data-raw '{
    "name": "test_datasource",
    "type": "mssql",
    "url": "server:1521",
    "access": "proxy",
    "database": "contoso",
    "basicAuth": false,
    "user": "bogus",
    "secureJsonData": {
        "password": "abc123"
    }
}'

[xwang]

You basic auth looks different, mine is through a url like “http://user:pass@localhost:3000/api/datasources”

[bkgann]

i haven’t had any luck with that syntax, have had to put it into the header

[xwang]

OK, I will try it this afternoon and let you know.

[xwang]

I tried using the auth header like you did, but still getting the blank password. Might be some configuration in my Grafana is different.

[xwang]

I checked my defaul.ini everything seems normal. My Grafana server is running SSL, could it be a bug somewhere? What else might cause this behavior?

[bkgann]

can you try it with something like postman?

[xwang]

Good suggestion! I tried SOAPUI it actually worked, the password is set. Now I will need to figured out where the difference is in these messages.

[xwang]

Finally got it all figured out. The problem was due to an error in my python script. The json data need to passed in using “json” parameter instead of “data” I was using.

Correct:
response = requests.post(url, json=data, verify=False, auth=(username, password))

Wrong:
response = requests.post(url, data=data, verify=False, auth=(username, password))

Thanks a lot, bkgann.

[akhilapenjuru1]

I’m facing the same issue when I try to pre configure Postgres Sql using YAML file ,password is not displaying. Can someone help me out on this.
my YAML file looks like
datasources:

• name: “PostgreSQL-Energy-Usage”
  type: “postgres”
  access: “proxy”
  orgId: 1
  url: “localhost:5432”
  password: “”
  user: “postgres”
  database: “DbTest”
  basicAuth: false
  isDefault: true
  jsonData:
  sslmode: “disable”
  postgresVersion: 903
  tlsAuth: false
  tlsAuthWithCACert: false
  tlsConfigurationMethod: “file-path”
  tlsSkipVerify: true
  secureJsonData:
  password: “abc123”

version: 1

editable: true