MS-SQL Error when connecting to named instance

[MrTrebron]

Hi there,

I am receiving an error when connecting to an names MS-SQL Server Instance

thread 'main' panicked at 'Invalid database URL: Configuration(InvalidDomainCharacter)', D:\a\SQLpage\SQLpage\src\webserver\database\mod.rs:207:34

{ "database_url": "mssql://user:pass@localhost\\sqlexpress/Database" }

Connection string for named instance
Server=myServerName\myInstanceName;Database=myDataBase;User Id=myUsername;Password=myPassword;

Regards
Norbert

[lovasoa]

Shouldn't you use a forward slash instead of a backslash after localhost ?

[MrTrebron]

For the instance name a backslash is used, see the example connection string.

If I use a forward slash sqlpage uses everything before the forward slash as hostname and the part after the slash as database name.

{ "database_url": "mssql://user:pass@localhost/sqlexpress/Database" }
Tries to connect to server localhost, default instance and tries database sqlexpress instead of Database

[lovasoa]

v0.10.3 now supports mssql://user:pass@localhost/Database?instance=sqlexpress

[MrTrebron]

Thank you for the fast reply, but I get another error now

[2023-09-14T05:03:09Z INFO sqlpage::webserver::database] Connecting to database: mssql://sql:sql@localhost/SQLPage?instance=sqlexpress thread 'main' panicked at 'not yet implemented: Instance', C:\Users\runneradmin\.cargo\registry\src\index.crates.io-6f17d22bba15001f\sqlx-core-oldapi-0.6.12\src\mssql\protocol\pre_login.rs:64:32

Seems something is missing

[lovasoa]

Okay, thanks for testing. It might take a little longer, then...

[lovasoa]

Okay, let's not be over-confident this time 😬

Can you try v0.10.4-beta1 https://github.com/lovasoa/SQLpage/releases/tag/v0.10.4-beta1 ?

Sorry for the back and forth, but I don't have a windows instance myself.

[MrTrebron]

You don't have to apologize to me.
This project is awesome. And I try to help as I can. And what I can do is MS-SQL tests. I never ever wrote a line of rust code.

No more error messages, but unfortunately the instance is ignored.
if I use
'mssql://sql:sql@localhost/SQLPageDB?instance=sqlexpress"'
SQLPage tries to logon against the default SQL Server instance.
I can determine by different passwords and test tables I created at default instance and named instance.

Edit: It seems to work. I will take some more time to test and will share my results.

[lovasoa]

That's strange... SQLPage is supposed to send the instance name in its login message now, so I don't know what more to add ...

[lovasoa]

Ok, I'm learning much more about microsoft tech than I thought I ever would.

After some reading, I'm realizing that non-default sql server instances run on dynamically allocated ports.

SQLPage doesn't (yet) know how to lookup your instance's port automatically, and always connects to the default 1433 port. So you need to specify it in the connection string. For instance, if your instance is bound to the TCP Dynamic Port 2179, you would specify mssql://sql:sql@localhost:2179/SQLPageDB?instance=sqlexpress

https://learn.microsoft.com/en-us/troubleshoot/sql/database-engine/connect/static-or-dynamic-port-config
https://www.linkedin.com/pulse/want-connect-sqlexpress-without-using-instance-name-follow-sullivan/
https://www.mssqltips.com/sqlservertip/2495/identify-sql-server-tcp-ip-port-being-used/

[MrTrebron]

I think of lot of connection error comes from the Windows and MS-SQL Server
You can have an instance without instance name, connect as
Server=myServerName;Database=myDataBase;User Id=myUsername;Password=myPassword;
You can have named instances, sqlexpress is by default a named instance but you can install every MS-SQL Server Version as a named instance, connect as
Server=myServerName\myInstanceName;Database=myDataBase;User Id=myUsername;Password=myPassword;

The default Windows port is 1433 for incoming TCP connections. But if you have a "normal" instance and a named instance they cannot share the default port.
On one of my machines the default instanced used the port so every incoming TCP on this was port was mapped to default instance. SQLExpress uses a dynamic port and was unreachable for SQLPage.
When I stopped the default and mapped the port to SQLExcpress with the current Beta I can connect to SQLExpress,

On my work machine I could not activate TCP for 1433 and so it was unreachable since port 1433, the default, was not listening.

As far as I can say by now, the error is fixed with your last beta. I can connect to a named instance.

I will test the connection with different ports, but this is not topic of this issue

[MrTrebron]

Ok, I'm learning much more about microsoft tech than I thought I ever would.

After some reading, I'm realizing that non-default sql server instances run on dynamically allocated ports.

SQLPage doesn't (yet) know how to lookup your instance's port automatically, so you need to specify it in the connection string. For instance, if your instance is bound to the TCP Dynamic Port 2179, you would specify mssql://sql:sql@localhost:2179/SQLPageDB?instance=sqlexpress

Thanks for your investigation. Seems we where witing at the same time, but you were faster.

I will try that.

[MrTrebron]

Sorry for the late reply.

It works amazing. Instance and port are recognized.

Thank you

[lovasoa]

Great ! I'd like to keep that open to give it more visibility until SQLPage is able to find the dynamic port on its own.