FWD » Core Development » Database

SQL Server is running on a virtual machine. I want to connect to the server instance from my local environment (Ubuntu 22.04).

To test the connection I am using sqlcmd command line tool. Also this command is used in build_db.xml ant tasks. An example of usage:

sqlcmd -S 192.168.30.243,49811 -U fwd_admin -P fwd_password123 -Nm -C

This is what I found in the sqlcmd documentation about -N option:

This option is used by the client to request an encrypted connection.

For the sqlcmd (Go) utility, -N takes a string value that can be one of true, false, or disable to specify the encryption choice. (default is the same as omitting the parameter):

On Linux and macOS, [s|m|o] were added in sqlcmd 18.0. -N can be o (for optional), m (for mandatory, the default), or s (for strict).

  If -N and -C aren't provided, sqlcmd negotiates authentication with the server without validating the server certificate.
  If -N is provided but -C isn't, sqlcmd requires validation of the server certificate. A false value for encryption could still lead to the encryption of the login packet.
  If both -N and -C are provided, sqlcmd uses their values for encryption negotiation.

And -C option:

This option is used by the client to configure it to implicitly trust the server certificate without validation. This option is equivalent to the ADO.NET option TRUSTSERVERCERTIFICATE = true.

For the sqlcmd (Go) utility, the following conditions also apply:

    If -N and -C aren't provided, sqlcmd negotiates authentication with the server without validating the server certificate.
    If -N is provided but -C isn't, sqlcmd requires validation of the server certificate. A false value for encryption could still lead to the encryption of the login packet.
    If both -N and -C are provided, sqlcmd uses their values for encryption negotiation.

I could connect to the SQL Server in a secure mode with -N option s(strict) or m(mandatory), only with additional -C option (skipping the certificate validation)

To connect in a secure mode in runtime I had to modifiy a bit the connection link in directory.xml

jdbc:sqlserver://192.168.30.243:49811;databaseName=hotel;TrustServerCertificate=TRUE;Encrypt=TRUE

As you can see in the docs TrustServerCertificate=TRUE argument is equivalent to -C option.

In a development environment should we use a certificate or skip the the certificate validation?

Eduard Soltan wrote:

In a development environment should we use a certificate or skip the the certificate validation?

Lets make this decision only after we find what's needed to make the secure connection work.

In any case, we need a to run the import over secure connection, too.

Constantin Asofiei wrote:

Lets make this decision only after we find what's needed to make the secure connection work.
In any case, we need a to run the import over secure connection, too.

To run the secure connection I had also to modify the sql.url.sqlserver to dbc:sqlserver://${db.host}:${db.port};databaseName=${db.sql.name};TrustServerCertificate=TRUE;Encrypt=TRUE.

While running the import, I catch the network packages using the wireshark with 2 versions of sql.url: Encrypt=FALSE and ;TrustServerCertificate=TRUE;Encrypt=TRUE.

With Encrypt=FALSE option I was able to decode the TCP payload, and read the data in plain text.

With ;TrustServerCertificate=TRUE;Encrypt=TRUE options on the another hand, the payload is encrypted and I could not read data.

Is there any configuration specific on the setup of SQLServer ?

Constantin Asofiei wrote:

Is there any configuration specific on the setup of SQLServer ?

I just setup the SQL Sever according to the https://proj.goldencode.com/projects/efi/wiki/Windows_Environment_Setup_-_Windows_+_SQL documentation.