Best Practices

 

Handling Usernames and Passwords
You should devise a plan now for how you deal with user ids and passwords required for connecting to the EDW and make sure all your applications adhere to it.

    • create an "include" file with the connection string needed to connect to the EDW. 
      For example: connect to oracle as edw (user=xxxxxx orapw=xxxxx path='dsprod01')

      Whenever your programs need to connect to the EDW, include this file instead of hard-coding our password and login.  Store file in a directory with read permission limited to the original programmer. This ensures that the program can be run only by the original programmer unless a new user inserts his/her own connection string or his/her own include fileref in place of the original include file.
    • use a SAS library approach
      The library approach has some advantages and disadvantages to the "connect to" approach. ("Connect to" is faster, but libraries can be accessed in DATA steps as well as PROC SQL steps.) For those who don't know SAS, or are just starting to,
      a "library" is simply an alias or reference for a file location. Our technical gurus inserted script into the SAS startup file, which calls macros (such as "include") that prompt the user to log into our main data sources (IRT, EDW, Sybase warehouse). The script assigns the sources library names, which solves one key to program sharing with the library approach, namely that everyone must assign the same library name to the same data source or the program won't run properly. So two users can run the same programs, stored in the same locations, from their own machines, but each is logged in independently across secure network connections. Neither can see the other's login.
    • use an INI file with your SQL Server DTS Package
      For SQL Server DTS users... sensitive connection information can be saved with the DTS package. Without passwords on the DTS package
      itself, it leaves the ability to execute the package fairly open. It also proves problematic when wanting to share the DTS package... since
      the username and password get saved with it.

      The INI file can contain all the connection properties (source and destination servers, password, username). The "Dynamic Properties" task
      would fetch the parameters. The file is stored on the server that would be executing the package. The password is in clear text, but the file
      would be secured using file permission restrictions.

      Here's an article that steps through the INI/DTS Connection Properties setup.
      http://databasejournal.com/features/mssql/article.php/3073161

      My only problem with the direction was figuring out how to "unsave" my password from my connection icon/object. To control the persisting of
      the authentication information, use the Persist Security Info option in the Advanced Connection Properties dialog box in DTS Designer. This
      option only exists for SQL Server connections. Also, on the workflow properties(options) of the connection, make sure you check "close
      connection on completion."

      Handling Package Security in DTS
      http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dtssql/dts_pkgmng_sec_09dl.asp
    • Link Access tables without the Save Password Feature
      Access databases can store the userid and password unencrypted for linked tables. So a savvy user can enable viewing system tables and voila know what they are for any systems you are linked to.  Before sending the database to users, delete the tables and make them link them, or relink them yourself WITHOUT the save password feature.

Structuring Local Tables to Mirror EDW

  • allows for portability and sharing of sql
  • removes one layer of translation and potential for error

Check EDW for Load Completion