Integrate Azure SQL Database with Service Connector
Artikkel
This page shows supported authentication methods and clients, and shows sample code you can use to connect compute services to Azure SQL Database using Service Connector. You might still be able to connect to Azure SQL Database using other methods. This page also shows default environment variable names and values you get when you create the service connection.
Supported compute services
Service Connector can be used to connect the following compute services to Azure SQL Database:
Azure App Service
Azure Container Apps
Azure Functions
Azure Kubernetes Service (AKS)
Azure Spring Apps
Supported authentication types and clients
The table below shows which combinations of authentication methods and clients are supported for connecting your compute service to Azure SQL Database using Service Connector. A “Yes” indicates that the combination is supported, while a “No” indicates that it is not supported.
Client type
System-assigned managed identity
User-assigned managed identity
Secret/connection string
Service principal
.NET
Yes
Yes
Yes
Yes
Go
No
No
Yes
No
Java
Yes
Yes
Yes
Yes
Java - Spring Boot
Yes
Yes
Yes
Yes
Node.js
Yes
Yes
Yes
Yes
PHP
No
No
Yes
No
Python
Yes
Yes
Yes
Yes
Python - Django
No
No
Yes
No
Ruby
No
No
Yes
No
None
Yes
Yes
Yes
Yes
This table indicates that the Secret/connection string method is supported for all client types. The System-assigned managed identity, User-assigned managed identity, and Service principal methods are supported for .NET, Java, Java - Spring Boot, Node.js, Python, and None client types. These methods are not supported for Go, PHP, Django, and Ruby client types.
Note
System-assigned managed identity,User-assigned managed identity and Service principal are only supported on Azure CLI.
Default environment variable names or application properties and sample code
Use the connection details below to connect compute services to Azure SQL Database. For each example below, replace the placeholder texts <sql-server>, <sql-database>, <sql-username>, and <sql-password> with your own server name, database name, user ID and password. For more information about naming conventions, check the Service Connector internals article.
Get the Azure SQL Database connection string from the environment variable added by Service Connector.
using Microsoft.Data.SqlClient;
string connectionString =
Environment.GetEnvironmentVariable("AZURE_SQL_CONNECTIONSTRING")!;
using var connection = new SqlConnection(connectionString);
connection.Open();
Get the Azure SQL Database connection string from the environment variable added by Service Connector.
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import com.microsoft.sqlserver.jdbc.SQLServerDataSource;
public class Main {
public static void main(String[] args) {
// AZURE_SQL_CONNECTIONSTRING should be one of the following:
// For system-assigned managed identity: "jdbc:sqlserver://{SQLName}.database.windows.net:1433;databaseName={SQLDbName};authentication=ActiveDirectoryMSI;"
// For user-assigned managed identity: "jdbc:sqlserver://{SQLName}.database.windows.net:1433;databaseName={SQLDbName};msiClientId={UserAssignedMiClientId};authentication=ActiveDirectoryMSI;"
// For service principal: "jdbc:sqlserver://{SQLName}.database.windows.net:1433;databaseName={SQLDbName};user={ServicePrincipalClientId};password={spSecret};authentication=ActiveDirectoryServicePrincipal;"
String connectionString = System.getenv("AZURE_SQL_CONNECTIONSTRING");
SQLServerDataSource ds = new SQLServerDataSource();
ds.setURL(connectionString);
try (Connection connection = ds.getConnection()) {
System.out.println("Connected successfully.");
} catch (SQLException e) {
e.printStackTrace();
}
}
}
For a Spring application, if you create a connection with option --client-type springboot, Service Connector sets the properties spring.datasource.url with value format jdbc:sqlserver://<sql-server>.database.windows.net:1433;databaseName=<sql-db>;authentication=ActiveDirectoryMSI; to Azure Spring Apps.
Get the Azure SQL Database connection configurations from the environment variable added by Service Connector. When using the code below, uncomment the part of the code snippet for the authentication type you want to use. If you are using Azure Container Apps as compute service or the connection string in the code snippet doesn't work, refer to Migrate a Python application to use passwordless connections with Azure SQL Database to connect to Azure SQL Database using an access token.
import os
import pyodbc
server = os.getenv('AZURE_SQL_SERVER')
port = os.getenv('AZURE_SQL_PORT')
database = os.getenv('AZURE_SQL_DATABASE')
authentication = os.getenv('AZURE_SQL_AUTHENTICATION')
# Uncomment the following lines corresponding to the authentication type you want to use.
# For system-assigned managed identity.
# connString = f'Driver={{ODBC Driver 18 for SQL Server}};Server=tcp:{server},{port};Database={database};Authentication={authentication};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30'
# For user-assigned managed identity.
# clientID = os.getenv('AZURE_SQL_USER')
# connString = f'Driver={{ODBC Driver 18 for SQL Server}};Server=tcp:{server},{port};Database={database};UID={clientID};Authentication={authentication};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30'
# For service principal.
# user = os.getenv('AZURE_SQL_USER')
# password = os.getenv('AZURE_SQL_PASSWORD')
# connString = f'Driver={{ODBC Driver 18 for SQL Server}};Server=tcp:{server},{port};Database={database};UID={user};PWD={password};Authentication={authentication};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30'
conn = pyodbc.connect(connString)
Install dependencies.
npm install mssql
Get the Azure SQL Database connection configurations from the environment variables added by Service Connector. When using the code below, uncomment the part of the code snippet for the authentication type you want to use.
For other languages, use the connection properties that Service Connector sets to the environment variables to connect the database. For environment variable details, see Integrate Azure SQL Database with Service Connector.
Data Source=<sql-server>.database.windows.net,1433;Initial Catalog=<sql-database>;User ID=<identity-client-ID>;Authentication=ActiveDirectoryManagedIdentity
Get the Azure SQL Database connection string from the environment variable added by Service Connector.
using Microsoft.Data.SqlClient;
string connectionString =
Environment.GetEnvironmentVariable("AZURE_SQL_CONNECTIONSTRING")!;
using var connection = new SqlConnection(connectionString);
connection.Open();
Get the Azure SQL Database connection string from the environment variable added by Service Connector.
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import com.microsoft.sqlserver.jdbc.SQLServerDataSource;
public class Main {
public static void main(String[] args) {
// AZURE_SQL_CONNECTIONSTRING should be one of the following:
// For system-assigned managed identity: "jdbc:sqlserver://{SQLName}.database.windows.net:1433;databaseName={SQLDbName};authentication=ActiveDirectoryMSI;"
// For user-assigned managed identity: "jdbc:sqlserver://{SQLName}.database.windows.net:1433;databaseName={SQLDbName};msiClientId={UserAssignedMiClientId};authentication=ActiveDirectoryMSI;"
// For service principal: "jdbc:sqlserver://{SQLName}.database.windows.net:1433;databaseName={SQLDbName};user={ServicePrincipalClientId};password={spSecret};authentication=ActiveDirectoryServicePrincipal;"
String connectionString = System.getenv("AZURE_SQL_CONNECTIONSTRING");
SQLServerDataSource ds = new SQLServerDataSource();
ds.setURL(connectionString);
try (Connection connection = ds.getConnection()) {
System.out.println("Connected successfully.");
} catch (SQLException e) {
e.printStackTrace();
}
}
}
For a Spring application, if you create a connection with option --client-type springboot, Service Connector sets the properties spring.datasource.url with value format jdbc:sqlserver://<sql-server>.database.windows.net:1433;databaseName=<sql-db>;authentication=ActiveDirectoryMSI; to Azure Spring Apps.
Get the Azure SQL Database connection configurations from the environment variable added by Service Connector. When using the code below, uncomment the part of the code snippet for the authentication type you want to use. If you are using Azure Container Apps as compute service or the connection string in the code snippet doesn't work, refer to Migrate a Python application to use passwordless connections with Azure SQL Database to connect to Azure SQL Database using an access token.
import os
import pyodbc
server = os.getenv('AZURE_SQL_SERVER')
port = os.getenv('AZURE_SQL_PORT')
database = os.getenv('AZURE_SQL_DATABASE')
authentication = os.getenv('AZURE_SQL_AUTHENTICATION')
# Uncomment the following lines corresponding to the authentication type you want to use.
# For system-assigned managed identity.
# connString = f'Driver={{ODBC Driver 18 for SQL Server}};Server=tcp:{server},{port};Database={database};Authentication={authentication};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30'
# For user-assigned managed identity.
# clientID = os.getenv('AZURE_SQL_USER')
# connString = f'Driver={{ODBC Driver 18 for SQL Server}};Server=tcp:{server},{port};Database={database};UID={clientID};Authentication={authentication};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30'
# For service principal.
# user = os.getenv('AZURE_SQL_USER')
# password = os.getenv('AZURE_SQL_PASSWORD')
# connString = f'Driver={{ODBC Driver 18 for SQL Server}};Server=tcp:{server},{port};Database={database};UID={user};PWD={password};Authentication={authentication};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30'
conn = pyodbc.connect(connString)
Install dependencies.
npm install mssql
Get the Azure SQL Database connection configurations from the environment variables added by Service Connector. When using the code below, uncomment the part of the code snippet for the authentication type you want to use.
For other languages, use the connection properties that Service Connector sets to the environment variables to connect the database. For environment variable details, see Integrate Azure SQL Database with Service Connector.
Microsoft recommends that you use the most secure authentication flow available. The authentication flow described in this procedure requires a very high degree of trust in the application, and carries risks that are not present in other flows. You should only use this flow when other more secure flows, such as managed identities, aren't viable.
Get the Azure SQL Database connection string from the environment variable added by Service Connector.
using Microsoft.Data.SqlClient;
string connectionString =
Environment.GetEnvironmentVariable("AZURE_SQL_CONNECTIONSTRING")!;
using var connection = new SqlConnection(connectionString);
connection.Open();
Add the following dependencies in your pom.xml file:
For other languages, use the connection properties that Service Connector sets to the environment variables to connect the database. For environment variable details, see Integrate Azure SQL Database with Service Connector.
Data Source=<sql-server>.database.windows.net,1433;Initial Catalog=<sql-database>;User ID=<client-Id>;Password=<client-secret>;Authentication=ActiveDirectoryServicePrincipal
Get the Azure SQL Database connection string from the environment variable added by Service Connector.
using Microsoft.Data.SqlClient;
string connectionString =
Environment.GetEnvironmentVariable("AZURE_SQL_CONNECTIONSTRING")!;
using var connection = new SqlConnection(connectionString);
connection.Open();
Get the Azure SQL Database connection string from the environment variable added by Service Connector.
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import com.microsoft.sqlserver.jdbc.SQLServerDataSource;
public class Main {
public static void main(String[] args) {
// AZURE_SQL_CONNECTIONSTRING should be one of the following:
// For system-assigned managed identity: "jdbc:sqlserver://{SQLName}.database.windows.net:1433;databaseName={SQLDbName};authentication=ActiveDirectoryMSI;"
// For user-assigned managed identity: "jdbc:sqlserver://{SQLName}.database.windows.net:1433;databaseName={SQLDbName};msiClientId={UserAssignedMiClientId};authentication=ActiveDirectoryMSI;"
// For service principal: "jdbc:sqlserver://{SQLName}.database.windows.net:1433;databaseName={SQLDbName};user={ServicePrincipalClientId};password={spSecret};authentication=ActiveDirectoryServicePrincipal;"
String connectionString = System.getenv("AZURE_SQL_CONNECTIONSTRING");
SQLServerDataSource ds = new SQLServerDataSource();
ds.setURL(connectionString);
try (Connection connection = ds.getConnection()) {
System.out.println("Connected successfully.");
} catch (SQLException e) {
e.printStackTrace();
}
}
}
For a Spring application, if you create a connection with option --client-type springboot, Service Connector sets the properties spring.datasource.url with value format jdbc:sqlserver://<sql-server>.database.windows.net:1433;databaseName=<sql-db>;authentication=ActiveDirectoryMSI; to Azure Spring Apps.
Get the Azure SQL Database connection configurations from the environment variable added by Service Connector. When using the code below, uncomment the part of the code snippet for the authentication type you want to use. If you are using Azure Container Apps as compute service or the connection string in the code snippet doesn't work, refer to Migrate a Python application to use passwordless connections with Azure SQL Database to connect to Azure SQL Database using an access token.
import os
import pyodbc
server = os.getenv('AZURE_SQL_SERVER')
port = os.getenv('AZURE_SQL_PORT')
database = os.getenv('AZURE_SQL_DATABASE')
authentication = os.getenv('AZURE_SQL_AUTHENTICATION')
# Uncomment the following lines corresponding to the authentication type you want to use.
# For system-assigned managed identity.
# connString = f'Driver={{ODBC Driver 18 for SQL Server}};Server=tcp:{server},{port};Database={database};Authentication={authentication};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30'
# For user-assigned managed identity.
# clientID = os.getenv('AZURE_SQL_USER')
# connString = f'Driver={{ODBC Driver 18 for SQL Server}};Server=tcp:{server},{port};Database={database};UID={clientID};Authentication={authentication};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30'
# For service principal.
# user = os.getenv('AZURE_SQL_USER')
# password = os.getenv('AZURE_SQL_PASSWORD')
# connString = f'Driver={{ODBC Driver 18 for SQL Server}};Server=tcp:{server},{port};Database={database};UID={user};PWD={password};Authentication={authentication};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30'
conn = pyodbc.connect(connString)
Install dependencies.
npm install mssql
Get the Azure SQL Database connection configurations from the environment variables added by Service Connector. When using the code below, uncomment the part of the code snippet for the authentication type you want to use.
For other languages, use the connection properties that Service Connector sets to the environment variables to connect the database. For environment variable details, see Integrate Azure SQL Database with Service Connector.