We want to use AzureSqlServer with ActiveDirectoryMSI authentication as well as token-based authentication and
We are able to execute successfully from VM created in Azure network and added as a member of the Azure AD group.
For that, we have created Contained user by following the link
https://docs.microsoft.com/en-us/azure/active-directory/managed-identities-azure-resources/tutorial-windows-vm-access-sql
And added the VM as part of AzureActiveDirectory
by following this link
com.microsoft.sqlserver.jdbc.SQLServerException: MSI Token failure: Failed to acquire token from MSI Endpoint
And we are able to access the SQL data without providing username and password using both IMDS server and able to retrieve the token using
http://169.254.169.254/metadata/identity/oauth2/token?api-version=2018-02-01&resource=https%3A%2F%2Fdatabase.windows.net%2F
and
ActiveDirectorMSI
URIString jdbc:sqlserver://azuresqlserverNAME:1433;databaseName=DatabaseNAME;Authentication=ActiveDirectoryMsi;
.
But when it comes to access from Onprem Windows/Linux/Mac machine we are not able to access Azure SQL server
Can someone please suggest to me in terms of on-prem what needs to be done so we can access AzureSqlServer with ActiveDirectoryMSI Authentication as well as token-based authentication?
According to my test, if you want to connect Azure SQL on-premise machine, please refer to the following steps
- Create a service principal
az ad sp create-for-rbac -n 'name' --skip-assignment
Add the service principal as Azure SQL database contained user.
Set environment variable. Please set the following variable as the environment variable
AZURE_TENANT_ID: ID of the service principal's tenant. Also called its 'directory' ID.
AZURE_CLIENT_ID: the service principal's client ID
AZURE_CLIENT_SECRET: one of the service principal's client secrets
- SDK
<dependency>
<groupId>com.azure</groupId>
<artifactId>azure-identity</artifactId>
<version>1.0.7</version>
</dependency>
<dependency>
<groupId>com.microsoft.sqlserver</groupId>
<artifactId>mssql-jdbc</artifactId>
<version>8.2.2.jre8</version>
</dependency>
- code
public static void main( String[] args )
{
AccessToken token= GetAccessToken();
SQLServerDataSource ds = new SQLServerDataSource();
ds.setServerName("<>.database.windows.net"); // Replace with your server name.
ds.setDatabaseName("demo"); // Replace with your database name.
ds.setAccessToken(token.getToken());
try (Connection connection = ds.getConnection();
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery("SELECT SUSER_SNAME()")) {
if (rs.next()) {
System.out.println("You have successfully logged on as: " + rs.getString(1));
}
}
}
public static AccessToken GetAccessToken() {
DefaultAzureCredential creds = new DefaultAzureCredentialBuilder()
.build();
TokenRequestContext request = new TokenRequestContext();
System.out.println("444");
request.addScopes("https://database.windows.net//.default");
String token;
AccessToken accesstoken=creds.getToken(request).block();
return accesstoken;
}