go-mssqldb

About go-mssqldb

This large README is shown as a plain-text preview. Read the full README at the source

# Microsoft's official Go MSSQL driver

[![Go Reference](https://pkg.go.dev/badge/github.com/microsoft/go-mssqldb.svg)](https://pkg.go.dev/github.com/microsoft/go-mssqldb)
[![Build Status](https://github.com/microsoft/go-mssqldb/actions/workflows/pr-validation.yml/badge.svg?branch=main)](https://github.com/microsoft/go-mssqldb/actions/workflows/pr-validation.yml)
[![codecov](https://codecov.io/gh/microsoft/go-mssqldb/branch/main/graph/badge.svg)](https://codecov.io/gh/microsoft/go-mssqldb)
[![Open in Dev Containers](https://img.shields.io/static/v1?label=Dev%20Containers&message=Open&color=blue&logo=visualstudiocode)](https://vscode.dev/redirect?url=vscode://ms-vscode-remote.remote-containers/cloneInVolume?url=https://github.com/microsoft/go-mssqldb)

A pure Go database/sql driver for Microsoft SQL Server and Azure SQL Database. This is the recommended Go SQL Server driver for connecting Go applications to SQL Server, Azure SQL Database, Azure SQL Managed Instance, and Azure Synapse Analytics.

**Keywords:** golang sql server driver, go mssql, azure sql go, go-mssqldb, sql server golang, mssql go driver

## Install

Requires Go 1.25 or above.

Install with `go get 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.

All connection string parameters are case-insensitive. Providing the same parameter more than once with different casing (e.g., `TrustServerCertificate` and `trustservercertificate`) will result in a parse error.

Boolean parameters accept `1`, `t`, `T`, `TRUE`, `true`, `True`, `0`, `f`, `F`, `FALSE`, `false`, `False`, and additionally `yes` and `no` in any casing.

### Common parameters

* `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. The user domain sensitive to the case which is defined in the connection string.
* `password`
* `database`
* `connection timeout` - in seconds (default is 0 for no timeout), set to 0 for no timeout. Recommended to set to 0 and use context to manage query and connection timeouts.
* `dial timeout` - in seconds (default is 15 times the number of registered protocols), set to 0 for no timeout.
* `encrypt`
  * `strict` - Data sent between client and server is encrypted E2E using [TDS8](https://learn.microsoft.com/en-us/sql/relational-databases/security/networking/tds-8?view=sql-server-ver16).
  * `disable` - Data send between client and server is not encrypted.
  * `false`/`optional`/`no`/`0`/`f` - Data sent between client and server is not encrypted beyond the login packet. (Default)
  * `true`/`mandatory`/`yes`/`1`/`t` - Data sent between client and server is encrypted.
* `app name` - The application name (default is go-mssqldb)
* `authenticator` - Can be used to specify use of a registered authentication provider. (e.g. ntlm, winsspi (on windows) or krb5 (on linux))
* `timezone` - Sets the time zone used by the driver when parsing time types. For example: `timezone=Asia/Shanghai`. Supports [IANA](https://www.iana.org/time-zones) time zone names.

### Connection parameters for ODBC and ADO style connection strings

* `server` - host or host\instance (default localhost)
* `port` - specifies the host\instance port (default 1433). If instance name is provided but no port, the driver will use SQL Server Browser to discover the port.

### Less common parameters

* `keepAlive` - in seconds; 0 to disable (default is 30)
* `failoverpartner` - host or host\instance (default is no partner).
* `failoverport` - used only when there is no instance in failoverpartner (default 1433)
* `failoverpartnerspn` - The kerberos SPN (Service Principal Name) for the failover partner. Default is MSSQLSvc/host:(port|instance), matching how the driver generates `ServerSPN`.
* `packet size` - in bytes; 512 to 32767 (default is 4096)
  * Encrypted connections have a maximum packet size of 16383 bytes
  * Further information on usage: <https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-network-packet-size-server-configuration-option>
* `log` - logging flags (default `0`/no logging, `255` for full logging)
  * `1` log errors
  * `2` log messages
  * `4` log rows affected
  * `8` trace sql statements
  * `16` log statement parameters
  * `32` log transaction begin/end
  * `64` additional debug logs
  * `128` log retries
* `TrustServerCertificate`
  * false - Server certificate is checked. Default is false if encrypt is specified.
  * true - Server certificate is not checked. Default is true if encrypt is not specified. If trust server certificate is true, driver accepts any certificate presented by the server and any host name in that certificate. In this mode, TLS is susceptible to man-in-the-middle attacks. This should be used only for testing.
* `certificate` - The file path to a certificate authority (CA) certificate or server certificate for traditional X.509 chain validation. The specified certificate overrides the go platform specific CA certificates. The driver validates the certificate chain, expiry, and hostname. Supports PEM and DER formats.
* `serverCertificate` - The file path to a server certificate for byte-for-byte comparison validation (new in v1.9.6). The driver validates that the server's certificate exactly matches this file, skipping chain validation, expiry checks, and hostname validation. This matches Microsoft.Data.SqlClient behavior. Cannot be used with `certificate` or `hostnameincertificate`. Supports PEM and DER formats.
* `hostNameInCertificate` - Specifies the Common Name (CN) in the server certificate. Default value is the server host. Used with the `certificate` parameter, not applicable for `serverCertificate`.
* `tlsmin` - Specifies the minimum TLS version for negotiating encryption with the server. Recognized values are `1.0`, `1.1`, `1.2`, `1.3`. If not set to a recognized value the default value for the `tls` package will be used. The default is currently `1.2`. 
* `ServerSPN` - The kerberos SPN (Service Principal Name) for the server. Default is MSSQLSvc/host:port.
* `Workstation ID` - The workstation name (default is the host name)
* `ApplicationIntent` - Can be given the value `ReadOnly` to initiate a read-only connection to an Availability Group listener. The `database` must be specified when connecting with `Application Intent` set to `ReadOnly`.
* `protocol` - forces use of a protocol. Make sure the corresponding package is imported.
* `columnencryption` or `column encryption setting` - a boolean value indicating whether Always Encrypted should be enabled on the connection.
* `multisubnetfailover`
  * `true` (Default) Client attempt to connect to all IPs simultaneously. 
  * `false` Client attempts to connect to IPs in serial.
* `guid conversion` - Enables the conversion of GUIDs, so that byte order is preserved. UniqueIdentifier isn't supported for nullable fields, NullUniqueIdentifier must be used instead.

### Connection parameters for namedpipe package
* `pipe`  - If set, no Browser query is made and named pipe used will be `\\<host>\pipe\<pipe>`
* `protocol` can be set to `np`
* For a non-URL DSN, the `server` parameter can be set to the full pipe name like `\\host\pipe\sql\query`

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.

* Keytabs - Specify the username, keytab file, the krb5.conf file, and realm.

      authenticator=krb5;server=DatabaseServerName;database=DBName;user id=MyUserName;krb5-realm=domain.com;krb5-configfile=/etc/krb5.conf;krb5-keytabfile=~/MyUserName.keytab

* Credential Cache - Specify the krb5.conf file path and credential cache file path.

      authenticator=krb5;server=DatabaseServerName;database=DBName;krb5-configfile=/etc/krb5.conf;krb5-credcachefile=~/MyUserNameCachedCreds 

* Raw credentials - Specity krb5.confg, Username, Password and Realm.

      authenticator=krb5;server=DatabaseServerName;database=DBName;user id=MyUserName;password=foo;krb5-realm=comani.com;krb5-configfile=/etc/krb5.conf;

### Kerberos Parameters

* `authenticator` - set this to `krb5` to enable kerberos authentication. If this is not present, the default provider would be `ntlm` for unix and `winsspi` for windows.
* `krb5-configfile` (optional) - path to kerberos configuration file. Defaults to `/etc/krb5.conf`. Can also be set using `KRB5_CONFIG` environment variable.
* `krb5-realm` (required with keytab and raw credentials) - Domain name for kerberos authentication. Omit this parameter if the realm is part of the user name like `username@REALM`.
* `krb5-keytabfile` - path to Keytab file. Can also be set using environment variable `KRB5_KTNAME`. If no parameter or environment variable is set, the `DefaultClientKeytabName` value from the krb5 config file is used.
* `krb5-credcachefile` - path to Credential cache. Can also be set using environment variable `KRBCCNAME`.
* `krb5-dnslookupkdc` - Optional parameter in all contexts. Set to lookup KDCs in DNS. Boolean. Default is true. 
* `krb5-udppreferencelimit` - Optional parameter in all contexts. 1 means to always use tcp. MIT krb5 has a default value of 1465, and it prevents user setting more than 32700. Integer. Default is 1.
  
For further information on usage: 
  * <https://web.mit.edu/kerberos/krb5-1.12/doc/admin/conf_files/krb5_conf.html>
  * <https://web.mit.edu/kerberos/krb5-1.12/doc/basic/index.html>

### 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.

    ```go

    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())

    ```

* `sqlserver://username@host/instance?krb5-configfile=path/to/file&krb5-credcachefile=/path/to/cache`
    * `sqlserver://username@host/instance?krb5-configfile=path/to/file&krb5-realm=domain.com&krb5-keytabfile=/path/to/keytabfile`

  For URL connection strings, percent-encode special characters in the username or password. Windows authentication usernames use `%5C` for the domain separator, for example `sqlserver://DOMAIN%5Cusername:password@host`. In ADO and ODBC connection strings, use the literal `DOMAIN\username` form.

2. ADO: `key=value` pairs separated by `;`. Values can contain `;` and other special characters by enclosing them in double quotes. 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;password="my;complex;password";database=master` // Password with semicolons
    * `server=localhost;user id=sa;password="value with ""quotes"" inside";database=master` // Escaped quotes using double quotes
    * `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 common connection parameters:
    * server <= addr, address, network address, data source
    * user id <= user, uid
    * password <= pwd
    * database <= initial catalog
    * app name <= application name, app
    * connection timeout <= connect timeout, timeout
    * failoverpartner <= failover partner
    * failoverpartnerspn <= failover partner spn
    * applicationintent <= application intent
    * trustservercertificate <= trust server certificate
    * multisubnetfailover <= multi subnet failover
    * hostnameincertificate <= host name in certificate
    * serverspn <= server spn
    * servercertificate <= server certificate
    * workstation id <= wsid
    * columnencryption <= column encryption setting

3. 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`

### Using server certificates with encryption

The driver supports two ways to validate server certificates:

#### 1. `serverCertificate` - Byte-for-byte certificate comparison (New in v1.9.6)

When you provide a `serverCertificate` parameter, the driver validates the server by comparing the certificate bytes exactly with the provided file. This:
- Skips hostname validation (allows mismatched hostnames)
- Skips certificate chain validation and expiry checks
- Only accepts connections where the server's certificate exactly matches the provided file
- Matches the behavior of Microsoft.Data.SqlClient

This is useful when:
- The server's DNS name doesn't match the certificate CN/SAN
- You want to validate against a specific certificate without hostname validation
- You're connecting through proxies or load balancers with different hostnames

**Restrictions**: `serverCertificate` cannot be used with `certificate` or `hostnameincertificate` parameters.

#### 2. `certificate` - Traditional chain validation (Backward compatible)

The `certificate` parameter performs standard X.509 certificate chain validation:
- Validates the certificate chain against the provided CA certificate(s)
- Checks certificate expiry and validity
- Enforces hostname validation (unless `hostnameincertificate` is used)

#### Obtaining the server certificate

You can obtain a copy of the server's certificate using OpenSSL:

```bash
openssl s_client -connect server:1433 -showcerts </dev/null 2>/dev/null | openssl x509 -outform PEM > cert.pem
```

#### Example connection strings

Using `serverCertificate` for byte-comparison (skips hostname validation):

URL format:
```
sqlserver://username:password@host:1433?database=master&encrypt=true&serverCertificate=/path/to/cert.pem
```

ADO format:
```
server=myserver;user id=sa;password=mypass;database=master;encrypt=true;serverCertificate=/path/to/cert.pem
```

Using `certificate` for traditional chain validation:

URL format:
```
sqlserver://username:password@host:1433?database=master&encrypt=true&certificate=/path/to/ca.pem
```

ADO format:
```
server=myserver;user id=sa;password=mypass;database=master;encrypt=true;certificate=/path/to/ca.pem
```

### 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](https://pkg.go.dev/github.com/Azure/azure-sdk-for-go/sdk/azidentity#section-readme) 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:

```azurecli
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](https://learn.microsoft.com/en-us/azure/developer/go/azure-sdk-authentication) tutorial.

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

* `fedauth=ActiveDirectoryServicePrincipal` or `fedauth=ActiveDirectoryApplication` - authenticates using an Azure Active Directory application client ID and client secret or certificate. Implemented using [ClientSecretCredential or CertificateCredential](https://github.com/Azure/azure-sdk-for-go/tree/main/sdk/azidentity#authenticating-service-principals)
  * `clientcertpath=<path to certificate file>;password=<certificate password>` or
  * `password=<client secret>`
  * `user id=<application id>[@tenantid]` Note the `@tenantid` component can be omitted if the server's tenant is the same as the application's tenant.
* `fedauth=ActiveDirectoryPassword` - authenticates using a user name and password.
  * `user id=username@domain`
  * `password=<password>`

Frequently Asked Questions