search

denisenkom / go-mssqldb

Microsoft SQL server driver written in go language
BSD 3-Clause "New" or "Revised" License
1.82k stars 499 forks source link

feature: support connecting over named pipes #96

Open FilipDeVos opened 9 years ago

FilipDeVos commented 9 years ago

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 commented 9 years ago

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 commented 9 years ago

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 commented 9 years ago

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 commented 9 years ago

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 commented 9 years ago

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

clns commented 8 years ago

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

FilipDeVos commented 8 years ago

Not really no. Sorry

simnalamburt commented 7 years ago

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

References
kardianos commented 7 years ago

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 commented 7 years ago

@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 commented 2 years ago

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 commented 2 years ago

@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 commented 1 year ago

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

image

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 commented 1 year ago

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 commented 1 year ago

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 commented 1 year ago

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 commented 1 year ago

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 commented 1 year ago

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. 🙏🏼