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

[jcromwell77]

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.

[gwootton]

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.

[jcromwell77]

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.

[gwootton]

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

[jcromwell77]

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?

[gwootton]

That's correct.

[SASKiwi]

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;

[Sajid01]

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.