feature: support connecting over named pipes

[FilipDeVos]

This driver is unable to connect to SQL Server LocalDB instance. When you attempt to connect to localdb the following error is returned:

Cannot connect:  Unable to get instances from Sql Server Browser on host (localdb): dial udp: GetAddrInfoW: No such host is known.

This is probably because a connection is attempted to the name (localdb), and LocalDB only supports named pipes. In this article https://msdn.microsoft.com/en-us/library/hh510202.aspx it is mentioned how to get the named pipe.

[denisenkom]

Named pipes and shared memory connections are not supported. Only tcp connections are supported, you can enable tcp endpoint for your server to get it to work.

[FilipDeVos]

Thanks for the feedback. LocalDB only supports named pipes. Is supporting named pipes something that you would be interested in? (As far as I can see only jTDS has an implementation it uses jcifs for the grunt work in SmbNamedPipe which uses SmbFileInputStream and SmbFileOutputStream. Note that jtds and jcifs are LGPL licensed, so that's a bit problematic with the BSD 3-clause license used by this project.)

[denisenkom]

There is a https://github.com/natefinch/npipe project which seems promising. If you want you can implement it and send a pull request.

[denisenkom]

If you decide to implement it please make it a soft dependency on npipe, it should only be required if named pipes connection is requested by user.

[FilipDeVos]

I will give it a try. Still new at go so it might take some time to get somewhere.

[clns]

@FilipDeVos I'm also interested in this, have you been able to implement https://github.com/natefinch/npipe? Or made any progress whatsoever?

[FilipDeVos]

Not really no. Sorry

[simnalamburt]

I'm working on this issue at simnalamburt/go-mssqldb.

The actual ADO connection string format supports explicitly specifying the DB connection protocol. I'll extend the ADO version of current go-mssqldb's connection string format just like the actual ADO connection string format to specify the protocol. This will resolve this issue without introducing any breaking changes.

Current design

Current go-mssqldb will always try to connect to the DB using TCP.

server=localhost;user id=USER;password=PASSWORD;database=master

Proposal

Now, you'll be able to explicitly choose a protocol.

# TCP (Omitting the protocol will defaults to TCP to preserve backward compatibility)
server=localhost;user id=USER;password=PASSWORD;database=master

# TCP
server=tcp:localhost;user id=USER;password=PASSWORD;database=master

# Named pipe
server=np:\\.\pipe\sql\query;user id=USER;password=PASSWORD;database=master

# Shared Memory (It'll return "not implemented yet" error)
server=lpc:.\SQLEXPRESS;user id=USER;password=PASSWORD;database=master

If you build go-mssqldb in linux, using named pipe or shared memory protocol will causes "not implemented yet" error.

TODO

• [x] Implement MVP
• [x] Use named-pipe only in windows

References

• https://github.com/natefinch/npipe
• Creating a Valid Connection String Using Named Pipes - MSDN
• SqlConnection.ConnectionString Property - MSDN

[kardianos]

I'm assuming you are also going to plug into the https://github.com/denisenkom/go-mssqldb/blob/8e6115d5172422909579b49fd1fe0f7591416e4b/tds.go#L24 SQL Server Browser to automatically choose named pipes?

[simnalamburt]

@kardianos Actually, I was editting parseConnectParams function. https://github.com/simnalamburt/go-mssqldb/commit/4ef9cb6d6

I'll let users to explicitly choose the protocol, rather than automatic detection. Looks like the old ADO interface does automatically choose protocol but it was misleading for many users.

[yusufozturk]

Thanks for the progress on this. So shared memory is not implemented yet, right? I read there was some good progress for that. (2 years ago) But it's kind of on hold now?

[simnalamburt]

@yusufozturk Yes I implemented named pipe feature in my fork (https://github.com/denisenkom/go-mssqldb/pull/250) but not the shared memory feature.

Actually implementing more conenction protocol is not that hard but I'm not using go-mssqldb anymore so I don't have enough motivation to implement it on my own.

[fortunewalla]

Not sure if this belongs here but I was told to ask you about a similar problem. I have problems connecting to LocalDB using DataStation CE software.

Below is a copy of the original issue: https://github.com/multiprocessio/datastation/issues/305

---

SQL Server LocalDB Instance : (localdb)\MSSQLLocalDB

So I entered the host as (localdb)\MSSQLLocalDB with pubs as the database. (This hostname works in Excel when connecting using SQL Server connection)

select 1+2;

It gives this error.

Error evaluating panel:
[INFO] 2023-08-20T22:49:28 DataStation Runner (Go) 0.11.0
[INFO] 2023-08-20T22:49:28 Evaling database panel: Untitled panel #1
[INFO] 2023-08-20T22:49:28 Failed to eval: parse "sqlserver://user:pass@(localdb)\\MSSQLLocalDB:1433?database=pubs": invalid character "\\" in host name

BTW it works with another similar software called DatabaseBrowser

---

Also tried

Server=127.0.0.1\MSSQLLocalDB

and it gives a similar error.

Error evaluating panel:
[INFO] 2023-08-26T17:44:25 DataStation Runner (Go) 0.11.0
[INFO] 2023-08-26T17:44:25 Evaling database panel: Untitled panel #1
[INFO] 2023-08-26T17:44:25 Failed to eval: parse "sqlserver://@Server=127.0.0.1\\MSSQLLocalDB:1433?database=pubs": invalid character "\\" in host name

Any ideas for resolving this? It seems to a case of not being able to parse the backslash character properly \.

Hope this helps in anyway. Thanks.

[FilipDeVos]

You're trying to connect with TCP/IP by specifying the port number . Localdb only supports connecting with named pipes.

You can force a named pipes connection by prefixing your server name with np:

Read this for more info: https://weblogs.asp.net/jongalloway/sql-force-the-protocol-tcp-named-pipes-etc-in-your-connection-string

On Sat, Aug 26, 2023 at 8:18 AM fortunewalla @.***> wrote:

Not sure if this belongs here but I was told to ask you about a similar problem. I have problems connecting to LocalDB using DataStation CE software.

Below is a copy of the original issue: multiprocessio/datastation#305 https://github.com/multiprocessio/datastation/issues/305

SQL Server LocalDB Instance : (localdb)\MSSQLLocalDB

So I entered the host as (localdb)\MSSQLLocalDB with pubs as the database. (This hostname works in Excel when connecting using SQL Server connection)

select 1+2;

It gives this error.

Error evaluating panel: [INFO] 2023-08-20T22:49:28 DataStation Runner (Go) 0.11.0 [INFO] 2023-08-20T22:49:28 Evaling database panel: Untitled panel #1 [INFO] 2023-08-20T22:49:28 Failed to eval: parse "sqlserver://user:pass@(localdb)\MSSQLLocalDB:1433?database=pubs": invalid character "\" in host name

BTW it works with another similar software called DatabaseBrowser

[image: image] https://user-images.githubusercontent.com/7158596/261867749-8bef8bec-df88-44e4-bbd8-60de879b3826.png

Also tried

Server=127.0.0.1\MSSQLLocalDB

and it gives a similar error.

Error evaluating panel: [INFO] 2023-08-26T17:44:25 DataStation Runner (Go) 0.11.0 [INFO] 2023-08-26T17:44:25 Evaling database panel: Untitled panel #1 [INFO] 2023-08-26T17:44:25 Failed to eval: parse @.***=127.0.0.1\MSSQLLocalDB:1433?database=pubs": invalid character "\" in host name

Any ideas for resolving this? It seems to a case of not being able to parse the backslash character properly .

Hope this helps in anyway. Thanks.

— Reply to this email directly, view it on GitHub https://github.com/denisenkom/go-mssqldb/issues/96#issuecomment-1694328909, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAAFOSBRB2UHRRYNFQTZA5LXXHSS7ANCNFSM4A6Z7NEQ . You are receiving this because you were mentioned.Message ID: @.***>

[fortunewalla]

You're trying to connect with TCP/IP by specifying the port number . Localdb only supports connecting with named pipes. You can force a named pipes connection by prefixing your server name with np: So as per the suggestion given on this page

I tried with the connection string Server=np:(localdb)\MSSQLLocalDB

It now gives a different error where it parses everything after : as a port number.

Error evaluating panel:
[INFO] 2023-08-27T16:40:05 DataStation Runner (Go) 0.11.0
[INFO] 2023-08-27T16:40:05 Evaling database panel: Untitled panel #1
[INFO] 2023-08-27T16:40:05 Failed to eval: parse "sqlserver://@Server=np:(localdb)\\MSSQLLocalDB?database=pubs": invalid port ":(localdb)\\MSSQLLocalDB" after host

I also tried np:127.0.0.1

Error evaluating panel:
[INFO] 2023-08-27T16:45:56 DataStation Runner (Go) 0.11.0
[INFO] 2023-08-27T16:45:56 Evaling database panel: Untitled panel #1
[INFO] 2023-08-27T16:45:56 Failed to eval: parse "sqlserver://@Server=np:127.0.0.1?database=pubs": invalid port ":127.0.0.1" after host

[fortunewalla]

I also tried specifying the port number explicitly.

np:(localdb)\MSSQLLocalDB:1433

It gives this error

Error evaluating panel:
[INFO] 2023-08-27T16:50:50 DataStation Runner (Go) 0.11.0
[INFO] 2023-08-27T16:50:50 Evaling database panel: Untitled panel #1
[INFO] 2023-08-27T16:50:50 Failed to eval: DSError {
  "name": "Error",
  "message": "Could not split host-port: address np:(localdb)\\MSSQLLocalDB:1433: too many colons in address",
  "stack": "goroutine 1 [running]:\nruntime/debug.Stack()\n\truntime/debug/stack.go:24 +0x65\ngithub.com/multiprocessio/datastation/runner.makeErrException({0x285f2e0?, 0xc000ead9d0?})\n\tgithub.com/multiprocessio/datastation/runner/errors.go:81 +0x5c\ngithub.com/multiprocessio/datastation/runner.edsef({0x244eade?, 0x1e?}, {0xc000bfef88?, 0x1?, 0x0?})\n\tgithub.com/multiprocessio/datastation/runner/errors.go:89 +0x32\ngithub.com/multiprocessio/datastation/runner.getDatabaseHostPortExtra({0xc000973de0?, 0xc000e228a0?}, {0x241d546, 0x4})\n\tgithub.com/multiprocessio/datastation/runner/database.go:37 +0x112\ngithub.com/multiprocessio/datastation/runner.EvalContext.EvalDatabasePanel({{{0xc000cc2407, 0xc}, 0xc000e62380, 0xc000e22c90, {0xc000cc24d8, 0x34}, 0x1388, {0xc000cc2549, 0x4}, {0x37d1808, ...}}, ...}, ...)\n\tgithub.com/multiprocessio/datastation/runner/database.go:470 +0x1405\ngithub.com/multiprocessio/datastation/runner.EvalContext.Eval({{{0xc000cc2407, 0xc}, 0xc000e62380, 0xc000e22c90, {0xc000cc24d8, 0x34}, 0x1388, {0xc000cc2549, 0x4}, {0x37d1808, ...}}, ...}, ...)\n\tgithub.com/multiprocessio/datastation/runner/eval.go:209 +0x878\nmain.eval({{{0xc000cc2407, 0xc}, 0xc000e62380, 0xc000e22c90, {0xc000cc24d8, 0x34}, 0x1388, {0xc000cc2549, 0x4}, {0x37d1808, ...}}, ...}, ...)\n\t./main.go:88 +0x7b\nmain.main()\n\t./main.go:126 +0x25e\n",
  "targetPanelId": "",
  "extra": null
}
goroutine 1 [running]:
runtime/debug.Stack()
	runtime/debug/stack.go:24 +0x65
github.com/multiprocessio/datastation/runner.makeErrException({0x285f2e0?, 0xc000ead9d0?})
	github.com/multiprocessio/datastation/runner/errors.go:81 +0x5c
github.com/multiprocessio/datastation/runner.edsef({0x244eade?, 0x1e?}, {0xc000bfef88?, 0x1?, 0x0?})
	github.com/multiprocessio/datastation/runner/errors.go:89 +0x32
github.com/multiprocessio/datastation/runner.getDatabaseHostPortExtra({0xc000973de0?, 0xc000e228a0?}, {0x241d546, 0x4})
	github.com/multiprocessio/datastation/runner/database.go:37 +0x112
github.com/multiprocessio/datastation/runner.EvalContext.EvalDatabasePanel({{{0xc000cc2407, 0xc}, 0xc000e62380, 0xc000e22c90, {0xc000cc24d8, 0x34}, 0x1388, {0xc000cc2549, 0x4}, {0x37d1808, ...}}, ...}, ...)
	github.com/multiprocessio/datastation/runner/database.go:470 +0x1405
github.com/multiprocessio/datastation/runner.EvalContext.Eval({{{0xc000cc2407, 0xc}, 0xc000e62380, 0xc000e22c90, {0xc000cc24d8, 0x34}, 0x1388, {0xc000cc2549, 0x4}, {0x37d1808, ...}}, ...}, ...)
	github.com/multiprocessio/datastation/runner/eval.go:209 +0x878
main.eval({{{0xc000cc2407, 0xc}, 0xc000e62380, 0xc000e22c90, {0xc000cc24d8, 0x34}, 0x1388, {0xc000cc2549, 0x4}, {0x37d1808, ...}}, ...}, ...)
	./main.go:88 +0x7b
main.main()
	./main.go:126 +0x25e

[FilipDeVos]

The db driver you are using doesn't support named pipes. So it's never going to work.

On Sun, Aug 27, 2023 at 7:22 AM fortunewalla @.***> wrote:

I also tried specifying the port number explicitly.

np:(localdb)\MSSQLLocalDB:1433

It gives this error

Error evaluating panel:[INFO] 2023-08-27T16:50:50 DataStation Runner (Go) 0.11.0[INFO] 2023-08-27T16:50:50 Evaling database panel: Untitled panel #1[INFO] 2023-08-27T16:50:50 Failed to eval: DSError { "name": "Error", "message": "Could not split host-port: address np:(localdb)\MSSQLLocalDB:1433: too many colons in address", "stack": "goroutine 1 [running]:\nruntime/debug.Stack()\n\truntime/debug/stack.go:24 +0x65\ngithub.com/multiprocessio/datastation/runner.makeErrException({0x285f2e0 http://ngithub.com/multiprocessio/datastation/runner.makeErrException(%7B0x285f2e0?, 0xc000ead9d0?})\n\tgithub.com/multiprocessio/datastation/runner/errors.go:81 +0x5c\ngithub.com/multiprocessio/datastation/runner.edsef({0x244eade http://ngithub.com/multiprocessio/datastation/runner.edsef(%7B0x244eade?, 0x1e?}, {0xc000bfef88?, 0x1?, 0x0?})\n\tgithub.com/multiprocessio/datastation/runner/errors.go:89 +0x32\ngithub.com/multiprocessio/datastation/runner.getDatabaseHostPortExtra({0xc000973de0 http://ngithub.com/multiprocessio/datastation/runner.getDatabaseHostPortExtra(%7B0xc000973de0?, 0xc000e228a0?}, {0x241d546, 0x4})\n\tgithub.com/multiprocessio/datastation/runner/database.go:37 +0x112\ngithub.com/multiprocessio/datastation/runner.EvalContext.EvalDatabasePanel({{{0xc000cc2407 http://ngithub.com/multiprocessio/datastation/runner.EvalContext.EvalDatabasePanel(%7B%7B%7B0xc000cc2407, 0xc}, 0xc000e62380, 0xc000e22c90, {0xc000cc24d8, 0x34}, 0x1388, {0xc000cc2549, 0x4}, {0x37d1808, ...}}, ...}, ...)\n\tgithub.com/multiprocessio/datastation/runner/database.go:470 +0x1405\ngithub.com/multiprocessio/datastation/runner.EvalContext.Eval({{{0xc000cc2407 http://ngithub.com/multiprocessio/datastation/runner.EvalContext.Eval(%7B%7B%7B0xc000cc2407, 0xc}, 0xc000e62380, 0xc000e22c90, {0xc000cc24d8, 0x34}, 0x1388, {0xc000cc2549, 0x4}, {0x37d1808, ...}}, ...}, ...)\n\tgithub.com/multiprocessio/datastation/runner/eval.go:209 +0x878\nmain.eval({{{0xc000cc2407, 0xc}, 0xc000e62380, 0xc000e22c90, {0xc000cc24d8, 0x34}, 0x1388, {0xc000cc2549, 0x4}, {0x37d1808, ...}}, ...}, ...)\n\t./main.go:88 +0x7b\nmain.main()\n\t./main.go:126 +0x25e\n", "targetPanelId": "", "extra": null}goroutine 1 [running]:runtime/debug.Stack() runtime/debug/stack.go:24 +0x65github.com/multiprocessio/datastation/runner.makeErrException({0x285f2e0 http://github.com/multiprocessio/datastation/runner.makeErrException(%7B0x285f2e0?, 0xc000ead9d0?}) github.com/multiprocessio/datastation/runner/errors.go:81 +0x5cgithub.com/multiprocessio/datastation/runner.edsef({0x244eade http://github.com/multiprocessio/datastation/runner.edsef(%7B0x244eade?, 0x1e?}, {0xc000bfef88?, 0x1?, 0x0?}) github.com/multiprocessio/datastation/runner/errors.go:89 +0x32github.com/multiprocessio/datastation/runner.getDatabaseHostPortExtra({0xc000973de0 http://github.com/multiprocessio/datastation/runner.getDatabaseHostPortExtra(%7B0xc000973de0?, 0xc000e228a0?}, {0x241d546, 0x4}) github.com/multiprocessio/datastation/runner/database.go:37 +0x112github.com/multiprocessio/datastation/runner.EvalContext.EvalDatabasePanel({{{0xc000cc2407 http://github.com/multiprocessio/datastation/runner.EvalContext.EvalDatabasePanel(%7B%7B%7B0xc000cc2407, 0xc}, 0xc000e62380, 0xc000e22c90, {0xc000cc24d8, 0x34}, 0x1388, {0xc000cc2549, 0x4}, {0x37d1808, ...}}, ...}, ...) github.com/multiprocessio/datastation/runner/database.go:470 +0x1405github.com/multiprocessio/datastation/runner.EvalContext.Eval({{{0xc000cc2407 http://github.com/multiprocessio/datastation/runner.EvalContext.Eval(%7B%7B%7B0xc000cc2407, 0xc}, 0xc000e62380, 0xc000e22c90, {0xc000cc24d8, 0x34}, 0x1388, {0xc000cc2549, 0x4}, {0x37d1808, ...}}, ...}, ...) github.com/multiprocessio/datastation/runner/eval.go:209 +0x878main.eval({{{0xc000cc2407, 0xc}, 0xc000e62380, 0xc000e22c90, {0xc000cc24d8, 0x34}, 0x1388, {0xc000cc2549, 0x4}, {0x37d1808, ...}}, ...}, ...) ./main.go:88 +0x7bmain.main() ./main.go:126 +0x25e

— Reply to this email directly, view it on GitHub https://github.com/denisenkom/go-mssqldb/issues/96#issuecomment-1694641276, or unsubscribe https://github.com/notifications/unsubscribe-auth/AAAFOSEIHT256WCOT5J2WKTXXMUZBANCNFSM4A6Z7NEQ . You are receiving this because you were mentioned.Message ID: @.***>

[fortunewalla]

The db driver you are using doesn't support named pipes. So it's never going to work.

I was told that it is this library go-mssqldb that they are using but I guess it is not possible.

Thanks for your quick replies to all these. 🙏🏼