search

microsoft / go-mssqldb

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

readme

Microsoft's official Go MSSQL driver

Go Reference Build status codecov

Install

Requires Go 1.17 or above.

Install with go install github.com/microsoft/go-mssqldb@latest.

Connection Parameters and DSN

The recommended connection string uses a URL format: sqlserver://username:password@host/instance?param1=value&param2=value Other supported formats are listed below.

Common parameters

Connection parameters for ODBC and ADO style connection strings

Less common parameters

Connection parameters for namedpipe package

If no pipe name can be derived from the DSN, connection attempts will first query the SQL Browser service to find the pipe name for the instance.

DNS Resolution through a Custom Dialer

Custom Dialers can be used to resolve DNS if the Connection's Dialer implements the HostDialer interface. This is helpful when the dialer is proxying requests to a different, private network and the DNS record is local to the private network.

Protocol configuration

To force a specific protocol for the connection there two several options:

  1. Prepend the server name in a DSN with the protocol and a colon, like np:host or lpc:host or tcp:host
  2. Set the protocol parameter to the protocol name

msdsn.ProtocolParsers can be reordered to prioritize other protocols ahead of tcp

The admin protocol will not be used for dialing unless the connection string explicitly specifies it. Note SQL Server allows only 1 admin (or DAC) connection to be active at a time.

Kerberos Active Directory authentication outside Windows

To connect with kerberos authentication from a Linux server you can use the optional krb5 package. Imported krb alongside the main driver 

package main

import (
    ...
    _ "github.com/microsoft/go-mssqldb"
    _ "github.com/microsoft/go-mssqldb/integratedauth/krb5"
)

func main() {
    ...
}

It will become available for use when the connection string parameter "authenticator=krb5" is used.

The package supports authentication via 3 methods.

Kerberos Parameters

For further information on usage:

The connection string can be specified in one of three formats

  1. URL: with sqlserver scheme. username and password appears before the host. Any instance appears as the first segment in the path. All other options are query parameters. Examples:

    • sqlserver://username:password@host/instance?param1=value&param2=value
    • sqlserver://username:password@host:port?param1=value&param2=value
    • sqlserver://sa@localhost/SQLExpress?database=master&connection+timeout=30 // `SQLExpress instance.
    • sqlserver://sa:mypass@localhost?database=master&connection+timeout=30 // username=sa, password=mypass.
    • sqlserver://sa:mypass@localhost:1234?database=master&connection+timeout=30 // port 1234 on localhost.
    • sqlserver://sa:my%7Bpass@somehost?connection+timeout=30 // password is "my{pass" A string of this format can be constructed using the URL type in the net/url package.
    
query := url.Values{}
query.Add("app name", "MyAppName")

u := &url.URL{
    Scheme:   "sqlserver",
    User:     url.UserPassword(username, password),
    Host:     fmt.Sprintf("%s:%d", hostname, port),
    // Path:  instance, // if connecting to an instance instead of a port
    RawQuery: query.Encode(),
}
db, err := sql.Open("sqlserver", u.String())

  1. ADO: key=value pairs separated by ;. Values may not contain ;, leading and trailing whitespace is ignored. Examples:

    • server=localhost\\SQLExpress;user id=sa;database=master;app name=MyAppName
    • server=localhost;user id=sa;database=master;app name=MyAppName
    • server=localhost;user id=sa;database=master;app name=MyAppName;krb5-configfile=path/to/file;krb5-credcachefile=path/to/cache;authenticator=krb5
    • server=localhost;user id=sa;database=master;app name=MyAppName;krb5-configfile=path/to/file;krb5-realm=domain.com;krb5-keytabfile=path/to/keytabfile;authenticator=krb5

    ADO strings support synonyms for database, app name, user id, and server

    • server <= addr, address, network address, data source
    • user id <= user, uid
    • database <= initial catalog
    • app name <= application name

  2. ODBC: Prefix with odbc, key=value pairs separated by ;. Allow ; by wrapping values in {}. Examples:

    • odbc:server=localhost\\SQLExpress;user id=sa;database=master;app name=MyAppName
    • odbc:server=localhost;user id=sa;database=master;app name=MyAppName
    • odbc:server=localhost;user id=sa;password={foo;bar} // Value marked with {}, password is "foo;bar"
    • odbc:server=localhost;user id=sa;password={foo{bar} // Value marked with {}, password is "foo{bar"
    • odbc:server=localhost;user id=sa;password={foobar } // Value marked with {}, password is "foobar "
    • odbc:server=localhost;user id=sa;password=foo{bar // Literal {, password is "foo{bar"
    • odbc:server=localhost;user id=sa;password=foo}bar // Literal }, password is "foo}bar"
    • odbc:server=localhost;user id=sa;password={foo{bar} // Literal {, password is "foo{bar"
    • odbc:server=localhost;user id=sa;password={foo}}bar} // Escaped } with}}`, password is "foo}bar"
    • odbc:server=localhost;user id=sa;database=master;app name=MyAppName;krb5-configfile=path/to/file;krb5-credcachefile=path/to/cache;authenticator=krb5
    • odbc:server=localhost;user id=sa;database=master;app name=MyAppName;krb5-configfile=path/to/file;krb5-realm=domain.com;krb5-keytabfile=path/to/keytabfile;authenticator=krb5

Azure Active Directory authentication

Azure Active Directory authentication uses temporary authentication tokens to authenticate. The mssql package does not provide an implementation to obtain tokens: instead, import the azuread package and use driver name azuresql. This driver uses azidentity to acquire tokens using a variety of credential types.

To reduce friction in local development, ActiveDirectoryDefault can authenticate as the user signed into the Azure CLI.

Run the following command to sign into the Azure CLI before running your application using the ActiveDirectoryDefault connection string parameter:

az login

Azure CLI authentication isn't recommended for applications running in Azure. More details are available via the Azure authentication with the Azure Identity module for Go tutorial.

The credential type is determined by the new fedauth connection string parameter.


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

  // Import the Azure AD driver module (also imports the regular driver package)
  "github.com/microsoft/go-mssqldb/azuread"
)

func ConnectWithMSI() (*sql.DB, error) {
  return sql.Open(azuread.DriverName, "sqlserver://azuresql.database.windows.net?database=yourdb&fedauth=ActiveDirectoryMSI")
}

Executing Stored Procedures

To run a stored procedure, set the query text to the procedure name:


var account = "abc"
_, err := db.ExecContext(ctx, "sp_RunMe",
    sql.Named("ID", 123),
    sql.Named("Account", sql.Out{Dest: &account}),
)

Reading Output Parameters from a Stored Procedure with Resultset

To read output parameters from a stored procedure with resultset, make sure you read all the rows before reading the output parameters:


sqltextcreate := `
CREATE PROCEDURE spwithoutputandrows
    @bitparam BIT OUTPUT
AS BEGIN
    SET @bitparam = 1
    SELECT 'Row 1'
END
`
var bitout int64
rows, err := db.QueryContext(ctx, "spwithoutputandrows", sql.Named("bitparam", sql.Out{Dest: &bitout}))
var strrow string
for rows.Next() {
    err = rows.Scan(&strrow)
}
fmt.Printf("bitparam is %d", bitout)

Caveat for local temporary tables

Due to protocol limitations, temporary tables will only be allocated on the connection as a result of executing a query with zero parameters. The following query will, due to the use of a parameter, execute in its own session, and #mytemp will be de-allocated right away:

conn, err := pool.Conn(ctx)
defer conn.Close()
_, err := conn.ExecContext(ctx, "select @p1 as x into #mytemp", 1)
// at this point #mytemp is already dropped again as the session of the ExecContext is over

To work around this, always explicitly create the local temporary table in a query without any parameters. As a special case, the driver will then be able to execute the query directly on the connection-scoped session. The following example works:

conn, err := pool.Conn(ctx)

// Set us up so that temp table is always cleaned up, since conn.Close()
// merely returns conn to pool, rather than actually closing the connection.
defer func() {
    _, _ = conn.ExecContext(ctx, "drop table #mytemp")  // always clean up
    conn.Close() // merely returns conn to pool
}()

// Since we not pass any parameters below, the query will execute on the scope of
// the connection and succeed in creating the table.
_, err := conn.ExecContext(ctx, "create table #mytemp ( x int )")

// #mytemp is now available even if you pass parameters
_, err := conn.ExecContext(ctx, "insert into #mytemp (x) values (@p1)", 1)

Return Status

To get the procedure return status, pass into the parameters a *mssql.ReturnStatus. For example:


var rs mssql.ReturnStatus
_, err := db.ExecContext(ctx, "theproc", &rs)
log.Printf("status=%d", rs)

or

var rs mssql.ReturnStatus
_, err := db.QueryContext(ctx, "theproc", &rs)
for rows.Next() {
    err = rows.Scan(&val)
}
log.Printf("status=%d", rs)

Limitation: ReturnStatus cannot be retrieved using QueryRow.

Parameters

The sqlserver driver uses normal MS SQL Server syntax and expects parameters in the sql query to be in the form of either @Name or @p1 to @pN (ordinal position).


db.QueryContext(ctx, `select * from t where ID = @ID and Name = @p2;`, sql.Named("ID", 6), "Bob")

Parameter Types

To pass specific types to the query parameters, say varchar or date types, you must convert the types to the type before passing in. The following types are supported:

Using an int parameter will send a 4 byte value (int) from a 32bit app and an 8 byte value (bigint) from a 64bit app. To make sure your integer parameter matches the size of the SQL parameter, use the appropriate sized type like int32 or int8.

// If this is passed directly as a parameter, 
// the SQL parameter generated would be nvarchar
name := "Bob"
// If the user_name is defined as varchar,
// it needs to be converted like this:
db.QueryContext(ctx, `select * from t2 where user_name = @p1;`, mssql.VarChar(name))
// Note: Mismatched data types on table and parameter may cause long running queries

Using Always Encrypted

The protocol and cryptography details for AE are detailed elsewhere.

Enablement

To enable AE on a connection, set the ColumnEncryption value to true on a config or pass columnencryption=true in the connection string.

Decryption and encryption won't succeed, however, without also including a decryption key provider. To avoid code size impacts on non-AE applications, key providers are not included by default.

Include the local certificate providers:

 import (
  "github.com/microsoft/go-mssqldb/aecmk/localcert"
 )

You can also instantiate a key provider directly in code and hand it to a Connector instance.

c := mssql.NewConnectorConfig(myconfig)
c.RegisterCekProvider(providerName, MyProviderType{})

Decryption

If the correct key provider is included in your application, decryption of encrypted cells happens automatically with no extra server round trips.

Encryption

Encryption of parameters passed to Exec and Query variants requires an extra round trip per query to fetch the encryption metadata. If the error returned by a query attempt indicates a type mismatch between the parameter and the destination table, most likely your input type is not a strict match for the SQL Server data type of the destination. You may be using a Go string when you need to use one of the driver-specific aliases like VarChar or NVarCharMax.

NOTE - Currently char and varchar types do not include a collation parameter component so can't be used for inserting encrypted values. https://github.com/microsoft/go-mssqldb/issues/129

Local certificate AE key provider

Key provider configuration is managed separately without any properties in the connection string. The pfx provider exposes its instance as the variable PfxKeyProvider. You can give it passwords for certificates using SetCertificatePassword(pathToCertificate, path). Use an empty string or "*" as the path to use the same password for all certificates.

The MSSQL_CERTIFICATE_STORE provider exposes its instance as the variable WindowsCertificateStoreKeyProvider.

Both providers can be constrained to an allowed list of encryption key paths by appending paths to provider.AllowedLocations.

Azure Key Vault (AZURE_KEY_VAULT) key provider

Import this provider using github.com/microsoft/go-mssqldb/aecmk/akv

Constrain the provider to an allowed list of key vaults by appending vault host strings like "mykeyvault.vault.azure.net" to akv.KeyProvider.AllowedLocations.

Important Notes

Features

Tests

go test is used for testing. A running instance of MSSQL server is required. Environment variables are used to pass login information.

Example:

    env SQLSERVER_DSN=sqlserver://user:pass@hostname/instance?database=test1 go test

AZURESERVER_DSN environment variable provides the connection string for Azure Active Directory-based authentication. If it's not set the AAD test will be skipped.

Deprecated

These features still exist in the driver, but they are are deprecated.

Query Parameter Token Replace (driver "mssql")

If you use the driver name "mssql" (rather then "sqlserver") the SQL text will be loosly parsed and an attempt to extract identifiers using one of

will be used. This is not recommended with SQL Server. There is at least one existing won't fix issue with the query parsing.

Use the native "@Name" parameters instead with the "sqlserver" driver name.

Known Issues

Contributing

This project is a fork of https://github.com/denisenkom/go-mssqldb and welcomes new and previous contributors. For more informaton on contributing to this project, please see Contributing.

For more information on the roadmap for go-mssqldb, project plans are available for viewing and discussion.

Microsoft Open Source Code of Conduct

This project has adopted the Microsoft Open Source Code of Conduct.

Resources: