feature: enable column encryption at the TDS level

[simenfd]

This might not be an issue, as much as it is a missing feature:

It seems like sql server has a feature called "always encrypted" which can be enabled by writing "Column Encryption Setting=enabled;" in the connection string. The idea is that the driver pulls some keys from a certificate and automatically decrypts the encrypted columns. Currently, this is not working with this driver.

[kardianos]

@simenfd Can you link to official docs in the TDS documentation?

[dimdin]

They use AES-256 with CBC and HMAC with SHA-512 for column encryption.
The latest version of TDS spec is https://msdn.microsoft.com/en-us/library/dd304523.aspx
FEATUREEXTACK message is used for column encryption feature activation and version, and COLMETADATA is extended with keys.

[yang-jiayi]

parameter "Column Encryption Setting=enabled" is woking?

package main

import (
"context"
"database/sql"
"fmt"
"log"

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

)

var db *sql.DB

var server = ""
var port = 1433
var user = "sa"
var password = "<>"
var database = ""

// Always Encrypted
var alwaysencrypted = "enabled"

func main() {
connString := fmt.Sprintf("server=%s;user id=%s;password=%s;port=%d;database=%s;Column Encryption Setting=%s;",
server, user, password, port, database, alwaysencrypted)

//fmt.Printf(connString)

var err error

db, err = sql.Open("sqlserver", connString)
if err != nil {
	log.Fatal("Error creating connection pool:", err.Error())
}
fmt.Printf("Connected!\n")

count, err := ReadaeTest1()
fmt.Printf("Read %d rows successfully.\n", count)

}

func ReadaeTest1() (int, error) {
ctx := context.Background()

err := db.PingContext(ctx)
if err != nil {
	log.Fatal("Error pinging database: " + err.Error())
}

tsql := fmt.Sprintf("SELECT colA, colB, colC FROM aeTestDB.dbo.aeTest1;")

// QLクエリの実行
rows, err := db.QueryContext(ctx, tsql)
if err != nil {
	log.Fatal("Error reading rows: " + err.Error())
	return -1, err
}

defer rows.Close()

var count int = 0

// リザルトループ
for rows.Next() {
	var colA int
	var colB, colC string

	err := rows.Scan(&colA, &colB, &colC)
	if err != nil {
		log.Fatal("Error reading rows: " + err.Error())
		return -1, err
	}

	fmt.Printf("colA: %d, colB: %s, colC: %s\n", colA, colB, colC)
	count++
}

return count, nil

}

[rcscoggin]

Any update on adding support for Column Level Encryption to tds.go? thanks!

[denysvitali]

Hello everyone, this feature is now implemented (check the related PR). At the moment only support for decryption is available though.

cc/ @rcscoggin , @simenfd

[shueybubbles]

I've started a more extensive AE implementation in the Microsoft fork and welcome feedback. We're starting with decryption using local certs or Azure Key Vault then expanding to encryption. microsoft#116