Windows Azure Connect Use Case: Web Role / Application Pool Access SQL Server using Windows Authentication

We showed you how to domain join Windows Azure roles to on-premises Active Directory in an earlier post. Once the Azure roles are domain joined, it is easy to enable a very common use case – application pool uses domain credential to access SQL server on-premises.

The steps below assume that you have already configured your on-premises SQL server to use Windows Authentication, and you have set Integrated Security=true in your connection string.

0. First follow the instruction to enable domain join for your Windows Azure roles.

1. Specify the credential that your application pool will use in the .cscfg file.

a) To do that, you would need to define the settings in the .csdef first. Also it is important that you set executionContext to “elevated” because we would need to have sufficient rights to run appcmd.exe to set the identity for the application pool.

 <ConfigurationSettings> <Setting name="AppPoolUserName" /> <Setting name="AppPoolUserPassword" /> </ConfigurationSettings> <Runtime executionContext="elevated" /> 

b) Then specify the value of the credential for your application pool in the .cscfg file. You need to encrypt the user password using a certificate (the same way you did for encrypting user password used for domain join). Here is an example:

 <!-- Specify the user name for the Application Pool --> <Setting name="AppPoolUserName" value="corp4\jason" /> <!-- Insert encrypted password for the user specified above --> <Setting name="AppPoolUserPassword" value="MIIBFwYJKoZIhvcNAQcDoIIBCDCCAQQCAQAxgckwgcYCAQAwLzAbMRkwFwYDVQQDExBNeUVuY3J5cHRpb25DZXJ0AhCTEsiJ0zzrjktvASTLQh7qMA0GCSqGSIb3DQEBAQUABIGABGQW6efUv3fpewvgCcqxqfzJu5gmlUqXPg60aggvDeBYwyPL6xVqZ9DZYiYxbBmHSjJgzrIrLY+rP3EMtV/8G4f6hvyewasWvJgMe2vzwcMGSKqixcIRnCLuLov7zLgCsYylzZ1h4j/SIf0gUBtwC1leW4C07z+KtQb8fdDbi5wwMwYJKoZIhvcNAQcBMBQGCCqGSIb3DQMHBAiyEoiHBP8V8YAQ2PN+j2uY07qpS93N15uQkA==" /> 

2. Set up your application pool to use the credential specified above when the role starts up. This can be done with one line of code in OnStartup() if you include our WAConnectUtils class in your project.

a) Download WAConnectUtils class (WAConnectUtils.cs). We have created this utility class to help you do a few chores including:

  • Detecting if the role’s Connect connection is up
  • Detecting if the role is domain joined
  • Configuring IIS application pool
  • Decrypting password encrypted using a cert

b) Add WAConnectUtils.cs to your web role project.

c) Add necessary references to assemblies.

For Microsoft.Web.Administration, the component path is %windir%\system32\inetsrv\Microsoft.Web.Administration.dll

d) Add one line of code in OnStartup() as show in the highlighted below. ConfigureIISAppPoolAfterDomainJoin waits for the role to be domain joined and then launches appcmd.exe to set IIS application pool to use the credential you specified in the .cscfg file.

 public override bool OnStart()
{
    RoleEnvironment.Changing += RoleEnvironmentChanging;

    try {
        // Use the name of site from your .csdef, in this case it is called "Web" 

WAConnectHelpers.WAConnectUtils.ConfigureIISAppPoolAfterDomainJoin("Web"

 );
    }
    catch (Exception)
    {
        // Catch all exceptions here, else the role will get recycled. // Trace Exception information here }

    return base.OnStart();
}

3. Build, publish and deploy your role to Windows Azure. Once the role is up and domain joined, you can remote desktop into a role instance (using existing domain credential if you wish) and verify that the application pool is set to use the credential specified.

image

4. Now your web roles will be using the specified domain credential to perform windows authentication when accessing SQL server.

 

--Jason Chen