topic Re: User ID variable in odbc.ini file? in Administration and Deployment

User ID variable in odbc.ini file?

Jonno — Fri, 05 Jan 2018 14:54:11 GMT

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.

Re: User ID variable in odbc.ini file?

jklaverstijn — Fri, 05 Jan 2018 20:17:31 GMT

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.

Re: User ID variable in odbc.ini file?

Jonno — Fri, 05 Jan 2018 17:00:32 GMT

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

Re: User ID variable in odbc.ini file?

SASKiwi — Fri, 05 Jan 2018 21:56:23 GMT

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").

Re: User ID variable in odbc.ini file?

Jonno — Fri, 05 Jan 2018 22:02:25 GMT

Thanks. Hopefully this solution will work for us.

I really appreciate both responses I received.

Jonno