onprem machines to Azure Active Directory so we ca

Question

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?

Answer 1

According to my test, if you want to connect Azure SQL on-premise machine, please refer to the following steps

1. Create a service principal

az ad sp create-for-rbac -n 'name' --skip-assignment

2. Add the service principal as Azure SQL database contained user.

3. 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

4. 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>

5. 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;
                
                
        
        
    }