Mikro ORM doesn't allow passing "server" to MSSQL

[SteadEXE]

Describe the bug

Hello,

It's not possible to pass the "server" parameter to mssql so it is not yet possible to connect to a named instance.
The documentation doesn't mention it either.

Tedious needs "server" option in format "HOST\INSTANCE" in order to allow connecting to a named instance of SQL Server.

Reproduction

MikroOrmModule.forRoot({
    driver: MsSqlDriver,
    dbName: 'my_awesome_database',
    user: 'sa',
    password: 'sa password',
    server: 'LOCALHOST\\SQLEXPRESS2022' // <-- this is mendatory to be able to login to the local named instance,
    driverOptions: {
      encrypt: false,
    },
    debug: true,
}),

What driver are you using?

None

MikroORM version

6.2.0

Node.js version

v21.7.1

Operating system

No response

Validations

• Read the Contributing Guidelines.
• Read the docs.
• Check that there isn't already an issue that reports the same bug to avoid creating a duplicate.
• Check that this is a concrete bug. For Q&A open a GitHub Discussion or join our Slack.
• The provided reproduction is a minimal reproducible example of the bug.

[B4nan]

You can use driverOptions here too. I will have a look if we can map such value from host option, I dont see why we would need anything else.

Also keep in mind that driverOptions are an override for knex options, what you do seems wrong, you are missing a connection object there probably.

[SteadEXE]

Is driverOptions passed to new Connection(/* here */) of tedious?
I can get tedious to connect to my database but cannot get Mikro ORM to it yet. You are probably right, it's a option mapping issue I think

[SteadEXE]

I could get it working with this configuration (on tedious alone):

const connection = new Connection({
    server: 'localhost',
    options: {
        database: 'my_awesome_database',
        instanceName: 'SQLEXPRESS2022'
    },
    authentication: {
        type: 'default',
        options: {
            user: 'sa',
            password: 'sa password'
        }
    }
});

[B4nan]

Is driverOptions passed to new Connection(/* here */) of tedious?

No, as I just said, it's passed to knex(), MikroORM does not directly work with tedious.

[B4nan]

As expected, you can just use host instead of server, I won't be adding such configuration if its just a simple alias.

https://github.com/knex/knex/blob/master/lib/dialects/mssql/index.js#L51

You need to think of this driver as knex dialect, not the db connection (in this case tedious).

[SteadEXE]

Sorry, I was indeed forgetting about this layer of abstraction. I figured how to get it working with:

MikroOrmModule.forRoot({
      driver: MsSqlDriver,
      dbName: 'my_awesome_database',
      user: 'sa',
      password: 'sa password',
      driverOptions: {
        connection: {
          server: 'localhost',
          options: {
            instanceName: 'SQLEXPRESS200',
          },
        },
      },
      debug: true,
    }),

Thank you for your patience and the advices and sorry again.

[B4nan]

would it make sense to do this automatically? is it safe to assume that the host parameter will never contain \\ in itself? i think it should be fine, so we could expand it automatically

also note that user: 'sa' is the default

[boenrobot]

would it make sense to do this automatically?

Some MSSQL clients do this automatically. For sure Microsoft's SQL Management Studio*. Based on the example above, I'd guess tedious does not do this automatically.

is it safe to assume that the host parameter will never contain \\ in itself?

I'm not sure about the instance name itself, but for sure, the DNS name can't contain "\", so splitting by the first occurrence, and using everything else (including any subsequent "\") as the instance name should be fine.

* Disclaimer: Most of my experience with MSSQL is in managing DBs for apps that use MSSQL through SQL Management Studio (think backups, credentials, config files...). Not in creation of apps that use MSSQL. And the only reason I say "most" is uni assignments, years ago.

[SteadEXE]

I looked how MSSQL initialize tedious under the hood.
https://github.com/tediousjs/node-mssql/blob/master/lib/base/connection-pool.js#L67

They cut the configuration key "server" (in format host\instance) to extract real server and instanceName.

If instanceName is set, they also delete "port" from options.
https://github.com/tediousjs/node-mssql/blob/master/lib/tedious/connection-pool.js#L50

I think @B4nan you can safely assume that a host with \ is a named instance in case of SQL Server, since it is not allowed in DNS, there should be not edge case.

[B4nan]

This should help dc7dc4c

I looked how MSSQL initialize tedious under the hood.
tediousjs/node-mssql@master/lib/base/connection-pool.js#L67

They cut the configuration key "server" (in format host\instance) to extract real server and instanceName.

You are looking at the wrong library, we use https://github.com/tediousjs/tedious, and there I can't find such code, so I made it explicit and also removed the port right ahead.

[SteadEXE]

I know you are using tedious, but I was using mssql, so I checked how they handle the instance name "automagically".
By the way, thank for the shortcut, it would be less tedious to initialize tedious now