golang SQL Server query via go-mssqldb

Question

I'm trying to query SQL Server 2008 R2 using go

https://github.com/denisenkom/go-mssqldb.

The SQL Server 2008 R2 instance is on a VM under Windows Server 2008 R2; I'm doing the development under the Win 7 VMWare host and running the program from there to query the DB on the VM. The DB has been up and running an app hosted on the server VM. Code is below.

The error I'm getting is :

[EDIT 2017-03-14 : new error when I specify port]

Login error: read tcp 192.168.91.1:15222->192.168.91.135:1433: wsarecv: An existing connection was forcibly closed by the remote host.

This error is returned when the SQL Server port (1433) is specified. Including or not including the instance doesn't change it.

SQL Server is configured to allow remote connections, SQL Server Auth, Connection not encrypted, TCP/IP enabled, IPALL port=1433. Firewall is open for TCP on 80, 443, 1433, 1434; UDP on 1433, 1434. I was getting a different error until I added the db instance into the connection string.

The SQL server logs seem to indicate that the machines are talking. The IP addresses are for the VMWare host and the VM. The SQL Server Browser service is running (acct "Local Service"). SQL Server Agent is not running. I've tried using ODBC and ADO connection strings and seem to get the same error. Any help would be appreciated.

package main
import (
   // Import go-mssqldb strictly for side-effects
   _ "github.com/denisenkom/go-mssqldb"
   "database/sql"
   "log"
)

func main() {
   var n_tables int

   println (sql.Drivers())

   // URL connection string formats
   //    sqlserver://sa:mypass@localhost?database=master&connection+timeout=30         // username=sa, password=mypass.
   //    sqlserver://sa:my%7Bpass@somehost?connection+timeout=30                       // password is "my{pass"
   // note: pwd is "myP@55w0rd"
   connectString := "sqlserver://SBM:myP%4055w0rd@VM17:1433?database=AE&connection+timeout=30"
   println("Connection string=" , connectString )

   println("open connection")
   db, err := sql.Open("mssql", connectString)
   defer db.Close()
   println ("Open Error:" , err)
   if err != nil {
      log.Fatal(err)
   }

   println("count records in TS_TABLES & scan")
   err = db.QueryRow("Select count(*) from ts_tables").Scan(&n_tables)
   if err != nil {
      log.Fatal(err)
   }
   println ("count of tables" , n_tables)

   println("closing connection")
   db.Close()
}

output:

[2/2]0xc042002c20
Connection string= sqlserver://VM17_SBM:P%4055word@VM17:1433?database=VM17_SBM_AE_OE_REPO_CL&connection+timeout=30
open connection
Open Error: (0x0,0x0)
count records in TS_TABLES & scan
2017/03/14 19:48:01 Login error: read tcp 192.168.91.1:15222->192.168.91.135:1433: wsarecv: An existing connection was forcibly closed by the remote host.
exit status 1

mnemotronic · Accepted Answer · 2017-03-15 04:12:37Z

1

I found the answer in a comment by the library author on Github.

Adding the "encrypt=disable" to the connection string did it. I'm downloading the SP3 update for SQL Server 2008 R2 x64 as suggested here and will install it when I get some time. As for now though the query works.

answered Mar 15, 2017 at 4:12

mnemotronic

1,0462 gold badges10 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

TheGameiswar · Accepted Answer · 2017-03-13 09:37:38Z

0

The github repo says below

Ensure that the SQL Server Browser windows service is running and there is no firewall blocking UDP port 1434. This service is used by the driver to get the TCP port of the SQL Server instance

Ensure your SQLbrowser service is running on your host..

also you can specify your connection string along with port number(this negates the need of starting SQLBrowser service,if your port is static)

answered Mar 13, 2017 at 9:37

TheGameiswar

29k9 gold badges67 silver badges106 bronze badges

4 Comments

mnemotronic Over a year ago

I edited post with additional server config info. SQL Browser is running. Specifying the port doesn't seem to matter. I don't know why NMAP says 1434 is filtered: ` PORT STATE SERVICE VERSION 1433/tcp open ms-sql-s Microsoft SQL Server 2008 R2 10.50.1600; RTM 1434/tcp filtered ms-sql-m `

TheGameiswar Over a year ago

read udp 192.168.91.1:49155->192.168.91.135:1434 why the connection is udp

TheGameiswar Over a year ago

did you enabled tcp ,are you able to ping the server from outside

mnemotronic Over a year ago

TCP and UDP are enabled on both ports. npcap doesn't work on my system so I can't do an nmap UDP scan. I don't allow access to the VM from "outside" my laptop.

Collectives™ on Stack Overflow

golang SQL Server query via go-mssqldb

2 Answers 2

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related