通过go-mssqldb进行golang SQL Server查询 [英] golang SQL Server query via go-mssqldb

问题描述

我正在尝试使用go查询 SQL Server 2008 R2

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

SQL Server 2008 R2实例位于Windows Server 2008 R2下的VM上；我正在Win 7 VMWare主机下进行开发，并从那里运行程序以查询VM上的数据库.数据库已启动并正在运行服务器虚拟机上托管的应用程序.代码如下.

我得到的错误是:

登录错误:读取tcp 192.168.91.1:15222->192.168.91.135:1433:wsarecv:远程主机强行关闭了现有连接.

当指定SQL Server端口(1433)时，将返回此错误.包含或不包含实例都不会更改它.

SQL Server配置为允许远程连接，SQL Server身份验证，未加密的连接，已启用TCP/IP，IPALL端口= 1433.已为80、443、1433、1434上的TCP打开防火墙;UDP，1433年，1434年.在将数据库实例添加到连接字符串中之前，我遇到了另一个错误.

SQL Server记录似乎，以表明机器正在通话.IP地址用于VMWare主机和VM. SQL Server浏览器服务正在运行(访问本地服务").SQL Server代理未运行.我尝试使用ODBC和ADO连接字符串，但似乎收到了相同的错误.任何帮助将不胜感激.

 程序包主要进口 (//严格出于副作用导入go-mssqldb_"github.com/denisenkom/go-mssqldb"数据库/sql"日志")func main(){var n_tables intprintln(sql.Drivers())//URL连接字符串格式//sqlserver://sa:mypass @ localhost?database = master& connection + timeout = 30////用户名= sa，密码= mypass.//sqlserver://sa:my％7Bpass @ somehost?connection + timeout = 30//密码为"my {pass"//注意:pwd为"myP @ 55w0rd"connectString:="sqlserver://SBM:myP％4055w0rd @ VM17:1433?database = AE& connection + timeout = 30"println("Connection string ="，connectString)println(开放连接")db，err:= sql.Open("mssql"，connectString)延迟db.Close()println(打开错误:"，错误)如果err！= nil {log.Fatal(错误)}println(计数TS_TABLES和扫描中的记录")err = db.QueryRow(从ts_tables中选择count(*)").Scan(& n_tables)如果err！= nil {log.Fatal(错误)}println(表数"，n_tables)println(关闭连接")db.Close()} 

输出:

  [2/2] 0xc042002c20连接字符串= sqlserver://VM17_SBM:P％4055word @ VM17:1433?数据库= VM17_SBM_AE_OE_REPO_CL& connection + timeout = 30开放式连接打开错误:(0x0,0x0)计算TS_TABLES中的记录&扫描2017/03/14 19:48:01登录错误:读取tcp 192.168.91.1:15222->192.168.91.135:1433:wsarecv:现有连接被远程主机强行关闭.退出状态1 

解决方案

我在图书馆作者在Github上的评论.

在连接字符串中添加"encrypt = disable"即可.我正在下载SQL Server 2008 R2 x64的SP3更新如此处建议，并在我有空的时候安装它.就目前而言，尽管查询有效.

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

解决方案

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.

这篇关于通过go-mssqldb进行golang SQL Server查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！