SAS9 Administration

Managing Database Credentials Across Multiple Environments (DEV/TEST/PROD)

Simon 24/08/2020 4 vues

When deploying SAS© projects from a development environment to an integration or production environment, managing database connections (DB2, Oracle, SQL Server) often becomes a headache.

The Problem: You have defined a library in the SAS© Management Console. In the physical storage tab, you have entered the username and password for the development database. When you deploy your jobs or .sas© files to the integration environment, the connection fails because the database credentials are different there. You are then forced to modify the metadata or regenerate the code with each deployment.

The Solution: Abstraction with Macro Variables

Rather than hardcoding credentials in the table or library metadata, the best practice is to use dynamic references that will be resolved at runtime (runtime).

Here is the two-step process to make your deployments seamless:

1. Metadata Configuration (SMC)

In the SAS© Management Console, at the level of your library definition (or in the database properties):

  • Do not enter the actual username.

  • Instead, enter the name of a macro variable, for example &DBUSER and &DBPASS.

  • Make sure to enter these values literally (with the &).

During the library assignment, SAS© will not see the password yet, but the reference to the variable.

Note :
Environment Configuration (Autoexec)
The actual value of these variables must be defined at the server level, specifically for each environment. The best place for this is the server's autoexec.sas© file (or configuration file).

On the Development server: The autoexec contains:
1%let DBUSER = user_dev;
2%let DBPASS = pass_dev;
Note :
On the Integration server: The autoexec contains:
1%let DBUSER = user_int;
2%let DBPASS = pass_int;
Note :
Thus, the same deployed code will work everywhere without modification, as it will fetch the 'local' value defined on the current machine.

Security: Don't Forget Encoding
Storing passwords in clear text in an autoexec.sas© file presents a security risk. It is strongly recommended to use the PROC PWENCODE procedure to hide this information.

Example:
1/* Générer un mot de passe encodé */
2PROC PWENCODE in='MonVraiMotDePasse'; RUN;
3 
4/* Résultat dans la log : {SAS002}123456... */
Note :
You can then use this encoded string in your autoexec:

SAS©
1%let DBPASS = {SAS002}123456... ;
SAS© will automatically interpret and decode this value when connecting to the database.
Retour à la liste des articles
Références & Docs
SAS Management Console SQL Server autoexec.sas Work
Copyright © WeAreCAS 2014-2026
SAS et tous les autres noms de produits ou de services de SAS Institute Inc. sont des marques déposées ou des marques de commerce de SAS Institute Inc. aux États-Unis et dans d'autres pays. ® indique un enregistrement aux États-Unis. WeAreCAS est un site communautaire indépendant et n'est pas affilié à SAS Institute Inc.