mssqlh

README

SQL Server Helper Library

Package mssqlh provides connection string building and helper routines for working with Microsoft SQL Server.

This package provides support for connecting to SQL Server using either:

• https://github.com/denisenkom/go-mssqldb (native GO driver)
• https://github.com/alexbrainman/odbc (ODBC driver)

Using the Connection type, you should be able to switch seamlessly between the two. The package defaults to the "mssql" driver (mssqlh.DriverMSSQL) usless you specify the "odbc" driver (mssqlh.DriverODBC).

Example using Open:

db, err := mssqlh.Open(fqdn)

This uses a trusted connection to the designated server using the mssql driver. It accepts server.domain.com, server\instance, server,port, or server:port.

Example code using NewConnection:

cxn := mssqlh.NewConnection("localhost", "", "", "myapp")
db, err := sql.Open("mssql", cxn.String())

If you don't pass user and password, it defaults to a trusted connection.

Example using the Connection type:

cxn := mssqlh.Connect{
	FQDN:        "db-txn.corp.loc",
	Application: "myapp",
	DialTimeout: 15,
}
cxn.Database = "TXNDB"
db, err := cxn.Open()

Defaults

The package provides the following defaults

1. If no server is specified, use localhost
2. If no user is specified, default to a trusted connection
3. If no application name is specified, default to the name of the executable

Using the ODBC driver

The subpackage odbch provides additional support for using ODBC driver (https://github.com/alexbrainman/odbc)

Example code using the Connection object:

cxn := mssqlh.Connect{
	Driver:     mssqlh.DriverODBC,
	ODBCDriver: odbch.ODBC18,
	FQDN:       "localhost",
}
db, err := cxn.Open()

This connects using the specified ODBC driver.

SQL Server Version Support

GetServer and GetSession should support SQL Server 2005 and beyond. They have been tested on SQL Server 2014 through SQL Server 2019.

There is limited testing with Azure SQL Databases. The GetSession method requires VIEW DATABASE STATE permission.

Linux

It should support Linux but this has recived very little testing.

Linux looks for the following files to locate installed ODBC drivers:

• /usr/local/etc/odbcinst.ini
• /etc/odbcinst.ini

Applications

The system comes with three sample applications

• mssqlh.exe is a sample application
• odbcraw.exe can test ODBC connections from a settings.txt file
• testkerbexe can test connections for Kerberos from a settings.txt file

Documentation

Index

• Constants
• Variables
• func Open(fqdn, database string) (*sql.DB, error)
• func QuoteName(s string) string
• func QuoteString(s string) string
• type Connection
  • func NewConnection(server, user, password, database, app string) Connection
  • func (c Connection) Computer() string
  • func (c Connection) Instance() string
  • func (c Connection) Open() (*sql.DB, error)
  • func (c Connection) Port() int
  • func (c Connection) Redacted(n int) string
  • func (c Connection) ServerName() string
  • func (c Connection) String() string
• type Server
  • func GetServer(ctx context.Context, db *sql.DB) (Server, error)
• type Session
  • func GetSession(ctx context.Context, db *sql.DB) (Session, error)

Constants

const (
	EncryptMandatory string = "Mandatory"
	EncryptNo        string = "No"
	EncryptOptional  string = "Optional"
	EncryptStrict    string = "Strict"
	EncryptYes       string = "Yes"
)

Variables

var DriverMSSQL = "mssql"

DriverMSSQL uses the https://github.com/denisenkom/go-mssqldb library. This is the driver for this package.

var DriverODBC = "odbc"

DriverODBC uses the https://github.com/alexbrainman/odbc library

var ErrInvalidEncrypt = errors.New("invalid encrypt: expected (blank), Optional, Yes, No, Mandatory, or Strict")

https://learn.microsoft.com/en-us/troubleshoot/sql/connect/certificate-chain-not-trusted?tabs=odbc-driver-18x https://techcommunity.microsoft.com/t5/sql-server-blog/odbc-driver-18-0-for-sql-server-released/ba-p/3169228 https://learn.microsoft.com/en-us/sql/connect/odbc/windows/release-notes-odbc-sql-server-windows?view=sql-server-ver16

Functions

func Open

func Open(fqdn, database string) (*sql.DB, error)

Open connects to a SQL Server. It accepts "host[\instance]", "host:port", or "host,port".

func QuoteName

func QuoteName(s string) string

QuoteName wraps a string in [brackets]. It tries to match the functionality of SQL Server's QUOTENAME()

func QuoteString

func QuoteString(s string) string

QuoteString wraps a string in single-quotes and tries to handle embedded quotes

Types

type Connection

type Connection struct {
	// Driver sets the GO driver that will be used.
	// Leaving this blank defaults to DriverMSSQL (the native GO driver).
	Driver         string
	FQDN           string
	User           string
	Password       string
	Database       string
	Application    string
	DialTimeout    int
	ConnectTimeout int
	ODBCDriver     string
	Encrypt        string
}

Connection is the basis for building connection strings

func NewConnection

func NewConnection(server, user, password, database, app string) Connection

NewConnection returns a connection with sane defaults. You can specify the server "host[\instance]", "host:port", or "host,port" format.

func (Connection) Computer

func (c Connection) Computer() string

Computer returns the computer (or host) name from FQDN

func (Connection) Instance

func (c Connection) Instance() string

Instance returns the instance from FQDN

func (Connection) Open

func (c Connection) Open() (*sql.DB, error)

Open connects to the SQL Server

func (Connection) Port

func (c Connection) Port() int

Port returns the port from FQDN. It returns 0 if no port.

func (Connection) Redacted

func (c Connection) Redacted(n int) string

Redacted returns a connection string with the password replaced with "redacted". Optionally, you can specify how many characters of the password to include

func (Connection) ServerName

func (c Connection) ServerName() string

ServerName buids a string in the format server\instance or server:host. Most likely you won't have an instance and a port. Plus I don't think that works. This should be roughly what it tries to connect to.

func (Connection) String

func (c Connection) String() string

String returns a connection string for the given connection. Setting Driver to an invalid type returns an unusable connection string but not an error. It should be caught on Open

type Server

type Server struct {
	// Name holds the result of @@SERVERNAME
	Name      string
	Computer  string
	Instance  string
	Domain    string
	DNSSuffix string
	FQDN      string

	EngineEdition       int
	ProductVersion      string
	ProductMajorVersion int
}

Server holds information about an instance of SQL Server

func GetServer

func GetServer(ctx context.Context, db *sql.DB) (Server, error)

GetServer gets details on the SQL Server TODO Create a queryer interface and accept that (suppport sqlx)

type Session

type Session struct {
	Server          string    `db:"atat_server_name"`
	ID              int       `db:"session_id"`
	ConnectTime     time.Time `db:"connect_time"`
	LoginTime       time.Time `db:"login_time"`
	ClientInterface string    `db:"client_interface_name"`
	ClientVersion   int       `db:"client_version"`
	AuthScheme      string    `db:"auth_scheme"`
	Application     string
	Login           string
	Database        string
}

Session stores information about the connection to SQL Server

func GetSession

func GetSession(ctx context.Context, db *sql.DB) (Session, error)

GetSession gets details on the current connection to SQL Server TODO Create a queryer interface and accept that This requires VIEW DATABASE STATE in Azure