Note:

If the Oracle Clinical database server is installed on a Windows computer, SAS must also be installed on a Windows computer, even if it is a separate machine.

The SAS/ACCESS Interface to Oracle requires Oracle SQL*NET on the computer with the SAS software installation. For this statistics application to function with Oracle Clinical Data Extract, you must install these SAS components:

Base SAS

SAS/ACCESS

7.1 Set Up SAS Data Extract

Users need access to the opapps account to run Oracle Clinical SAS Data Extract jobs.

7.1.1 Give Users Access to the opapps Account

The same setup is required for SAS users in Oracle Clinical as for PSUB users; see Section 6.1.2, "Enable Users to Submit PSUB Jobs" .

7.1.2 Add the opapps Account to the oclsascr User Group

Add the opapps user to the oclsascr user group. It is the only user required to be in this group.

See the Oracle Clinical Administrator's Guide for more information.

7.1.3 Give opapps Access to the Directory Specified in the RXC_SAS_VIEW Environment Variable

See the Oracle Clinical Administrator's Guide for more information.

Indicate your choice in the OCL_STATE local reference codelist along with other SAS-related settings. By default the value is set to Oracle Wallet.

See the Oracle Clinical Administrator's Guide for more information.

7.2.1 Modify SAS 9.4 and opa_settings on UNIX

Oracle Clinical supports SAS 9.4. You can validate your Oracle Clinical installation, and then upgrade to SAS 9.4 later. The SAS/ACCESS Interface to Oracle requires Oracle SQL*NET on the computer with the SAS software installation.

Follow these instructions on the SAS server—whether SAS is on the same computer or a different one from Oracle Clinical.

7.2.1.1 Prepare the SAS Template File

Make the following modifications to the SAS template file:

Copy the SAS template file from OPA_HOME /oc/52/tools to OPA_HOME /bin.

Open the SAS file in a text editor and find this text string:

<path_to_SAS_executable>

Replace the string with the actual SAS executable path; for example:

/root /SAS94/SASFoundation/9.4/sas $*

Save your work.

7.2.1.2 Additional Modifications for Oracle Solaris

This section describes Oracle Solaris-specific installation issues.

LD_LIBRARY_PATH

On Oracle Solaris, in previous releases of Oracle Clinical and versions of SAS before 8.2, you had to configure a script in OPA_HOME /bin that intercepted the SAS command to set some additional environment variables. The script then called the actual SAS executable. In Oracle Clinical, the SAS script file includes a step that points to the 32-bit libraries.

SAS/ACCESS Error with Oracle Database 11g

Using Oracle Database 11g may cause an error with SAS/ACCESS to Oracle. When using SAS/ACCESS to Oracle's SQL Pass Through Facility or Libname engine, you may receive an error similar to this one:

error: ld.so.1 sas: fatal: libclntsh.so.9.0:
open failed: no such file or directory

To work around this problem:

Create a link from libclntsh.so.11.0 to libclntsh.so.9.0.

ln -s libclntsh.so.12.0 libclntsh.so.9.0

7.2.2 Install SAS on the Same UNIX Computer as Oracle Clinical

Oracle recommends installing SAS on the same server computer as the Oracle Clinical database server installation, following instructions in this section.

7.2.2.1 Set REMOTE_OS_AUTHENT to FALSE

Verify that the REMOTE_OS_AUTHENT initialization parameter is set to FALSE for the Oracle Clinical database instance in the init.ora file.

REMOTE_OS_AUTHENT=FALSE

See Section 3.4, "Set Initialization Parameters" for more information.

7.2.2.2 Set Up a SAS Connection

You can set up a SAS connection to the Oracle database in two ways—using Oracle Wallet or SAS encryption. The Oracle Clinical Installer sets the SAS_CONNECTION value in the OCL_STATE reference codelist to ORACLE_WALLET.

7.2.2.2.1 Oracle Wallet

This is the default option. The SAS connection is set up automatically.

7.2.2.2.2 SAS Encryption

Manually set SAS_ENCRYPTION as the SAS_CONNECTION long value in the OCL_STATE local reference codelist. In addition:

Log in to the database as system user and create the sas_proxy_user database account:

create user sas_proxy_user identified by <password>;

Grant proxy connection for each database user account who needs to submit SAS Data Extract jobs:

alter user <oc user> grant connection through sas_proxy_user

Run the script ocl_grant_revoke_sas_proxy_user.sql in the install directory to grant or revoke user connections through proxy account sas_proxy_user

Log in to the PSUB server as RXC_SAS_VIEW owner and set the environment:

C shell:

opa_setup database_name code_environment

Bourne shell:

p1 = database_name p2 = code_environment . opa_setup

Create a temporary SAS file named pwd.sas to contain the password for the sas_proxy_user account:

filename pwfile 'sas_proxy_password_encoded_file'; proc pwencode in=' password ' out=pwfile method=sas002;

Run the file created in Step 4 to encrypt the file. This creates the sas_proxy_password_encoded_file which stores the encrypted password of the sas_proxy_user db user (where pwd.sas is the name of the temporary file you created):

In UNIX:

sas pwd.sas

In Windows:

sas pwd.sas -sysin

Remove the file created in Step 4.

Run the SAS files.

7.2.3 Install SAS on a Different UNIX Computer from Oracle Clinical

Oracle recommends installing SAS on the same UNIX server computer as the Oracle Clinical database server installation.

If you choose to install SAS on a UNIX server computer different from that of the Oracle Clinical database server installation, it must be on the same intranet and you need to set up a connection to SAS.

7.2.3.1 Establish the Connection to SAS on a Different Computer

To set up SAS on a different UNIX server computer:

Note:

The following procedure uses secure shell (ssh) to establish the connection to SAS.

7.2.3.1.1 NFS

Use Network File System (NFS) protocol to make the directory on the Oracle Clinical server pointed to by the $RXC_USER /sas_view visible to the SAS server.

Export this directory with write privileges because the SAS scripts generated by Oracle Clinical produce SAS view descriptors that are created in this directory tree.

Note:

By default the $RXC_USER environment variable is the opapps home directory.

7.2.3.1.2 Create opapps on the SAS Server

Create the opapps UNIX user account on the SAS server.

Create a group for opapps and put it in the group.

Link /etc/group with /etc/logingroup on the SAS server if it is not the primary group for opapps.

Verify that the REMOTE_OS_AUTHENT initialization parameter is set to FALSE for the Oracle Clinical database instance in the init.ora file:

REMOTE_OS_AUTHENT=FALSE

See Section 3.4, "Set Initialization Parameters" for more information.

7.2.3.1.4 Create a Shell Script

Create a shell script that forces a "SAS" invocation on the Oracle Clinical database server to run as a remote shell on the SAS server that invokes the SAS engine, passing it the name of the SAS file:

Create the shell script on the Oracle Clinical database server in a publicly visible directory, such as opapps/bin.

Name the script sas .

Set the protection mode to 755 .

Insert code lines into the SAS file.

#!/bin/sh RXC_LOG1= log_path_on_the_SAS_server SASDIR=`dirname $3` FILENAME=`basename $2` LOGNAME=$RXC_LOG1$FILENAME ssh server_name /bin/sh -c ". .profile;setenv TNS_ADMIN $HOME ;setenv ORACLE_HOME oracle_home_on_sas_server ;cd $SASDIR ; path_to_sas_script_on_SAS_server -log $LOGNAME $3 $4 $5 $6 "

Where:

RXC_LOG1 is the path of the log directory in the SAS server

SASDIR is the directory RXC_SAS_VIEW where SAS view is created and folder is NFS mounted; comes as input

FILENAME is the SAS log file name; comes as input

server_name is the SAS server connected through ssh

path_to_sas_script_on_SAS_server is the sas file created in the OPA_BIN directory on the SAS server

For example:

#!/bin/sh RXC_LOG1=/pharm/home/opapps/log SASDIR=`dirname $3` FILENAME=`basename $2` LOGNAME=$RXC_LOG1$FILENAME ssh opapps@server_name /bin/sh -c ". .profile;setenv TNS_ADMIN $HOME ;setenv ORACLE_HOME /u01/app/oracle/product/12.0.0.1;cd $SASDIR ; /home/opapps/bin/sas -log $LOGNAME $3 $4 $5 $6 "

7.2.3.2 Set Up SAS Security on a Different Computer

You can set up a SAS connection to the Oracle database in two ways—using Oracle Wallet or SAS encryption. The Oracle Clinical Installer sets the SAS_CONNECTION value in the OCL_STATE reference codelist to ORACLE_WALLET.

7.2.3.2.1 Oracle Wallet

This is the default option. You do not need to change the OCL_STATE reference codelist SAS_CONNECTION value, but you must enter the SAS server as the REMOTE_SAS_SERV value in OCL_STATE. See the Oracle Clinical Administrator's Guide for information.

And, on the SAS server:

Create an opapps account in the same way you did on database server with default shell C Shell. See Section 4.1.7.1, "Complete the Setup of the opapps Account" .

Log on as opapps.

Set the environment variables in .cshrc so you can connect to the database using SQL*Plus.

Set up the Oracle Wallet on the SAS server. You may use the same Wallet password that you used during Oracle Clinical installation or a different one:

mkstore -wrl wallet_location -create -nologo

Example wallet location: /home/opapps/wallet

Enter your password, then enter it again.

Add OCPSUB credentials:

mkstore -wrl wallet_location -createCredential db_connect_string OCPSUB

Enter information as follows at the prompts:

Enter your secret password : Enter the OCPSUB password.

Re-enter your secret password : Re-enter the OCPSUB password.

Enter wallet password : Enter the Wallet password created above.

Create sqlnet.ora in opapps home. Specify the Wallet path. For example:

WALLET_LOCATION=(SOURCE =(METHOD = FILE)(METHOD_DATA =(DIRECTORY = /pharm/home/opapps/520000/wallet)))SQLNET.WALLET_OVERRIDE = TRUE

where /pharm/home/opapps/520000/wallet is the wallet path

In the opapps home, add the following in .cshrc:

setenv TNS_ADMIN $HOME

Test the Wallet connection:

Open another telnet/putty session of SAS server and log in as opapps.

Try connecting. It should not require specifying a password.

sqlplus /@db_connect_string sql> show user

You should see the OCPSUB user.

7.2.3.3 Configure Private and Public Keys for Using SSH with SAS

If you are installing SAS on a different server on an intranet, do the following to establish an SSH connection from the PSUB server to the SAS server.

Oracle Clinical DB Server

Perform the following tasks on the Oracle Clinical UNIX database server computer:

Log in to the Oracle Clinical UNIX database server computer as the opapps user.

Use ssh-keygen to create a password-less set of identity keys:

ssh-keygen -t rsa -N ''

The system prompts for the file into which you want to save the set of identity keys.

Press Return to accept the default location. This process creates two files in the user's home directory:

~/.ssh/id_rsa This file contains the private key that represents your identity on that particular machine. Note that the private key is neither world nor group readable. You should never transfer the private key from the machine or change its modes.

~/.ssh/id_rsa.pub This file contains the public key, which is world readable. The ssh program and other programs can use the public key to encrypt messages that only you can decrypt using the private key. The -N ' ' argument to the ssh-keygen command specifies that no passwords are associated with the public keys.

Transport the file id_rsa.pub to a location on the SAS Server (for example, /tmp ) using a secure method as defined by the policies of your organization.

SAS Server

Perform the following tasks on the SAS Server computer:

Log in to the SAS Server computer as opapps.

In the home directory Create the .ssh directory if it does not exist, and set the permission to 700:

mkdir .ssh chmod 700 .ssh cd ~/.ssh

Append the contents of the id_rsa.pub file in the /tmp directory to the authorized_keys file in the GUEST1_HOME/.ssh directory. For example:

cat /tmp/id_rsa.pub >> authorized_keys

Change the permission of the authorized_keys file to 600:

chmod 600 authorized_keys

Verify SSH

To test the ssh setup from the Oracle Clinical Database server:

Log in as opapps.

Enter:

ssh sas_servername

It should log you in to the Sas server without the password.

7.3.1 Modify the opasettings.bat file

To use SAS 9.4 with Oracle Clinical on a Windows platform:

Navigate to the following directory:

OPA_HOME \bin

Open the opa_settings.bat file.

Verify that the value of the SASORA environment variable is commented in opa_settings.bat (is preceded by REM ).

REM set SASORA=V9

7.3.2 Install SAS on the Same Windows Server Computer

Oracle recommends installing SAS on the same Windows server computer as the Oracle Clinical database server installation.

If you choose to install SAS on a Windows server computer different from that of the Oracle Clinical database server installation, you must execute SAS jobs manually. However that configuration is also possible; see Section 7.3.3, "Install SAS on a Different Windows Server Computer" .

7.3.2.1 Set REMOTE_OS_AUTHENT to FALSE

Verify that the REMOTE_OS_AUTHENT initialization parameter is set to FALSE for the Oracle Clinical database instance in the init.ora file.

See Section 3.4, "Set Initialization Parameters" for more information.

In the command prompt, enter: path

The system lists the set of directories in which the system will search for an executable file (in this case, the SAS executable). Among others, the following path must be present (in this example, SAS 9.4 is installed in C:\Program Files):

C:\Program Files\ SAS94\SharedFiles\Formats(32);C:\Program Files\SAS94\SASFoundation\9.4;

If not present:

Navigate to the Control Panel, then System and Security , then System .

Select the Advanced tab, then Environment Variables .

In the System Variable sections select PATH and click the Edit button.

In the Variable Value box, append the correct value for your location, for example:

;C:\Program Files\ SAS94\SharedFiles\Formats(32);C:\Program Files\SAS94\SASFoundation\9.4;

to the existing value and click OK .

Click OK in the Environment Variables dialog box.

Click OK in the System Properties dialog box.

7.3.2.3 Edit the SAS Config File and Grant Permissions

To be able to run SAS on the SAS server of a Windows computer, the user must have full control directory permissions to the following folders located in the SAS software folder:

SASWORK

SASUSER

To accomplish this:

Log in to the Oracle Clinical database server as an a dministrator.

Using Windows Explorer, navigate to the following location (in this example SAS 9.4 is installed in C:\Program Files):

C:\Program Files\SAS94\SASFoundation\9.4\nls\en

Open SASV9.cfg file in a text editor.

Comment the existing lines about -SASUSER and -WORK and add new ones, substituting any directory you choose for D:\sasuser and D:\sastemp, so that the section looks like this:

/* Setup the default SAS System user profile folder */ /*-SASUSER "?CSIDL_PERSONAL\My SAS Files\9.2"*/ -SASUSER "D:\sasuser" /* Setup the default SAS System user work folder */ /*-WORK "!TEMP\SAS Temporary Files"*/ -WORK "D:\sastemp"

Save the file.

Create directories D:\sasuser and D:\sastemp or your preferred equivalents. (In Windows Explorer, right-click and then select New , then Folder .)

Give full control directory permissions on these directories to the oclsascr group.

7.3.3 Install SAS on a Different Windows Server Computer

You can also install SAS on a computer other than the Oracle Clinical database. However, if you do you must execute SAS jobs manually after their generation from Oracle Clinical. See the Data Extract chapter in the Oracle Clinical Conducting a Study manual for more information.

7.3.3.1 Set REMOTE_OS_AUTHENT to TRUE

Verify that the REMOTE_OS_AUTHENT initialization parameter is set to TRUE for the Oracle Clinical database instance in the init.ora file.

See Section 3.4, "Set Initialization Parameters" for more information.

7.3.3.2 Enable Executing Data Extract PSUB Jobs

To enable executing data extract PSUB jobs:

Create a local account on the computer with the PSUB service. Note the password you create for this account. You must specify the same password when you create the SAS account in the next step.

Create the oclsascr local group on the computer with the SAS application.

Enroll the user in the oclsascr group.

Set up RXC_SAS_VIEW:

Using Universal Naming Conventions, define the RXC_SAS_VIEW environment variable in the opa_settings.bat file. For example:

\\ server_name \ sas_view \ database_name

Create a folder on the computer with the SAS application you defined in the RXC_SAS_VIEW environment variable.

Give full control share permissions to oclsascr on the folder you defined in the RXC_SAS_VIEW environment variable.

Give full control directory permissions to oclsascr on the folder you defined in the RXC_SAS_VIEW environment variable.

7.3.3.3 Edit the SAS Config File and Grant Permissions

To be able to run SAS on the SAS server of a Windows computer, the user must have full control directory permissions to the following folders located in the SAS software folder:

SASWORK

SASUSER

To accomplish this:

Log in to the server as Administrator.

Using Windows Explorer, navigate to the following location (in this example SAS 9.4 is installed in C:\Program Files):

C:\Program Files\SAS94\SASFoundation\9.4\nls\en

Open SASV9.cfg file in a text editor.

Comment the existing lines about -SASUSER and -WORK and add new ones, substituting any directory you choose for D:\sasuser and D:\sastemp, so that the section looks like this:

/* Setup the default SAS System user profile folder */ /*-SASUSER "?CSIDL_PERSONAL\My SAS Files\9.3"*/ -SASUSER "D:\sasuser" /* Setup the default SAS System user work folder */ /*-WORK "!TEMP\SAS Temporary Files"*/ -WORK "D:\sastemp"

Save the file.

Create directories D:\sasuser and D:\sastemp or your preferred equivalents. (In Windows Explorer, right-click and then select New , then Folder .)

Give full control directory permissions on these directories to the oclsascr group.