Setting up libraries in SAS with database connections in SAS Management Console

[cjsummers]

Good morning everyone, apologize if these seem like simple questions, but I was curious to see if anyone could give me some advice. We're setting up our first SAS server at my company and my team is responsible for setting up the server with a multitude of libraries for multiple departments and with multiple oracle / SQL databases. Even with my limited SAS experience (and with a little help from users at this community) I was able to setup multiple user accounts / groups that are able to connect to the server w/o issue. With that said, libraries seem a different beast all together (particularly with regards to database connections).

For starters, I have found some guides online that detail the necessary steps to create a database library for users to use. I have followed the steps in creating a user group for the database connection, a database server, and a database library. With that said, we initially had an issue where we were missing an oracle driver on the server (which is resolved), but are now faced with an entirely different issue. When trying to connect, its registering the LIBNAME, PATH, and SCHEMA I setup appropriately, but it wouldn't take the ID / password I used for the database connection (though I tested this in the ODBC connection manager w/o issue). It seemed like SAS Management Console needed a username / password that had access to the SASApp server (which in this case is an administrative account we use). I put in those credentials and the libname now displays with a user ID and password, but with the user ID and password I used to perform the configuration on the library itself (the server admin account / password). I didn't think this was necessarily a big deal, but I can't change it. I tried deleting and recreating the library, and the only username / password that gets used is by that account, it doesn't even prompt me for any credentials. I need to wipe this USER ID and Password so that I can test it with the database user ID and password, but I'm simply not sure how. Any advice or guidance would be greatly appreciated.

[JuanS_OCS]

Hello @cjsummers,

your answer is to use authentication domains. You can see some of the main the references below. In short:

• you would need to create a user group, and in the accounts tab, set the main database user and password, with a custom aunthentication domain (say, DB1Auth).
• Then, on the Connection details of the database connection, you can set this DB1Auth authentication domain
• Finally, on the library, when you select this connection, you will be asked if you waht to prompt user/password or to use an account. If you select the second one, you will get the options of each account you put

With this option, every user member of that user group, will be able to authenticate initially with that account. No more prompts.

http://support.sas.com/documentation/cdl/en/engfedsrv/70118/HTML/default/viewer.htm#n0vqkim10g8j7on1...

http://documentation.sas.com/?docsetId=mcsecug&docsetTarget=n11xfvoau0jli0n1cl6rtfh5idfx.htm&docsetV...

http://documentation.sas.com/?docsetId=bisecag&docsetTarget=p1du6ccnyjmlkdn1pwc9q11m088w&docsetVersi...

https://communities.sas.com/t5/Administration-and-Deployment/Data-base-authentication-details-to-dat...

https://communities.sas.com/t5/Administration-and-Deployment/Same-user-multiple-authentication-domai...

https://communities.sas.com/t5/Administration-and-Deployment/Adding-Authentication-Domain-for-SQL-Se...

PS. Forgot to mention: with this option, the password won't show in the logs either. 😉

[cjsummers]

Thanks for the quick reply JuanS_OCS! That definitely helped with regards to eliminating the wrong username being used for the library (no longer referenced in the libname statement). With that said, I'm not getting the following error:

NOTE: The autoexec file, C:\SAS\Config\Lev1\SASApp\WorkspaceServer\autoexec.sas, was executed at server initialization.
1          libname orac list;
ERROR: Libref ORAC is not assigned.
ERROR: Error in the LIBNAME statement.

Do I have to manually add in each library I create in the SAS Management Console to the usermods config file?

[JuanS_OCS]

My pleasure!

You need to assign the library to, at least, o e SASApp. Once done, you are ready to go for workspace server and batch sessions. For stored process or pooled workspace, you need to restart the object spawner.

[cjsummers]

Thanks again JuanS_OCS, I apologize, but I shouldn't have put it out there as though I had previous experience modifying the autoexec files for SAS (my experience is in Qlikview Administration / Development, but we have recently been tacked on with SAS as well - w/o prior experience). Based on my research on the SAS sites so far I thought I only had to modify this file if I was using pre-assignment with the external config option? Even if I proceed with the database connection using pre-assignment (i.e. metadata engine), I get the following (ignore the # and $, just used as place holders on account of sensitive information):

No tables retrieved from your query. Your connection information may be incorrect. Do you want to view the SAS log?
NOTE: The autoexec file, C:\SAS\Config\Lev1\SASApp\WorkspaceServer\autoexec.sas, was executed at server initialization.

1          libname ### list;
NOTE: Libref=   ##### 
      Scope=    Object Server
      Engine=   ORACLE
      Physical Name= $$$$.$$$$$
      Schema/User= $$$$_$$$$

It appears to pull in the Schema/User from the database server info, but doesn't pull in any kind of password even though there is only one account that exists in the same authentication domain for this database. I have read up on pre-assignment using the external configuration files, but I'm trying to perform what actions I can without having to write script for SAS - which I assumed would be possible using the SAS Management Console GUI, but I think I'm simply not understanding the process properly.

[JuanS_OCS]

Hello @cjsummers,

do you already have any database-based library on your SAS metadata or autoexec file?

BTW: I recommend you to use the autoexec_usermods.cfg file, instead of the autoexec.cfg

If a user is not connecting to the database, and is complaining about credentials, it would be either a problem of the credentials or the configuration of the authentication domain (for clarification purposes, let's focus on it, and let's forget about the autoexec configuration, you can comment any line you have on the autoexec)

1. Check that you can connect with the user to the database
2. Is the database connection ODBC/OLEDB, or just using the DB driver?
3. Do you have the SAS/ACCESS license to that database?
4. Do you have the DB connection created on your SAS compute server (in the OS? If ODBC, you need an ODBC connection, if you have a direct DB connection, you will need the client driver for that DB and to create the connection. Then, test the connection with the credentials you expect.
5. If everything OK till now, you can proceed with the configuration in SAS:
   1. Create the DB Server definition (pointing to the server - SAS compute - where you created the DB connection in the OS)
   2. Create the DB server connection (using the DB1Auth authentication domain)
   3. Create the SAS usergroup, including your users as members and the DB credentials on the Account tab (also password ant the DB1Auth authentication domain)
   4. Create the SAS library, assigning it to a SASApp, and selecting the DB Connection, and selecting the user account you will use (the one set on the usergroup)
   5. (sometimes needed) Refresh/Restart Object spawner related to that SASApp
   6. You can try to register some tables, to test the connection from SAS.