Support for MSSQL named pipe (np:) and shared memory (lpc:) DSNs

[fsiler]

Is your feature request related to a problem? Please describe.
In my environment, I'm concerned about my ability to use TCP for instrumentation and would like to have shared memory and named pipes available.

Describe the solution you'd like
It appears that the upstream microsoft/go-mssqldb library supports np: and lpc: DSNs. When I try data_source_name: 'lpc:SQLEXPRESS' or data_source_name: 'lpc://SQLEXPRESS' in the current release, I get: No metrics gathered, [from Gatherer #1] invalid URL. I've also tried lpc:$IPADDRESS, lpc:localhost, and lpc:(local). I'm having trouble tracing down where the "invalid URL" portion of the message comes from.

I get a different error message when attempting DSNs such as data_source_name: 'sqlserver://localhost?protocol=lpc: No metrics gathered, [from Gatherer #1] No protocol handler is available for protocol: 'lpc'

Describe alternatives you've considered
We might well just use TCP. I'm trying to mitigate the risk and understand the codebase here.

Additional context
n/a

[fsiler]

Aha, I traced the issue here, so it's actually an issue with dburl. I will open an issue or PR there.

[kenshaw]

Just FYI -- I'm looking into how this can be supported with dburl (and subsequently usql), and I will need to dig through the SQL Server driver here to figure out if there are other ways to pass this.

[kenshaw]

@fsiler Please try adding a &protocol=lpc or &protocol=np parameter with a sqlserver:// URL. As I'm not a Windows user, I cannot test this. This might work properly, as a work around.

For example, if you wanted to connect to np:namedpipe that would become: sqlserver://namedpipe/?protocol=np

[kenshaw]

Apologies, I just read your original issue here, and it seems you had tried that. That error is not from dburl, and my guess is that the sharedmemory and namedpipe packages need to be imported into sql_exporter. That, coupled with the ?protocol= parameter is what I believe will be necessary to get this to work. As I'm not on Windows, I do not have a way of testing this. I will look at modifying dburl to support this, and modify/change the URL to inject this transport protocol, but the using app/package (sql_exporter in this case) will need to make modifications to its code base regardless of whether or not dburl mangles the underlying DSN/URL.

[kenshaw]

To make it easy, I mean an anonymous import like this:

import (
    _ "github.com/microsoft/go-mssqldb/namedpipe"
    _ "github.com/microsoft/go-mssqldb/sharedmemory"
)

[kenshaw]

@fsiler I just pushed a commit for usql that imports these packages. You can try using usql with a named pipe and reporting back here:

go install github.com/xo/usql@master
usql sqlserver://my_named_pipe/?protocol=np

If you're able to connect with either lpc or np as a transport protocol specified with the ?protocol= parameter, then all that would be necessary for sql_exporter would be to add the anonymous imports.

See the relevant usql commit here.

[fsiler]

This is amazing, thanks- I am afraid I have to go for today but will test tomorrow.

[burningalchemist]

Hey @kenshaw thanks a lot for having a look at the issue! 👍
@fsiler Yeah, let's test this with usql first. I have no issues with adding these packages to sql_exporter once confirmed. 👍 I also have limited options testing against Windows and MSSQL, so your input would be valuable. 🙂

[fsiler]

I'm sorry for not knowing more Go, @kenshaw . I get errors on compile:

$ go install github.com/xo/usql@master
# github.com/xo/usql/drivers/sqlite3
go\pkg\mod\github.com\xo\u[email protected]\drivers\sqlite3\sqlite3.go:31:29: undefined: sqlite3.Error
go\pkg\mod\github.com\xo\u[email protected]\drivers\sqlite3\sqlite3.go:35:29: undefined: sqlite3.ErrN
$ uname -a
MINGW64_NT-10.0-20348 10-234-39-242 3.4.7-ea781829.x86_64 2023-07-05 12:05 UTC x86_64 Msys

[kenshaw]

@fsiler what environment is this? I use msys2 for building/developing usql under Windows. I don't believe I've seen this error before. I'm out of town at the moment and don't have the capability to spin up a Windows VM until Monday.

[fsiler]

@kenshaw Windows 2022, go installed via Chocolatey. I tried compiling in both Powershell and git bash, identical error messages.

[kenshaw]

@fsiler Sorry, I just woke up, I wasn't thinking. You should be able to build without the SQLite3 dependency in the following way:

go install -tags no_sqlite3 github.com/xo/usql@master

Without the sqlite3 dependency, it should be a 100% pure Go build.

[kenshaw]

@fsiler It's likely the error you're encountering is due to a missing C lib build dependency. I wouldn't know what that is off the top of my head.

[fsiler]

I was able to get usql to build as suggested by omitting sqlite support.
This machine is running MSSQL express with TCP, named pipes, and shared memory all enabled.
I initially tried the named pipe sql\query, which is what the interface suggested, and then thought perhaps the backslash was an issue so I renamed to just sql.
If it would be helpful, I can make an instance available via RDP for testing.

$ usql sqlserver://localhost
Connected with driver sqlserver (Microsoft SQL Server 15.0.2000.5, RTM, Express Edition (64-bit))
Type "help" for help.

ms:localhost=> select rand();
         1
--------------------
 0.2864187065348336
(1 row)

ms:localhost=>
ms:localhost=> ^D

$ usql sqlserver://sql//query/?protocol=np
error: sqlserver: no named pipe instance matching '/QUERY/' returned from host 'sql'

$ usql sqlserver://sql/query/?protocol=np
error: sqlserver: no named pipe instance matching 'QUERY' returned from host 'sql'

$ usql sqlserver://sql\\query/?protocol=np
error: parse "sqlserver://sql\\query/?protocol=np": invalid character "\\" in host name

$ usql sqlserver://sql/sql/query/?protocol=np
error: sqlserver: no named pipe instance matching 'SQL/QUERY' returned from host 'sql'

$ usql sqlserver://sql/sql\\query/?protocol=np
error: sqlserver: no named pipe instance matching 'SQL\QUERY' returned from host 'sql'

$ usql sqlserver://localhost/sql\\query/?protocol=np
error: sqlserver: no named pipe instance matching 'SQL\QUERY' returned from host 'localhost'

# at this point I renamed the named pipe from "sql\query" to "sql" and restarted MSSQL
$ usql sqlserver://localhost/sql/?protocol=np
error: sqlserver: no named pipe instance matching 'SQL' returned from host 'localhost'

$ usql sqlserver://localhost/sql/?protocol=np
error: sqlserver: no named pipe instance matching 'SQL' returned from host 'localhost'

$ usql sqlserver://localhost/ssnc_sqlexpress/sql/?protocol=np
error: sqlserver: no named pipe instance matching 'SSNC_SQLEXPRESS/SQL' returned from host 'localhost'

$ usql sqlserver://localhost/sql?protocol=lpc

$
# yes, usql just quit silently, no error, with exit code 1.

Another thing that's interesting is I can't connect with DSNs of the form sqlserver://$hostname or sqlserver://$hostname/$databasename. I'm guessing maybe the underlying library is just assuming that's a DNS name, as opposed to whatever weird resolution scheme Windows uses?

Another thing I noticed is that I often get the error message "error: sqlserver: The system cannot find the file specified." when attempting to use usql in bash. I'm not sure if I'm quoting things wrong or something?

Finally, it looks like there may be a code path problem which is defaulting to named pipes for some reason. Note (actual address elided):

# same thing happens when using a DNS name other than `localhost`
$ usql sqlserver://$IPADDRESS
error: sqlserver: no named pipe instance matching '' returned from host '$IPADDRESS'

[kenshaw]

I would really need to ask you to write a minor Go program to try to connect directly then using Go. I'll try to get this to work on my local system, but I (personally) have not run SQL Server on Windows since something like 2006. I would really ask the Microsoft SQL Server Go driver devs to help if you could.

[fsiler]

For the moment, it seems that the working assumption is that I will have TCP available. However, I'm also having issues connecting to SQL 2008 that I haven't had a chance to track down, so I may circle back and pursue this further.