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\usql@v0.15.2-0.20231019204845-42569f8939a9\drivers\sqlite3\sqlite3.go:31:29: undefined: sqlite3.Error
go\pkg\mod\github.com\xo\usql@v0.15.2-0.20231019204845-42569f8939a9\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.