Architecting, installing and maintaining your SAS environment

User ID variable in odbc.ini file?

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

User ID variable in odbc.ini file?

In an odbc.ini, is it possible to use a reference variable that resolves to the user's login ID in SAS when they connect? For example, if the ODBC driver has a variable UID, I might want something like this below. So at runtime I want ${USER} (or however I reference it) to resolve to the logged in user's SAS login ID. I understand that kerberos is an option, but I'm looking for a solution using SAS internal/local accounts. SAS is running on Linux, and users are using SAS through a web browser.

 

[MYCONNECTION]

UID=${USER}

 

Thank you.


Accepted Solutions
Solution
‎01-05-2018 05:00 PM
Super User
Posts: 3,863

Re: User ID variable in odbc.ini file?

Not too sure how this works with Linux SAS servers, but with Windows-based servers it is perfectly acceptable to only have IWA configured for the SAS ODBC database connection but not with the SAS server login. For example if you are using EG, your EG profile can use an actual userid/password not IWA, then you can specify IWA in your ODBC connection string (in Windows this is "trusted_connection=yes").

View solution in original post


All Replies
Valued Guide
Posts: 531

Re: User ID variable in odbc.ini file?

[ Edited ]

Not to my knowledge. Never seen it. But you may decide to use the "complete" or "noprompt" option to your ODBC libname statement where you have the entire odbc.ini file inside your SAS code. Any variable content could then be dealt with via macro variables.

 

See Usage Note 52777 for details.

 

An example would be:

libname mydata odbc complete="driver=SQL Server;
                              user=&SYSUSERID;
                              pwd=password;
                              database=SQL-database;
                              Server=server-name" 
                   schema=schema-name;

Will be a challenge to stick in the password without spilling that into source code and log files.

 

Hope this helps,

-- Jan.

 

 

New Contributor
Posts: 3

Re: User ID variable in odbc.ini file?

Posted in reply to jklaverstijn

Thanks for the info and example, Jan. Based on that, I think we'll need to enable kerberos (IWA). This database we're using for ODBC is already kerberos enabled (although other DBs are not). The driver allows a DelegateKrbCredentials option, but we have not enabled kerberos authentication for users on the SAS server. Enabling this may be complicated with this configuration and may also be very disruptive to the existing environment/users.

 

Ideally, I think we would prefer to continue using local SAS logins, but use kerberos credentials with this driver only. But, I assume that is not possible. Actually, ideally, IWA would have been enabled when SAS was originally installed. 

 

Thanks for your input.

 

Jonno

Solution
‎01-05-2018 05:00 PM
Super User
Posts: 3,863

Re: User ID variable in odbc.ini file?

Not too sure how this works with Linux SAS servers, but with Windows-based servers it is perfectly acceptable to only have IWA configured for the SAS ODBC database connection but not with the SAS server login. For example if you are using EG, your EG profile can use an actual userid/password not IWA, then you can specify IWA in your ODBC connection string (in Windows this is "trusted_connection=yes").

New Contributor
Posts: 3

Re: User ID variable in odbc.ini file?

Thanks. Hopefully this solution will work for us. 

 

I really appreciate both responses I received.

 

Jonno

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 273 views
  • 2 likes
  • 3 in conversation