Always Encrypted" column in SQL server

Mohammed Izzath 0

I can't decrypt the "Always Encrypted" column in SQL server using the keys in azure key vault, I am using Go language for the process and I can't find any reference reagrding the decryption, please guide me to decrypt the data in SQL server, thank you.

Erland Sommarskog 112.7K Reputation points MVP

2024-07-27T19:20:55.93+00:00

How do you connect to SQL Server from Go? Normally, decryption is performed in the client API. But I am uncertain of what you are using for Go.

Mohammed Izzath 0

Hi Erland Sommarskog,

I am using this below package for the connection :

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

According to many documentations, if the "column encryption setting=enabled" is added to the connection string, it'll decrypt the columns automatically and the above package will uses the Keys from Azure Key Vault for the decryption.

Even though, I am keep getting responses like below.

Decrypted Data: ☺�∟☻�9�u§��►�c@��M�n�cG��>pX�2ҋ&C��ׅmMA�)8]u�1��‼P�X��ԡ↑�}>A��/V�:�M�S��393��s^�

This is my code for the decryption process, please let me know if i am doing anything wrong.

package main

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

	"github.com/Azure/azure-sdk-for-go/sdk/azidentity"
	"github.com/Azure/azure-sdk-for-go/sdk/security/keyvault/azkeys"
	_ "github.com/denisenkom/go-mssqldb"
)

const (
	server   = "alisa.database.windows.net"
	port     = 1433
	user     = "<user>"
	password = "<my-pass>"
	database = "<my-db>"
)

const (
	keyVaultName = "<my-keyvault-name>"
	clientID     = "<my-client-id>"
	clientSecret = "<my-client-secret>"
	tenantID     = "<my-tenant-id>"
	keyName      = "<key-name>"
	keyVersion   = "<key-version>"
	keyVaultURL  = "https://<vaultname>.vault.azure.net/"
)

var db *sql.DB

func ReadaeTest1(ctx context.Context) (int, error) {
	cred, err := azidentity.NewClientSecretCredential(tenantID, clientID, clientSecret, nil)
	if err != nil {
		log.Fatalf("Failed to create credential: %v", err)
	}

	client, err := azkeys.NewClient(keyVaultURL, cred, nil)
	if err != nil {
		log.Fatalf("Failed to create Key Vault client: %v", err)
	}

	key, err := client.GetKey(context.TODO(), keyName, keyVersion, nil)
	if err != nil {
		log.Fatalf("Failed to retrieve CMK from Azure Key Vault: %v", err)
	}
	fmt.Printf("Key Name: %s\n", *key.Key.KID)

	tsql := `SELECT testing FROM alisa1.dbo.Test;`

	rows, err := db.QueryContext(ctx, tsql)
	if err != nil {
		return -1, err
	}
	defer rows.Close()

	var count int

	for rows.Next() {
		var testingValue string
		if err := rows.Scan(&testingValue); err != nil {
			return -1, err
		}
		fmt.Println("Decrypted Data:", testingValue)
		count++
	}

	if err := rows.Err(); err != nil {
		return -1, err
	}

	return count, nil
}

func main() {
	fmt.Println("starting main")

	connString := fmt.Sprintf("server=%s;user id=%s;password=%s;port=%d;database=%s;column encryption setting=enabled;keyStoreAuthentication=KeyVaultClientSecret;keyStorePrincipalId=%s;keyStoreSecret=%s;keyStoreTenantId=%s;",
		server, user, password, port, database, clientID, clientSecret, tenantID)

	var err error
	db, err = sql.Open("sqlserver", connString)
	if err != nil {
		log.Fatal("Error creating connection pool:", err.Error())
	}
	defer db.Close()

	ctx := context.Background()

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

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

	count, err := ReadaeTest1(ctx)
	if err != nil {
		log.Fatal("Error reading rows:", err.Error())
	}
	fmt.Printf("Read %d rows successfully.\n", count)
}

I have checked whether I can get the key from Azure vault and i got a URL from key.Key.KID, i just printed that.

Mahesh Kurva 725 Reputation points Microsoft Vendor

2024-08-27T15:49:07.3633333+00:00

Hi Mohammed Izzath,

Just checking in to see if the below answer provided by @ Erland Sommarskog helped.

If this answer is helpful, do click Accept Answer and Upvote and if you have any further queries do let us know.

3 answers

Erland Sommarskog 112.7K Reputation points MVP

2024-07-29T21:53:19.7633333+00:00
Since I don't know Go, and I don't think you generally can expect knowledge about Go and this specific driver here, I post an Answer with some advices for troubleshooting.

First make sure that you can retrieve an unencrypted string column, using the same code. (To make sure that you are not doing some basic silly mistake.)

Then run a query against an AE column for which the encryption is in your local certificate store. You can easily achieve this by using the AE wizard in SSMS. If this works, you know that the issue is the access to Azure Key Value. If it does not work, we can rule out AKV from the suspects.

Also run the program with the local key from a different machine which does not have access to the encryption key, so that you understand what the behaviour is when the encryption key is not available.

These steps may not lead to the answer, but you will be in better position to open an Issue on the Github site, if you first conduct your own troubleshooting.

It occurs to me a little funny that you specify in the code all the details about AKV. I was under the impression that such information was stored in the database together with the key. But I have never used AE with Azure Key Vault. And certainly not from Go.
Please sign in to rate this answer.

1 person found this answer helpful.
Mahesh Kurva 725 Reputation points Microsoft Vendor

2024-08-28T16:36:38.7866667+00:00

Hi Mohammed Izzath,

Just checking in to see if the above answer provided by @ Erland Sommarskog helped.

If this answer is helpful, do click Accept Answer and Upvote and if you have any further queries do let us know.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
Bruce (SqlWork.com) 66,706 Reputation points

2024-07-28T18:22:37.5633333+00:00

Review the driver and samples

https://github.com/microsoft/go-mssqldb
Please sign in to rate this answer.
Mohammed Izzath 0 Reputation points

2024-07-29T09:46:52.6+00:00

Hi Bruce,

If I use this package, i am getting error "Unable to find provider AZURE_KEY_VAULT to decrypt CEK" so I have used that one from "denisenkom"
PACKAGE: github.com/microsoft/go-mssqldb
Package that I am using: github.com/denisenkom/go-mssqldb

May be the issue fixed in this package, please review my code that I've share in the comments and let me know if you found what's wrong.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.
LiHongMSFT-4306 28,041 Reputation points

2024-07-29T02:26:34.67+00:00

Hi @Mohammed Izzath

Always Encrypted capabilities, including built-in column master key store providers vary by a driver library and its version.

See Develop applications using Always Encrypted for the list of client drivers supporting Always Encrypted and for information on how to develop applications that query encrypted columns.

You can also query encrypted columns using SQL tools, for example Azure Data Studio or SSMS.

See this doc for more details: Always Encrypted.

Best regards,

Cosmog

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Please sign in to rate this answer.
Mohammed Izzath 0 Reputation points

2024-07-29T09:49:42.57+00:00

Hi LiHongMSFT-4306,

I have decrypted the column in SQL server using SSMS by adding "column encryption setting=enabled" when login into the SSMS, but i want to decrypt it using the GO application, can you please review my code that I've share in the comments and let me know if you found what's wrong.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

Always Encrypted" column in SQL server

3 answers

Your answer