Go with SQL Server driver is unable to connect successfully, login fail

Question

I am trying to use Microsoft SQL Server with a Go program to connect to a database in SQL server and read some data from the database.

However when I use err=db.ping() it causes an error which says:

login failed for user 'sakhaloo'

I downloaded the zip file of the driver package from this directory: github.com/denisenkom/go-mssqldb then I copy the files within the zipped file and paste them in the following address : C:\Go\src\github.com\denisenkom\go-mssqldb

Another problem is when I try to open my SQL database with SQL Server 2014 Management Studio it doesn't accept my user name or password, actually it causes this error when I enter my username and password:

A connection was successfully established with the server, but then an error occurred during the login process. (provider: Shared Memory Provider, error: 0-no process is on the other end of the pipe.)(Microsoft SQL Server, Error:233)

i don't know what is wrong with all this process.

This is my code:

package main
import (
   //_ "code.google.com/p/odbc"
    _ "github.com/denisenkom/go-mssqldb"
    "database/sql"
    "fmt"
    "log"
    //"github.com/astaxie/beedb"
   // "github.com/weigj/go-odbc"
)

var (
    uName, pass string
    p *Person
)

type Person struct {
    userName string
    passWord string
    Email string
}

func main() {
    db, err := sql.Open("mssql", "server=SAKHALOO-PC;user id=sakhaloo;password=hoollehayerazi;database=webApp" )
    if err != nil {
        log.Fatal(err) 
    }
    defer db.Close()
    err = db.Ping()
    if err != nil {
        log.Fatal(err)
    }
    fmt.Println("Please enter your full name & password:")
    fmt.Scanln(&uName, &pass)
    row := db.QueryRow("SELECT username, password, email FROM user WHERE username=? and password=?", uName, pass)
    fmt.Println(row)
    p := new(Person)
    err = row.Scan(&p.userName, &p.passWord, &p.Email)
    fmt.Printf("%s , %s , %s \n", p.userName, p.passWord, p.Email)
    fmt.Printf("Hi %s, your email address is : %s", uName, p.Email)
}

Solution

I want to share my experience working out a simple demo Go language database program using SQL Server Express 2008. I believe that the following lessons learned will apply to any SQL Server version from 2008 and later.

My SQL Server Express was previously installed with the default instance rather than a named instance. It was also installed to use Windows Authentication. Both of these settings were required by other development work that I do. The other work that I do uses SQL Server Express on the same PC as the application as a local database engine. I was expecting to be able to use Windows Authentication with SQL Server in my Go application.

Looking for a driver and a small sample program to use with a local SQL Server and Go, this question came up in my search. I thought to add a bit of additional information and a sample program to help others to get started and learn from my mistakes. I also found this article GoLang and MSSQL Databases: An Example helpful especially after making sufficient mistakes that I understood it better.

The final version of my test program is as follows:

package main

import (
    "fmt"
    "log"
    "database/sql"
     _ "github.com/denisenkom/go-mssqldb"     // the underscore indicates the package is used
)    

func main() {
    fmt.Println("starting app")

    // the user needs to be setup in SQL Server as an SQL Server user.
    // see create login and the create user SQL commands as well as the
    // SQL Server Management Studio documentation to turn on Hybrid Authentication
    // which allows both Windows Authentication and SQL Server Authentication.
    // also need to grant to the user the proper access permissions.
    // also need to enable TCP protocol in SQL Server Configuration Manager.
    //
    // you could also use Windows Authentication if you specify the fully qualified
    // user id which would specify the domain as well as the user id.
    // for instance you could specify "user id=domain\\user;password=userpw;".

    condb, errdb := sql.Open("mssql", "server=localhost;user id=gouser;password=g0us3r;")
    if errdb  != nil {
        fmt.Println("  Error open db:", errdb.Error())
    }

    defer condb.Close()

    errdb = condb.Ping()
    if errdb != nil {
        log.Fatal(errdb)
    }

    // drop the database if it is there so we can recreate it
    // next we will recreate the database, put a table into it,
    // and add a few rows.
    _, errdb = condb.Exec("drop database mydbthing")
    if errdb != nil {
        fmt.Println("  Error Exec db: drop db - ", errdb.Error())
    }

    _, errdb = condb.Exec("create database mydbthing")
    if errdb  != nil {
        fmt.Println("  Error Exec db: create db - ", errdb.Error())
    }

    _, errdb = condb.Exec("use  mydbthing")
    if errdb  != nil {
        fmt.Println("  Error Exec db: using db - ", errdb.Error())
    }

    _, errdb = condb.Exec("create table junky (one int, two int)")
    if errdb  != nil {
        fmt.Println("  Error Exec db: create table - ", errdb.Error())
    }

    _, errdb = condb.Exec("insert into junky (one, two) values (101, 201)")
    if errdb  != nil {
        fmt.Println("  Error Exec db: insert table 1 - ", errdb.Error())
    }
    _, errdb = condb.Exec("insert into junky (one, two) values (102, 202)")
    if errdb  != nil {
        fmt.Println("  Error Exec db: insert table 2 - ", errdb.Error())
    }
    _, errdb = condb.Exec("insert into junky (one, two) values (103, 203)")
    if errdb  != nil {
        fmt.Println("  Error Exec db: insert table 3 - ", errdb.Error())
    }

    // Now that we have our database lets read some records and print them.
    var (
        one  int
        two  int
    )

    // documentation about a simple query and results loop is at URL
    // http://go-database-sql.org/retrieving.html
    // we use Query() and not Exec() as we expect zero or more rows to
    // be returned. only use Query() if rows may be returned.
    fmt.Println ("  Query our table for the three rows we inserted.")
    rows, errdb := condb.Query ("select one, two from junky")
    defer rows.Close()
    for rows.Next() {
        err:= rows.Scan (&one, &two)
        if err != nil {
            fmt.Println("  Error Query db: select - ", err.Error())
        } else {
            fmt.Printf("    - one %d and two %d\n", one, two)
        }
    }
    rows.Close()

    errdb = rows.Err()
    if errdb != nil {
        fmt.Println("  Error Query db: processing rows - ", errdb.Error())
    }

    fmt.Println("ending app")
}

The first time the above application is run once the necessary changes to SQL Server settings are made, it will generate the following output. Since the database does not exist the first time the program is run, you will see the error message printed. However subsequent times it is run the database will exist and the error message when the database is dropped exist will not be output.

starting app
  Error Exec db: drop db -  mssql: Cannot drop the database 'mydbthing', because it does not exist or you do not have permission.
  Query our table for the three rows we inserted.
    - one 101 and two 201
    - one 102 and two 202
    - one 103 and two 203
ending app

Installing the SQL Server Driver package

The first thing I had to do was to find a database driver package that would work with SQL Server. Several stackoverflow postings recommended github.com/denisenkom/go-mssqldb so that is what used.

In order to use the github.com/denisenkom/go-mssqldb package I had to first retrieve it from the github repository using go get github.com/denisenkom/go-mssqldb from the command shell window created by running Git Shell.

Git Shell is the github shell that is installed as part of installing Git. I found that I had to run the go get command in the Git Shell in order for the go command to find the git application and access the github repository. When I tried to run the go get command from a normal command shell I saw an error message indicating that the git command could not be found.

After installing the go-mssqldb package I was able to run my sample application and kept running into a runtime error from the Open(). The output from my application was the following:

starting app

Error Exec db: create db -  Unable to open tcp connection with host 'localhost:1433': dial tcp 127.0.0.1:1433: connectex: No connection could be made because the target machine actively refused it.

ending app

Enabling TCP connections for SQL Server

After some searching I found a number of different sites which all indicated that the error meant that my SQL Server instance was not configured for TCP/IP. The various postings indicated I needed to use the Sql Server Configuration Manager to enable TCP/IP.

What I discovered is that there are actually two places where TCP/IP needs to be enabled. One was Client Protocols and that was indeed already enabled. However the other was Protocols for MSSQLSERVER and in that one TCP/IP was disabled. So I enabled TCP/IP in the Protocols for MSSQLSERVER section, then restarted the SQL Server service using the Service utility of the Administrative Tools from the Control Panel.

However I was still having problems with any kind of a query after using sql.Open(). I was seeing application output that was some variation of the following. The error message was the same however when function calls had errors could change from one run to the next. I tried changing the connection string specified in the sql.Open() with no results other than different error messages.

starting app
  Error Exec db: create db -  driver: bad connection
  Error Exec db: create table -  driver: bad connection
ending app

Poking around further I found this note in the github repository:

Known Issues

SQL Server 2008 and 2008 R2 engine cannot handle login records when SSL encryption is not disabled. To fix SQL Server 2008 R2 issue, install SQL Server 2008 R2 Service Pack 2. To fix SQL Server 2008 issue, install Microsoft SQL Server 2008 Service Pack 3 and Cumulative update package 3 for SQL Server 2008 SP3. More information: http://support.microsoft.com/kb/2653857

So I downloaded the updates which I never actually installed. While waiting on the download, I poked around more and found the folder containing the actual SQL Server executable along with the Log folder containing a series of files ERRORLOG, ERRORLOG.1, etc.

SQL Server logs indicate SQL Server user required

Looking in the ERRORLOG file I found an error log of SQL Server with the following logs which provided the next piece of the puzzle:

2016-08-15 22:56:22.41 Server      SQL Server is now ready for client connections. This is an informational message; no user action is required.
2016-08-15 23:55:47.51 Logon       Error: 18456, Severity: 14, State: 58.
2016-08-15 23:55:47.51 Logon       Login failed for user 'rchamber'. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: 127.0.0.1]
2016-08-15 23:55:47.61 Logon       Error: 18456, Severity: 14, State: 58.
2016-08-15 23:55:47.61 Logon       Login failed for user 'rchamber'. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: ::1]
2016-08-15 23:55:47.62 Logon       Error: 18456, Severity: 14, State: 58.
2016-08-15 23:55:47.62 Logon       Login failed for user 'rchamber'. Reason: An attempt to login using SQL authentication failed. Server is configured for Windows authentication only. [CLIENT: 127.0.0.1]

I then realized that the Go SQL Server driver was not using Windows Authentication but instead was using SQL Server Authentication. I had tried to use Windows Authentication by specifying an empty user id= however that did not seem to work. So using the sqlcmd utility, I created an SQL Server user.

1> create login gouser with password='g0us3r';
2> go
1> create user gouser for login gouser;
2> go

Next I downloaded and installed the Microsoft SQL Server Management Studio. This is a different utility from the SQL Server Configuration Manager. Using this I did two things: (1) turned on SQL Server Authentication as well as Windows Authentication and (2) provided the necessary permissions for my new SQL Server user gouser. This utility also provided a nice user interface to browse the SQL Server and its various databases.

Make sure that the SQL user you create has sufficient permissions so that it can be used to connect to SQL Server and create a database.

Some Considerations for Using Windows Authentication

After further research I found that I could actually use Windows Authentication however the completely qualified user id and its password must be provided. For an environment using Active Directory with a domain name of "AD" the fully qualified user id would be "AD\userid" and for the local host would be "\userid". I am still researching on being able to automatically use the credentials of the currently logged in user.

After still further research and find assistance from the Go driver developers, Windows Authentication with the current should be possible if the sql.Open() does not include the user information meaning "user id=;password=;" should not be specified.

However this form of automatic Windows Authentication against the current user is only allowed if the SQL Server instance is using Kerberos with a valid Service Principal Name (SPN). If you perform a restart on your instance of SQL Server and you see the following log in your ERRORLOG file, SQL Server was not able to initialize with Kerberos.

2016-08-23 18:32:16.77 Server The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.

See also How to make sure that you are using Kerberos authentication when you create a remote connection to an instance of SQL Server 2005 which provides some additional information as well using the setspn command to correct the problem.

See also The SQL Network Interface library was unable to register SPN.

About Trusted Windows Authentication (Updated as per request from @Richard by @xpt)

Windows Authentication is logging into SQL Server with Windows credential without specifying a user id and password. This is called trusted connection for sqlcmd or ODBC; or called Single-Sign-On for go-mssqldb Go driver package.

From go-mssqldb's readme in github,

"user id" - enter the SQL Server Authentication user id or the Windows Authentication user id in the DOMAIN\User format. On Windows, if user id is empty or missing Single-Sign-On is used.

So I tried the following two ways with my SQL Server 2008 R2 and both are just working fine:

condb, errdb := sql.Open("mssql", "server=MyServer;user id=;password=DONTCARE;")
condb, errdb := sql.Open("mssql", "server=MyServer;user id=;password=;")

Note that using server=localhost would fail, as it is important to have the correct host name, from that name the driver is building the SQL Server kerberos Service Principal Name (SPN) and that name must match SQL Server's. I used a proper Service Principal Name (SPN) with my test so it works.

Comments

[xpt]

Thanks a lot @RichardChambers. Appreciate your comprehensive answer to give people like me an easy start.

[Richard Chambers]

@xpt, you are welcome. Since the moderators failed your edit, I have put it back. Thank you for taking the time to attempt an update on what you found.

[eremmel]

I was not able to connect to SQL server instance on the same host with SSO. I found here that adding ServerSPN=MSSQLSvc/127.0.0.1:49878; resolves the issue. This is in combination with telegraf. It might already be solved in the latest go module (not distrubuted with telegraf yet).

[Richard Chambers]

@eremmel according to blogs.technet.microsoft.com/fort_sql/2012/07/03/… the port number of 49878 is a dynamic TCP port used for named instances. You can also create a static, known port for a particular instance. Do you know if telegraf is using port 49878 as a static port for an SQL Server instance? Does the ServerSPN=MSSQLSvc/127.0.0.1:49878; create a Service Principal Name for the named SQL Server instance on the localhost at that port number?