Edit

Share via

Authenticate with Microsoft Entra ID in bcp

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics SQL database in Microsoft Fabric

The bulk copy program utility (bcp) supports several Microsoft Entra ID authentication models when you connect to Azure SQL Database, Azure SQL Managed Instance, SQL database in Microsoft Fabric, Azure Synapse Analytics, or SQL Server 2022 (16.x) and later versions.

To check whether your installed bcp supports Microsoft Entra authentication, run bcp --help and verify that -G appears in the list of available arguments.

Platform restrictions

Not all authentication modes are available on every platform:

Microsoft Entra username and password

Provide -G together with -U (username) and -P (password).

The following example exports table bcptest from database testdb on contoso.database.windows.net to file c:\last\data1.dat. Replace <password> with a valid password.

bcp bcptest out "c:\last\data1.dat" -c -S contoso.database.windows.net -d testdb -G -U alice@contoso.onmicrosoft.com -P <password>

The following example imports the same data:

bcp bcptest in "c:\last\data1.dat" -c -S contoso.database.windows.net -d testdb -G -U alice@contoso.onmicrosoft.com -P <password>

Microsoft Entra integrated

Provide -G without -U or -P. The current Windows account (or Kerberos identity on Linux/macOS) must be federated with Microsoft Entra ID. In the following examples, replace <server> with your server name.

Export:

bcp bcptest out "c:\last\data2.dat" -S <server>.database.windows.net -d testdb -G -c

Import:

bcp bcptest in "c:\last\data2.dat" -S <server>.database.windows.net -d testdb -G -c

Microsoft Entra Managed Service Identity

Authenticate as either a system-assigned or user-assigned managed identity through a configured DSN. The same approach works for both bcp in and bcp out.

Important

bcp is tightly coupled to its driver. The major version of bcp must match the major version of the driver the DSN is created with. To determine your bcp version, run bcp -v.

Configure a DSN through the ODBC Data Source Administrator:

  1. Press the Windows key on your keyboard.
  2. Type ODBC and select the appropriate version of the ODBC Data Source Administrator.
  3. Select either the User DSN or System DSN tab.
  4. Select Add and follow the prompts.
  5. When asked for an authentication type, select Azure Managed Service Identity authentication.
  6. For a User Assigned Managed Identity, paste the Object (principal) ID of the identity into the Login ID box on the authentication tab.
  7. Continue following the prompts to finish configuring the DSN.

For a full walkthrough including screenshots, see Creating and editing DSNs in the UI.

Use the -D flag to indicate that the value passed to -S is a DSN. The -D and -S switches can appear in any order on the command line.

bcp bcptest out "c:\last\data1.dat" -c -D -S myDSN -d testdb

Microsoft Entra ID access token

Applies to: Linux and macOS only. Windows isn't supported.

bcp 17.8 and later versions on Linux and macOS can authenticate with an access token. The following examples use the Azure CLI to retrieve the token and write it to a secure temporary file.

Important

The token file must be UTF-16LE without a BOM. Restrict file permissions and delete the file when it's no longer needed, as shown in the following examples.

System-assigned managed identity

Replace <server> with your server name.

  1. Sign in with your managed identity:

    az login --identity

  2. Retrieve the token, write it to a secure temporary file, and run bcp:

    # Create a secure temporary file for the token
tokenFile=$(mktemp)
chmod 600 "$tokenFile"

# Retrieve the access token and write it as UTF-16LE without BOM
az account get-access-token --resource https://database.windows.net --output tsv | cut -f 1 | tr -d '\n' | iconv -f ascii -t UTF-16LE > "$tokenFile"

# Run bcp with the token file
bcp bcptest out data2.dat -S <server>.database.windows.net -d testdb -G -P "$tokenFile" -c

# Clean up token file
rm -f "$tokenFile"

User-assigned managed identity

  1. Sign in with your user-assigned managed identity. Replace <client_id> with a valid value for your environment.

    az login --identity --username <client_id>

  2. Retrieve the token, write it to a secure temporary file, and run bcp. Replace <server> with a valid value for your environment.

    # Create a secure temporary file for the token
tokenFile=$(mktemp)
chmod 600 "$tokenFile"

# Retrieve the access token and write it as UTF-16LE without BOM
az account get-access-token --resource https://database.windows.net --output tsv | cut -f 1 | tr -d '\n' | iconv -f ascii -t UTF-16LE > "$tokenFile"

# Run bcp with the token file
bcp bcptest out data2.dat -S <server>.database.windows.net -d testdb -G -P "$tokenFile" -c

# Clean up token file
rm -f "$tokenFile"

Microsoft Entra interactive

Applies to: Windows only. Linux and macOS aren't supported.

Microsoft Entra interactive authentication uses a dialog to authenticate, and supports multifactor authentication (MFA). Interactive authentication requires bcp version 15.0.1000.34 or later, and ODBC Driver 18 for SQL Server (or driver 17.2 or later).

Provide -G with -U (username) only. Don't include -P. bcp prompts for the password (or for accounts with MFA enabled, completes the configured MFA flow).

bcp bcptest out "c:\last\data1.dat" -c -S contoso.database.windows.net -d testdb -G -U alice@contoso.onmicrosoft.com

For a Microsoft Entra user that's a Windows account from a federated domain, include the domain in the username (for example, joe@contoso.com):

bcp bcptest out "c:\last\data1.dat" -c -S contoso.database.windows.net -d testdb -G -U joe@contoso.com

If guest users in a Microsoft Entra tenant are part of a group that has database permissions in Azure SQL Database, use the guest user alias (for example, keith0@adventure-works.com).

Related content

Get help

Contribute to SQL documentation

Did you know that you can edit SQL content yourself? If you do so, not only do you help improve our documentation, but you also get credited as a contributor to the page.

For more information, see Edit Microsoft Learn documentation.

  • Last updated on