topic Re: Does a Program with hard coded User & Password require Server Manager configuration? in Administration and Deployment

Does a Program with hard coded User & Password require Server Manager configuration?

jcromwell77 — Thu, 26 Aug 2021 14:32:17 GMT

For better or worse, our programmer is hard-coding the DSN, User, and Password in their programs. Assuming the DSN on the Windows Server is set up properly (i.e.-correct credentials, connection string, and default database), does hard-coding the DSN, User and Password bypass Server Manager? In other words, does this go through Server Manager at all, or does it connect the program directly to the DB via the Windows Server DSN configuration?

Ultimately, I plan to ensure we're keeping with best practice, and I've never seen hard-coded User and Password directly in a program considered best practice. I assume we should be verifying user credentials via the User Manager, and then connecting to the DB via the Server Manager.

Any guidance/input is greatly appreciated.

Re: Does a Program with hard coded User & Password require Server Manager configuration?

gwootton — Fri, 27 Aug 2021 17:01:47 GMT

The definitions in Metadata would be used if you specify the META libname engine. If you are specifying the server connection information directly those are not used.

Re: Does a Program with hard coded User & Password require Server Manager configuration?

jcromwell77 — Fri, 27 Aug 2021 17:32:19 GMT

To make sure I'm clear, I would need to use something like:

libname sample meta ....

versus what we're currently using

libname sample odbc datasrc:...

Is that correct?

Also, I may be confusing things a bit. I have been reading about Authentication Domain for storing and passing credentials, which is what I believe I need to know as well. Thanks.

Re: Does a Program with hard coded User & Password require Server Manager configuration?

gwootton — Fri, 27 Aug 2021 17:41:23 GMT

Correct. When using the meta engine it will use the server definition in Metadata which includes connection properties that include an authentication domain so you would not need to specify in the libname statement an auth domain where you have stored the credentials in Metadata. When uisng the odbc libname engine you can optionally specify authdomain= to use the credentials from that domain instead of user/password hardcoded, but this would not use the server definition in Metadata so that would still need to be specified.

LIBNAME Statement for the ODBC Engine
https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/acreldb/p0bu3zsz1a08ton1msxdx1jo45np.htm

LIBNAME Statement for the Metadata Engine
https://go.documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/lrmeta/n16hsug0xiczidn141ezc7rlz8rb.htm

Re: Does a Program with hard coded User & Password require Server Manager configuration?

SASKiwi — Sat, 28 Aug 2021 03:09:44 GMT

Since you appear to be using Windows SAS servers then you could consider using Integrated Windows Authentication (IWA) if this is supported by the databases you connect to. You can also use DSN-less database connection strings where everything is defined in the string and doesn't require DSNs to be stored elsewhere like in the Windows ODBC Adminstrator.

We use both of these methods ourselves and it is secure (no userids and passwords required) and efficient from a maintenance point of view.

Here is an example of a LIBNAME statement using a DSN-less database connection to SQL Server using IWA:

libname SQLSRVR odbc noprompt = "server=SQLServerName;DRIVER=SQL Server Native Client 11.0;Trusted Connection=yes" DATABASE = MyDatabase schema = dbo;

Re: Does a Program with hard coded User & Password require Server Manager configuration?

jcromwell77 — Mon, 01 Nov 2021 19:32:00 GMT

You are correct, and I have not seen the first code snippet using "meta" before. If I'm understanding that correctly, does the first libname statement use the server manager connection in the management console to connect?

Re: Does a Program with hard coded User & Password require Server Manager configuration?

gwootton — Mon, 01 Nov 2021 19:58:21 GMT

That's correct.

Re: Does a Program with hard coded User & Password require Server Manager configuration?

Sajid01 — Fri, 19 Nov 2021 14:59:21 GMT

A one word answer to your question "Does a Program with hard coded User & Password require Server Manager configuration? " would be NO.

This may not be the best practice but is of common occurence. It ultimately depends on the organizations work culture and needs.