Cannot connect to Azure Sql Database

[Celludriel]

I tried running a test vs an Azure Sql Database but I can't seem to connect to it while I'm sure the username and password are ok. I am perfectly able to connect with this connection string in my java application or SquirellSQL query tool.

Here is my test, I blanked out the domain and password in the connection string I can say however the password contains these characters: +$(%

I also url encoded the password in one of my attempts to get it fixed

import http from 'k6/http';
import sql from 'k6/x/sql';
import { htmlReport } from "https://raw.githubusercontent.com/benc-uk/k6-reporter/main/dist/bundle.js";
import { sleep } from 'k6';

export const options = {
  vus: 10,
  duration: '30s'
};

const db = sql.open('sqlserver', 'jdbc:sqlserver://xxxxxxxx-a-sqls-001-trip.database.windows.net:1433;database=XXXXXXX-A-SQLD-001-TRIP-DIENSTREGELINGEN;user=trip-admin@xxxxxxxxx-a-sqls-001-trip;password=xxxxxxxxxxx;encrypt=false;trustServerCertificate=true;hostNameInCertificate=*.database.windows.net;loginTimeout=30;');

export default function () {
  const results = sql.query(db, 'SELECT count(*) FROM Trip;');
  sleep(0.3);
}

export function teardown() {
  db.close();
}

export function handleSummary(data) {
  return {
    "summary.html": htmlReport(data),
  };
}

output:

ERRO[0006] GoError: mssql: login error: Login failed for user 'trip-admin@xxxxxxxx-a-sqls-001-trip'.
        at reflect.methodValueCall (native)
        at file:///C:/Tools/k6/jdbctest.js:14:28(6)  executor=constant-vus scenario=default source=stacktrace
ERRO[0006] GoError: mssql: login error: Login failed for user 'trip-admin@xxxxxxxx-a-sqls-001-trip'.
        at reflect.methodValueCall (native)
        at file:///C:/Tools/k6/jdbctest.js:14:28(6)  executor=constant-vus scenario=default source=stacktrace
ERRO[0006] GoError: mssql: login error: Login failed for user 'trip-admin@xxxxxxxx-a-sqls-001-trip'.
        at reflect.methodValueCall (native)
        at file:///C:/Tools/k6/jdbctest.js:14:28(6)  executor=constant-vus scenario=default source=stacktrace`
`

[imiric]

Hi,

please take a look at the mssql_test.js example, and the go-mssqldb documentation.

I don't have an Azure account to test with, but your connection string looks like it has these issues:

• The jdbc prefix is only relevant for Java applications, so I would remove it.

• You seem to be mixing the URL and ADO syntax. If you're specifying a URL, the query string should be separated by ?, and parameters by &, and you should URL encode it. If you're using the ADO syntax, then separating parameters with semi-colon is fine, but you should specify the server parameter.

It still seems that go-mssqldb is able to parse it, but maybe it's interpreting it as an ADO string, and missing the server parameter.

So I would try with:

const db = sql.open('sqlserver', 'server=xxxxxxxx-a-sqls-001-trip.database.windows.net:1433;database=XXXXXXX-A-SQLD-001-TRIP-DIENSTREGELINGEN;user=trip-admin@xxxxxxxxx-a-sqls-001-trip;password=xxxxxxxxxxx;encrypt=false;TrustServerCertificate=true;hostNameInCertificate=*.database.windows.net;');

[Celludriel]

I got another error now

ERRO[0005] GoError: lookup xxxxxxx-a-sqls-001-trip.database.windows.net:1433: no such host
at reflect.methodValueCall (native)
at file:///C:/Tools/k6/jdbctest.js:14:28(6) executor=constant-vus scenario=default source=stacktrace

at least it is not a login failed without explanation why

[Celludriel]

Found a way to connect

const db = sql.open('sqlserver', 'Server=tcp:xxxxxx-a-sqls-001-trip.database.windows.net,1433;Initial Catalog=ARDELIJN-A-SQLD-001-TRIP-DIENSTREGELINGEN;Persist Security Info=False;User ID=trip-admin;Password=xxxxxxxx;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;');

Seems I had to use ADO as you said but add tcp: in front of the host, maybe append the documentation that jdbc wouldn't work ? and you need to use the ADO format ?

[imiric]

Great that you got it working. That's good to know.

JDBC is specific to Java, so we shouldn't need to mention it. And go-mssqldb supports 3 ways of specifying the connection string, so ADO is not strictly required. I would assume that a URL would also work, but you'd have to URL encode it properly.