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.
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.
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?
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;
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.
The SAS Users Group for Administrators (SUGA) is open to all SAS administrators and architects who install, update, manage or maintain a SAS deployment.
SAS technical trainer Erin Winters shows you how to explore assets, create new data discovery agents, schedule data discovery agents, and much more.
Find more tutorials on the SAS Users YouTube channel.