SiardGui : SQL server connection seems to only use certificates

[Tarjei-stavanger]

Only using certificates is annoying since this is something that has to be added to the SQL server installation by hand after it has been installed. It is likely to involve a change process as well as several people.

For me it is highly desireable to be able to connect the same ways as DBPTK-desktop does.

[HartwigThomas]

There is nothing in the SIARD code that requires certificates. However, the SQL Server instances may have been installed with various security features. In SIARD one can freely configure the JDBC connection string in case the standard connection is not appropriate. For possible connection strings, please refer to the SQL Server documentation: https://docs.microsoft.com/en-us/sql/connect/jdbc/connecting-to-sql-server-with-the-jdbc-driver?view=sql-server-ver15

[Tarjei-stavanger]

Since I know that the SQL server instance does not have any certificate installed, I'm confident that the toolkit you are using defaults to certificate authentication. But most people would need user name/password authentication or even windows authentication. This probably requires a somewhat different invocation of the toolkit.

[mburri]

Hei @Tarjei-stavanger

I agree with Hartwig that it should be possible to authenticate to SQL Server without any certificate - I'm never the less eager to learn more about your problem.

Could you tell me more about your setup?

What versions of MS SQL Server, SIARD GUI, OS are you using?

Is there something special about your setup that we should know?

Is the SQL Server instance running locally or do you have to connect over the network? Is it possible for you to connect to a local SQL Server?

[Tarjei-stavanger]

Have you actually tried it ?

If you have tried it with an SQL server without certificates and it worked, then it is time to look into the specifics.

I used dummy data today since it will take time to find a database to export.

Notice that the JDBC URL does not include user name and password.

[mburri]

Indeed - I just succesfully tried to export a database it with a local instance of ms sql server running in a docker container.

My connection settings are:

It's actually correct that the jdbc url does not contain the username and password. What error message do you get with your settings?

By the way: we have automated tests that are running on each merge request or push to the main branch. The pipeline starts a mssql server and runs a suite of automated tests... you can look at this job that ran the last time we changed something on the main branch. https://github.com/sfa-siard/JdbcMsSql/actions/runs/2629145718 (click on the "build" job to see what it actually does)

[mburri]

But if I think about it - we are using a standard setup - I'm not sure if there are any certificates involved. We will check that. Maybe you could nonetheless provide more information about your setup. That would be helpful for us.

[Tarjei-stavanger]

Thanks! Knowing that it should work will be helpful for me.

[mburri]

I'm sorry - but I cannot reproduce your issue.

could you please describe:

• your setup of your ms sql server instance (version, specific configurations regarding certificates) or point me to the relevant documentation on how your sql server is configured or how I can setup a "SQL Server without certificate"?.
• if available: error messages you got from siard gui - if any? there should be a siard21.log file in your temp folder where the log is written to.
• if possible: could you provide a working jdbc url connection string that you know works with your database setup?

[mburri]

We are closing this issue because we have not been able to reproduce the problem. Connections to MS SQL Server with username and password work fine.