Use advanced SQL Server connection properties in Microsoft Fabric with the ODBC connector

Some SQL Server ingestion scenarios depend on connection-string properties that the built-in SQL Server connector in Data Factory for Microsoft Fabric doesn’t expose. If you need to route reads to an Availability Group secondary, connect reliably across a multi-subnet listener, or read columns protected by Always Encrypted, you’ve probably hit that wall. This post shows a supported way around it: use the ODBC connector together with an ODBC data source name (DSN) configured on an on-premises data gateway (OPDG). The example in this post uses Always Encrypted in a Copy job.

Why these properties matter

Advanced SQL Server connection properties exist to support real enterprise patterns without sacrificing performance, availability, or security:

• ApplicationIntent=ReadOnly directs the connection to a read-only secondary replica in an Availability Group, so your ingestion workload reads from a secondary node instead of the primary.
• MultiSubnetFailover=true tells the driver to attempt connections to the IP addresses returned by an Availability Group listener in parallel. When the Availability Group spans subnets, this delivers faster, more reliable connections during and after failover.
• Column Encryption Setting=Enabled allows the driver to transparently decrypt columns protected by Always Encrypted, where sensitive data stays encrypted both at rest and in transit and is decrypted only by an authorized client.

Each of these lives in the connection string. The challenge is getting Fabric to pass them through.

The core idea: push the properties into an ODBC DSN

The solution is to move those properties into an ODBC DSN hosted on the gateway machine, and have Fabric connect through the ODBC connector. The DSN, backed by the Microsoft ODBC Driver for SQL Server, owns the connection string, including the advanced properties, and Fabric points at it by name.

This example uses a Copy job, but the same ODBC connector and DSN approach applies to the Copy activity in a data pipeline.

There’s one important identity consideration for Always Encrypted. Decrypting Always Encrypted columns requires the calling Windows identity to hold the column master key certificate in its personal (user) certificate store. By default the gateway’s Windows service runs as NT SERVICE\PBIEgwService, which authenticates as the machine’s computer account and has no user profile where that certificate can live. To make decryption work, the gateway must run under a service account, a domain user account, or a Group Managed Service Account (gMSA), whose user store contains the certificate.

Walkthrough

Multiple technologies are used for the solution and links to the authoritative documentation for each technology are included. This post is intended to demonstrate how to put them together.

1. Set up Always Encrypted on the source

Configure your column master key, column encryption key, and encrypted columns on SQL Server. To learn more, follow the Getting started with Always Encrypted tutorial. As part of that setup, you export the column master key certificate, which you import on the gateway machine in a later step.

2. Install and configure the on-premises data gateway

Stand up an OPDG on a machine that can reach your SQL Server. Refer to the Install an on-premises data gateway documentation.

3. Create a service account

There are two choices for a service account. The first uses a dedicated Active Directory domain user account to run the gateway service, because a domain account has a real user profile and therefore a user certificate store. To create one, refer to Manage user accounts with Active Directory Administrative Center. Keep in mind that using a standard domain user account as a service account usually means setting its password to never expire, which is not recommended.

4. Change the gateway service account

Change the service account from the default NT SERVICE\PBIEgwService to the account you created. For a domain user account, use the on-premises data gateway app rather than the Windows Services console so the account receives all required privileges. For the steps, refer to Change the on-premises data gateway service account. You can’t set a gMSA through the gateway app, because you don’t know its password. Instead, stop the service, change the account, and restart it with PowerShell:

Stop-Service -Name PBIEgwService

$svc = Get-WmiObject Win32_Service -Filter "Name='PBIEgwService'"
$svc.Change(
    $null, $null, $null, $null, $null, $null,
    "CONTOSO\opdg-sql$", ""
)

Start-Service -Name PBIEgwServiceCode language: PowerShell (powershell)

5. Grant the service account access in SQL Server

Give the account that runs the gateway service the SQL Server permissions it needs to read the source database and table. Use the same account you configured for the gateway: a domain user account appears as CONTOSO\svc-opdg, and a gMSA appears as CONTOSO\opdg-sql$ with a trailing $.

6. Import the certificate into the service account’s user store

If you use a domain user account, sign in to the gateway machine as that account and import the certificate you exported earlier through Certificate Manager. A gMSA has no interactive sign-in, so you can’t log on as it to import the certificate. Instead, use PsExec from Sysinternals to open a command prompt in the gMSA’s context, and then start Certificate Manager from there. Run psexec -i -u DOMAIN\gmsaAccount$ -p ~ cmd.exe, and then run certmgr.msc in the new prompt to import the certificate. For a similar setup for SSRS using a gMSA, refer to the Always Encrypted data displayed in SSRS with a gMSA post for another example.

7. Create the DSN

On the gateway machine, create a System DSN using the ODBC Driver for SQL Server. Give the DSN a name and point it at your server, choose Integrated Windows authentication, enable Column Encryption, and, for Availability Group scenarios, set Application intent to READONLY and select Multi-subnet failover.

8. Create a Copy job using the ODBC connector

In your Copy job, choose the ODBC connector and reference the DSN in the ODBC connection string, for example dsn=opsql. Select your OPDG as the data gateway. You can leave Authentication kind set to Anonymous, because the ODBC driver named in the DSN handles authentication to SQL Server and overrides Fabric’s authentication kind.

9. Verify decryption

Without Column Encryption Setting=Enabled, the encrypted Last_Name column comes across as ciphertext. The data is protected but unreadable:

With the setting enabled in the DSN and the certificate present in the service account’s user store, the Last_Name column is decrypted and lands in plaintext:

Boundaries and things to know

• The ODBC connector requires an on-premises data gateway. In a Copy job it supports Full load and Append, as both source and destination.
• The certificate must reside in the user store of the exact account running the gateway service.
• The ODBC Driver for SQL Server must be installed on the gateway host, and the DSN must be a System DSN so the gateway service can see it.

Next steps

The ODBC connector with a DSN gives you a supported path for advanced SQL Server connection properties, including reading Always Encrypted data into Fabric. To learn more, explore the following documentation:

• ODBC connector overview
• Configure ODBC in a copy activity