Why can I access my local SQL server without a DSN?

ghz 8months ago ⋅ 82 views

I've been trying to connect to a SQL Server database with Go. I'm new to Go and have discovered that I can access the database with a URL style DSN and I've been trying that out instead of ODBC connection string.

However, when I try to make a connection I get the following message.

login error: Login failed. The login is from an untrusted domain and cannot be used with Integrated authentication.

The code is

// using "github.com/microsoft/go-mssqldb" as a driver
    dsn := &url.URL{
        Scheme: "sqlserver",
        User:   url.UserPassword("myusername", "mypassword"),
        Host:   fmt.Sprintf("%s:%d", "servername", 1433),

        RawQuery: query.Encode(),
    }

    db, err := sql.Open("sqlserver", dsn.String())
    if err != nil {
        log.Fatal(err)
    }

    defer db.Close()

    err = db.Ping()
    if err != nil {
        log.Fatal(err)
    }

Instead when I try

    db, err := sql.Open("sqlserver", "")
    if err != nil {
        log.Fatal(err)
    }

    defer db.Close()

    err = db.Ping()
    if err != nil {
        log.Fatal(err)
    }

It works... Which doesn't make any sense to me.

Could anyone tell me what's happening, please? I've read https://github.com/microsoft/go-mssqldb#readme but still don't know. Also, could someone tell me if using the URL style of DSN over ODBC connection string is better or not? In the readme of the driver, the URL style is recommended and can't find any reasoning for it.

Answers

The issue you're encountering is related to the authentication mechanism being used when connecting to the SQL Server database. In your first code snippet, you're specifying the username and password directly in the DSN (Data Source Name), but the error message indicates that the login failed because it's using Integrated authentication, which suggests that it's attempting to use Windows Authentication (Integrated Security) instead of SQL Server Authentication.

To resolve this issue, you can explicitly specify that you want to use SQL Server Authentication in your DSN. Here's how you can modify your code to do that:

import (
    "database/sql"
    "fmt"
    "log"
    "net/url"

    _ "github.com/denisenkom/go-mssqldb"
)

func main() {
    dsn := fmt.Sprintf("sqlserver://%s:%s@%s:%d?database=%s",
        "myusername", "mypassword", "servername", 1433, "mydatabase")

    db, err := sql.Open("sqlserver", dsn)
    if err != nil {
        log.Fatal(err)
    }

    defer db.Close()

    err = db.Ping()
    if err != nil {
        log.Fatal(err)
    }

    fmt.Println("Connected to SQL Server!")
}

In this code:

  • We construct the DSN string manually using SQL Server Authentication (myusername and mypassword).
  • The sqlserver scheme is used in the DSN string.
  • We specify the server name, port, and database name in the DSN string.
  • We use the go-mssqldb package for the SQL Server driver.

Regarding your question about using the URL style of DSN over ODBC connection string, there's no inherent superiority of one over the other. It often comes down to personal preference and the specific requirements of your project. Both methods are valid ways to specify connection parameters, and you can choose whichever one you find more convenient or readable.

#go