Server Architecture Planning (SQL Server, Windows Authentication, Drive Mappings, Seamless Deployments)

I just wanted to post this brief writeup here of how I went about building out our RSP/RSC infrastructure, hopefully it helps others encountering similar questions.

Environment
Windows environment with a very limited number of Linux servers.
User authentication is done via Active Directory.
SQL Server is our database platform.
Windows server based network shares for document repositories.
RHEL7 (Centos) servers for RStudio Products. (RStudio Pro, RStudio Connect)

Goals
Leverage Active Directory for authentication whenever possible.
Leverage Kerberos ("Windows Authentication") for database access.
End users (viewers & publishers) will not have accounts on the RConnect Server.
Create method for users to be able to "seamlessly" deploy applications (no changing of connection strings on deploy).
No passwords in source code or configuration files.
Allow access to necessary network shares in both the RSP and RSC environments, based on existing permissions.
Allow for "seamless" deployment when network share access is required.

In short we wanted a developer to login to RSP with their windows credentials, have access to the necessary network shares and have access to the necessary databases solely via a data source name (DSN). Upon deployment to RSC we want the user to click publish and have their deployment appear seamless, in that they do not have to modify any access credentials to databases or network shares.

From this point on I am going to assume that you already have successfully configured authentication against AD using PAM (either via the KRB5 or SSSD modules). This is fairly well documented in the RSP documentation. Your server admins should be logging in via this method as well, they should not have passwords stored on the specific server.

Yum Pacakages
kstart - Utility will be leveraged to create and maintain Kerberos tickets for service accounts
cifs-utils - Needed to mount cifs
keyutils - Useful for debugging/verification

Needed for SQL Server connectivity (requires another repository, follow documentation like the following for installation: https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-2017)
msodbcsql17
mssql-tools
unixODBC
unixODBC-devel.x86_64

Required Service Accounts & k5start scripts
You will need one service account that handles mounting of drives. This account should be setup in Active Directory and granted read only access to the top directories that you wish to mount. It only needs read access to the top level directory in that structure. (This account needs to exist on the RSP & the RSC server)
You will need service accounts for running applications in RSC. These accounts can be as granular as you wish, you could simply have one account for all departments, one account per department or one account per application. The increased granularity will allow you to be more specific in what permissions are granted to each account, but also increase the administration overhead. (These accounts need to exist on the RSC Server)

Each service account should have a keytab file created and stored on the appropriate servers under the /etc directory with 640 or 440 permissions. I would suggest creating one keytab per service account to make maintenance simpler.

Each service account then needs to have a k5start init script created. See the attached as an example. This will create a daemon that gets and maintains a Kerberos TGT for the service account on startup. This will allow the service account to have access to the databases and file shares as needed. Make sure to add each kinit job to the init.d jobs.

Drive Mapping Scripts
You will need to create an init.d script to create the required network share mounts. The will open a configuration file and create the mounts in it. If your configurations look like the following, then the drive will be mapped by the service account but any user attempting to access the mount point will authenticate using their own credentials. If they do not have access they will not be able to access anything within the mount. Setup this script on both the RSP and RSC servers and mount the drives to the same location so that any deployed applications will look in the same location.

Database Access
Grant users access to databases as necessary and also grant database access to service accounts as needed. Create the same DSNs on both the RSP and RSC servers under /etc/odbc.ini.

Deploying Applications
The first time a new application is deployed to RSC an administrator must change the application to run as the appropriate service account.

Make sure that you follow the RSP documentation to run the PAM or SSSD module when creating a new RSP Session, this is required in order for a developer to be granted a Kerberos TGT upon logging in and starting/resuming a session.

Script Examples

K5Script

#!/bin/bash
#
# k5start-RStudioEquitiesAppID:  Keeps Kerberos 5 ticket-granting ticket active indefinitely for RStudioEquitiesAppID
# Original Author:               Mark R. Bannister
# Modified By:                   C.Roos for use at NYSTRS.
#
# chkconfig: 345 19 74
# priority must be prior to delayed-mnt
# description:  Keeps Kerberos 5 ticket-granting ticket active indefinitely
#
# processname: /usr/bin/k5start-RStudioEquitiesAppID
# config: /etc/krb5.conf
# pidfile: /var/run/k5start-RStudioEquitiesAppID.pid

# Sanity checks.
[ -f /etc/krb5.conf ] || exit 0
[ -x /usr/bin/k5start ] || exit 0

# Source function library.
. /etc/init.d/functions

K5START_KEYTAB=/etc/RStudioEquitiesAppID.keytab
K5START_PID_FILE=/var/run/k5start-RStudioEquitiesAppID.pid
K5START_MINUTES=30
K5START_OPTIONS=

RETVAL=0

start() {
    echo -n $"Starting k5start-RStudioEquitiesAppID: "
    daemon /usr/bin/k5start HTTP/RStudioEquities.strs.us@STRS.US -f $K5START_KEYTAB -k KEYRING:persistent:500 -bLK$K5START_MINUTES \
                        -p $K5START_PID_FILE $K5START_OPTIONS
    RETVAL=$?
    echo
    [ $RETVAL -eq 0 ] && touch /var/lock/subsys/k5start-RStudioEquitiesAppID
    return $RETVAL
}

stop() {
    echo -n $"Stopping k5start-RStudioEquitiesAppID: "
    killproc -p $K5START_PID_FILE k5start
    RETVAL=$?
    [ $RETVAL -eq 0 ] && rm -f /var/lock/subsys/k5start-RStudioEquitiesAppID
    echo
    return $RETVAL
}

restart() {
    stop
    start
}

# See how we were called.
case "$1" in
    start)
        start
        RETVAL=$?
        ;;
    stop)
        stop
        RETVAL=$?
        ;;
    status)
        status -p $K5START_PID_FILE k5start
        RETVAL=$?
        ;;
    restart)
        restart
        RETVAL=$?
        ;;
    try-restart | condrestart)
        [ -e /var/lock/subsys/k5start-RStudioEquitiesAppID ] && restart
        RETVAL=$?
        ;;
    force-reload | reload)
        echo -n $"Refreshing k5start-RStudioEquitiesAppID ticket cache: "
        killproc /usr/bin/k5start -ALRM
        RETVAL=$?
        echo
        ;;
    *)
        echo $"Usage: $0 {start|stop|status|restart|reload|condrestart}"
        RETVAL=1
        ;;
esac
exit $RETVAL

Drive Mounting

#!/bin/bash
#
# delayed-mnt:      Mounts drives that are dependent on the existence of Kerberos 5 tickets being initialized from the keytab file.
#                   (see: k5start init.d job)
# Author:           Christopher Roos  
#
# chkconfig: 345 99 73
# description:  Mounts drives in the fstab-delayed file which rely on the existence of Kerberos 5 tickets
#

# Source function library.
. /etc/init.d/functions

MOUNT_BIN=/bin/mount
MOUNT_DESC="mount from fstab-delayed"
MOUNT_FSTAB=/etc/fstab-delayed
UMOUNT_BIN=/bin/umount
LOG_FILE=/var/log/delayed-mnt/delayed-mnt.log
TIMESTAMP=`date +"%Y%m%d-%H%m%S"`

# Sanity checks.
[ -x "$MOUNT_BIN" ] || exit 0
[ -x "$UMOUNT_BIN" ] || exit 0
[ -r "$MOUNT_FSTAB" ] || exit 0

RETVAL=0

start() {
    RETVAL=0
    [ -f $LOG_FILE ] && mv $LOG_FILE "$LOG_FILE$TIMESTAMP"
    touch $LOG_FILE
    while IFS='|' read -r fssource fsmount fstype fsoptions; do
          [[ "$fssource" =~ ^#.*$ ]] && continue
          echo "Starting $MOUNT_DESC : -t $fstype -o $fsoptions \"$fssource\" \"$fsmount\"" "mount" >> $LOG_FILE
          $MOUNT_BIN -t $fstype -o $fsoptions "$fssource" "$fsmount"
          echo "Mount \"$fssource\" exit code: $?" >> $LOG_FILE
          [ $? -ne 0 ] && RETVAL=$?
    done < $MOUNT_FSTAB
    return $RETVAL
}

stop() {
    [ -f $LOG_FILE ] || touch $LOG_FILE
    RETVAL=0
    while IFS='|' read -r fssource fsmount fstype fsoptions; do
          [[ "$fssource" =~ ^#.*$ ]] && continue
          echo "Stopping $MOUNT_DESC \"$fssource\" -> \"$fsmount\"" >> $LOG_FILE
          $UMOUNT_BIN "$fsmount"
          echo "Umount \"$fssource\" exit code: $?" >> $LOG_FILE
          [ $? -ne 0 ] && RETVAL=$?
    done < $MOUNT_FSTAB
    return $RETVAL
}

restart() {
    stop
    start
}

# See how we were called.
case "$1" in
    start)
        start
        RETVAL=$?
        ;;
    stop)
        stop
        RETVAL=$?
        ;;
    status)
        cat $LOG_FILE
        RETVAL=$?
        ;;
    restart | try-restart | condrestart | force-reload | reload)
        restart
        RETVAL=$?
        ;;
    *)
        echo $"Usage: $0 {start|stop|status|restart|reload|condrestart}"
        RETVAL=1
        ;;
esac
exit $RETVAL

Drive Mounting Config

#
# /etc/fstab-delayed
# Created by croos on Tue Jul 31 2019
#
# Leveraged by delayed-mnt init.d script.
#
//data/path1|/mnt/EquitiesShared/Path1|cifs|multiuser,sec=krb5,user=<MountServiceID>,cruid=<MountServiceID>,cache=none,file_mode=0444,dir_mode=0444
//data/path2|/mnt/EquitiesShared/Path2|cifs|multiuser,sec=krb5,user=<MountServiceID>,cruid=<MountServiceID>,cache=none,file_mode=0444,dir_mode=0444
//data/parth3|/mnt/EquitiesShared/Path3|cifs|multiuser,sec=krb5,user=<MountServiceID>,cruid=<MountServiceID>,cache=none,file_mode=0444,dir_mode=0444
7 Likes

Thanks for posting! This guide will be super helpful for others who are trying to configure a similar architecture (and might help us improve our own documentation).

1 Like

Sure thing Nick, thanks for the support on getting this setup.

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.