This is documentation for Apprenda 7 and 8.
Documentation for newer version is available at https://new.docs.apprenda.com.

Support for SQL Server Database Windows Authentication (single-tenant .NET guest applications only)

MS SQL Server Authentication Modes

MS SQL Server supports two authentication modes:

  • Windows Authentication mode: authentication is supported ONLY through pre-existing Windows accounts that have been specifically granted permission to access the instance
  • SQL Server and Windows Authentication mode (also known as Mixed mode): authentication is supported BOTH through pre-existing Windows accounts that have been specifically granted permission to access the instance AND through SQL logins that are created in SQL Server

The authentication mode must be set when a SQL Server instance is installed, and can be changed later in SQL Server Management Studio through the Security option in the Server Properties section for the instance:

    Apprenda and SQL Authentication

    Prior to the release of Apprenda Platform 5.5.4, all Platform SQL Server instances were required to use Mixed mode authentication. A SQL login account specified for each SQL Server instance was used for all Platform and guest application creation, and guest application components (such as services and UIs) communicated with their respective databases via connection strings based on SQL logins.

    In order to accommodate company security policies that prefer the Windows Authentication mode to Mixed mode, Apprenda Platform version 5.5.4 introduced limited Windows Authentication support for certain types of applications:

    Table 1: Applications supported for Windows Authentication

    Single- or Multi-tenant? .NET or Java? MS SQL or Oracle? Windows Authentication
    Support
    Single-tenant .NET MS SQL Yes!
    Any Any Oracle No
    Any Java Any No
    Multi-tenant Any Any No

    As of this version of Apprenda, Platform Operators may configure the Platform to use Windows Authentication (via the Security Support Provider Interface, or SSPI) for all single-tenant .NET guest applications that include a SQL Server database tier. When this option is configured by a Platform Operator, service and UI components for these types of applications will communicate with their respective databases via connection strings based on Windows Authentication only.

    Depending on the Platform configuration and options selected by the Platform Operator as indicated in the table below, databases for supported applications will be created using Windows Authentication or using the SQL Server admin account specified for the instance:

    Table 2: Platform MS SQL Server database configuration options

    Database Security Mode
    Custom Property Setting
    SQL Server
    Authentication
    Creds used to create
    Single-tenant DBs
    Creds components use to
    access Single-tenant DBs
    Can host CoreDB/
    Multi-tenant app DBs
    SQL only* Mixed Mode SQL  SQL Yes
    SQL and SSPI Mixed Mode SQL Windows Yes
    SSPI only Windows
    Authentication
    Windows Windows No

    * This is the effective setting for all prior versions of the Apprenda Platform.

    PLEASE NOTE: databases essential for Platform functionality (such as the the Apprenda Core DB) require a SQL instance set to Mixed Mode Authentication. Because of this, all Apprenda Platform instances must contain a SQL instance configured for SQL only OR an instance configured for SQL and SSPI.

    It is recommended that Platforms that use SSPI contain at least one SQL instance configured for SQL only (to host the Core DB and other required Apprenda DBs).  Instances that will support SSPI can then be configured for SSPI only, which will allow those instances to take full advantage of Apprenda's support for Windows authentication (i.e., DBs will be both created with and accessed by Windows Authentication). 

    Prerequisites

    • The domain account under which the Storage Controlling Service runs (by default this is the account specified as the Apprenda Administrator) must be added as a user to SQL Server instances hosting guest application databases and granted the same SQL Server permissions as the SQL Server Admin Account outlined in the Understanding Platform User Accounts and Privileges page.
    • For SQL Server instances that will be set to Windows Authentication only (as opposed to Mixed Mode Authentication): the account under which the SQL Service DB engine runs requires read access to the token-groups-global-and-universal (TGGAU) attribute. This can be accomplished most easily by running said service(s) under a domain account that is granted membership to the Built-in Windows Authorization Access Group. In some domain setups it may also be possible to configure the read access permissions directly.

    Platform Installation/Modification/Uninstall/Upgrade

    • The Apprenda Platform must be installed with *all* SQL Server instances set to Mixed Mode Authentication. Any instances that will use Windows Authentication only must be configured to do so after Platform installation is complete; this is also true for any SQL instances added to the Platform after installation through the Apprenda Installer's Modify workflow.
    • The account specified at installation time as the Apprenda System account will by default be used as the account under which guest application (non-Apprenda) services will run. See below for more information. As of version 7.1, the System Account is no longer used. 
    • The account specified at installation time as the Apprenda Administrator account will by default be used as the account under which the Storage Controlling Services will run. See below for more information.
    • When uninstalling the Apprenda Platform or removing a SQL Server instance, any SQL instances previously set to Windows Authentication must be set back to Mixed Mode; valid SQL credentials must also be provided for the instance via the Infrastructure section of the SOC.
    • Likewise, when upgrading the Apprenda Platform,any SQL instances previously set to Windows Authentication must be set back to Mixed Mode. These instances can be reconfigured to use Windows Authentication mode after the upgrade process is complete.

    Configuration

    Verify Credentials for the Storage Controlling Services

    The Storage Controlling Services is an Apprenda WCF service that interfaces with SQL Server and Oracle to create, delete, and otherwise configure storage for your application's databases. When SSPI is used, the Windows account under which this service runs will need the same permissions as the SQL Server Admin Account (outlined in the Understanding Platform User Accounts and Privileges) on SQL Server instances configured to use SSPI. After Apprenda Platform installation, the Storage Controlling Services are set to use the account specified in the installer as the Apprenda Administrator account. As needed, a different account can be specified for the Storage Controlling Services through the Applications section of the SOC; once the account has been changed in the SOC, all instance sof the Storage Controlling Services must be restarted in order for the change to take effect.

    Database Security Mode Custom Property Configuration

    By default, all SQL instances will use SQL credentials only. The Platform Operator must mark each SQL instance that they have selected to use SSPI as follows:

    • Locate the instance in the Infrastructure section of the SOC under the Database Nodes tab.
    • Click on the instance name, which will open up detailed information about the instance.
    • Click on the View Custom Properties link in the Database Node Details section.
    • Click on the edit icon for the Database Security Mode Property. By default, SQL will be selected. As needed, the values for the instance can be set to SQL only, SSPI only, or (as of 5.5.5) both SQL and SSPI. 

    • Once you have made your selection, click Save. At this point the Platform will confirm that the account under which the Storage Controlling Services is able to log on to the SQL instance with the appropriate permissions (see Understanding Platform User Accounts and Privileges). If this check fails, your changes will not be saved, and you must correct any account assignment or permissions issue before changing the Database Security Mode for the SQL instance.
    • Repeat this process for any additional SQL instances that will use SSPI.

    As a reminder, any SQL instances on which Apprenda Platform Databases (e.g. Core) reside must be set to support SQL authentication (or SQL and SSPI) and cannot be configure to support SSPI only.

    Credentials for Service and UI Components

    Because they will have to communicate with their databases via Windows authentication, all services and UIs for supported applications must be configured to run under valid Windows domain accounts. By default, the account specified at install time as the Apprenda System account will be used as the default account for services (As of version 7.1, the System Account is no longer used). No default account is specified for UIs. This can be addressed by the Platform Operator in one of the following ways:

    • Require developers to specify Windows credentials on a per-component basis (Recommended)
    • Allow developers to specify Windows credentials on a per-component basis
    • Set a default account for User Interfaces (UIs)

    The workflows for both of these options are described in this Platform Operator topic on Specifying User Accounts for Guest Application Components.

    If no valid account is specified (either through a Platform default set by the Platform Operator or per credential by the developer) for a component, workload deployments will fail for that component. This will result in a promotion failure in the case of UIs, and scaling/functionality failures in the case of services.

    PLEASE NOTE: in order for workload deployment to work for components where the database uses Windows Authentication, accounts for components must specify the domain name in NetBIOS (as opposed to FQDN) format. 

    Platform Registry Settings

    The final Configuration step is to set the DB.EnforceWindowsAuthentication setting to a value of True in the Platform Registry Settings section of the SOC. This setting acts as a switch that will then enforce Windows Authentication for all newly deployed single-tenant .NET applications that use SQL Server.

    Operation

    Once the Configuration steps above have been completed, Windows Authentication will be enforced for all newly deployed single-tenant .NET applications that use SQL Server. It should be noted that this enforcement is automatic and Platform-wide; developers will not have the option to use SQL authentication.

    Information on how Apprenda deploys guest application databases can by found in the How Apprenda Deploys your Application: Data Tier section of our documentation; a section on databases that use Windows Authentication has been included.

    As needed, developers can obtain DB connection string information for all of their applications (including those that use Windows Authentication) through the Cloud Control section of the Developer Portal.