http://www.perlmonks.org?node_id=905838

OzVegan has asked for the wisdom of the Perl Monks concerning the following question:

I'm writing maintenance plans for SQLServer2008 Express (no SQLAgent and usefulness whatsoever). To check database integrity, perform automated maintenance etc I need to use the SQLCMD utility with an administrative user name and password.

Obviously I'm not going to hard code such things in the script itself but I will have to record them somewhere. What are my options? I've thought of:

Etc...etc... I'm pushing these scripts to over 700 servers as part of our company's supply chain upgrade. Yes they went for free SQLServer versions ( doh! ). The systems have to operate by themselves in cases of comms drop outs which we experience quite a bit.

Any ideas regarding securing passwords with perl much appreciated.

Replies are listed 'Best First'.
Re: Using Admin Passwords in Script
by Tanktalus (Canon) on May 20, 2011 at 03:06 UTC

    I've been fighting with a nearly-identical issue for the last couple of days, but with DB2 on AIX instead of SQLServer on Windows, so maybe I'll write down some of the thoughts I've had and some of the advice I remember from the plentiful advice freely given on the CB.

    In my mind, the best solution is one that uses the OS to authenticate instead of a password. You can run your maintenance code from under a service of some sort which can run as an authorised user, and problem is solved. I don't know if that is an option with SQLServer. It is a partial solution to DB2 - in my case, I needed to use the other connect type to do certain tests, and that connect type does not use the currently-logged-in user for authentication; it's user and password only.

    Next best is a password safe of some sort. On Linux with KDE, I've used KWallet (with Net::DBUS to query from it - I posted my code earlier, but it's not likely to help you so I'm not going to look it up link it). I don't know if KeePass for Windows has some interface that you can use to query from Perl. Other similar solutions include a GPG-encrypted password where you have a gpg-agent running. The downside to these is that you do have to log on to open the safe after every reboot. This may not be a viable option. For this reason, and due to a lack of any of these types of tools on AIX, and the short turn-around time required eliminating legal approval for anything, it was not an option for me this time, either. As I said, I use KWallet to store these types of passwords, but not for fully-automated tools.

    After that is a tightly-locked down source. This is hard to quantify. In my mind, a plain-text password stored on the system such that only the user that the script is running as has read access to it, and no one else, is sufficient. Any obfuscation or further encryption of the password is, in my mind, a waste. The difference between a plain-text password stored in a locked-down file and a mangled/encrypted password stored in the same locked-down file is akin to the difference between storing your apartment key on a hook just inside the door vs storing the apartment key inside the cookie jar in the kitchen. The thief has already gained access to the apartment (the user ID who has read access to the file) at that point, you're just making it more difficult for them (a malicious user) to find the key, without preventing the theft of any other valuable. After all, whatever algorithm you have for retrieving the password from that file is stored in plain text in your perl code, which that user must also be able to read merely so that perl can execute it, so the malicious user could just grab both the password file and the source to the decryption, and decrypt it at their leisure.

    In the case of a malicious user, encoding the password delays password retrieval by minutes. In the case of a non-malicious user, they won't even look anyway. It's not worth the effort in my mind. Apparently, many companies' security policies do not agree.

    Someone in the CB metnioned something about a secondary machine to hold the password to lock it down. I wasn't entirely clear on how this would help, other than if it used your OS user implicitly (again, no password) and then logged the request. It wouldn't remove the risk from a malicious user, but it would provide a safer avenue of detection.

    The best advice, which I also could not avail myself of, but you might, was to talk to the security officer of the company. S/he may have insight as to what the company has available, if anything. If nothing, s/he may have advice on what the company deems "acceptable security risk."

    In the end, the stakeholders who objected to the plaintext password in a safe file eventually agreed to withdraw the requirement that absolutely had to depend on the password, and I rewrote the part that did not depend on the password in such a way to guarantee a timeout ("alarm" doesn't necessarily fire if DBD::DB2's connect hangs): I shunted it to a child process via fork, and put the timeout in the foreground process, kill -9'ing the child if the timeout was reached. I ended up without a password requirement at all, but perhaps some of the above will help you.

Re: Using Admin Passwords in Script
by BrowserUk (Patriarch) on May 20, 2011 at 03:55 UTC

    Use the facilities provided by the OS.

    Preferably

    If neither the -U option nor the -P option is specified, sqlcmd tries to connect by using Microsoft Windows Authentication mode.

    Otherwise set up a user or group profile for running the script(s) that has the userid & password environment variables (SQLCMDUSER & SQLCMDPASSWORD) set in the profile. Profiles are encrypted.

    That way, the scripts can only be run by those authorised to do so by the login id or group membership.


    Examine what is said, not who speaks -- Silence betokens consent -- Love the truth but pardon error.
    "Science is about questioning the status quo. Questioning authority".
    In the absence of evidence, opinion is indistinguishable from prejudice.

      Thanks guys. Okay, so there are a few options I can try that rely on the agreement of our brand's strict security policies but one will have to work. Either:

      • Create a text file or use the library folder and lock it down to specific users that have SQLServer administrative rights
      • Create a local user and password that has access to run the script and has admin rights to SQLServer

      The Windows Service option didn't work for me. I use Perl scripted Windows Services on one server with INTSRV but in our store environment they only allow SC.EXE to run and it couldn't get it to accept the path to the Perl.exe program and pass it the path to the script at the same time. It just wouldn't work otherwise I'd have Windows Services running.

      Thanks for your help and insights, I have something substantial to work with now :-)

Re: Using Admin Passwords in Script
by sundialsvc4 (Abbot) on May 23, 2011 at 13:10 UTC

    The tough part of your requirement is when you say, “the systems have to operate by themselves in case of communications drop-outs, which we experience quite a bit.”

    I don’t know the facilities well-enough to say whether this excludes the use of Windows’s authentication services or not.   Clearly, if it does not, then that is the approach that you want to take:   centralized management, with satisfactory means of operating in “off-line mode.”

    Failing that, I would advocate the use of a cryptographically secure password-safe file which uses public-key cryptography ... and of course, a single Perl package whose sole responsibility is to know how to withdraw from that “safe” the strings that the application needs.   (In fact, I would build such a package anyway, in order to isolate the application from any future implementation changes.)   It goes without saying that you should use a strong, well-known, platform independent package here; not roll-your-own snake oil.

    The central idea is that ... “the magic key is not in the source-code; merely the means for getting it.”   And those “means” only work properly wherever the application is actually installed.   If you don’t steal both pieces, you don’t have the magic key.   If you do steal them, you still can’t change them, so you can’t prevent the company, having detected your intrusion, from promptly and effectively locking you out again.   Furthermore, the company can re-issue keys (and can even issue different keys to different computers), and can easily change them from time to time, without touching the deployed source-code itself.   Conversely, they can change the deployed source-code whenever they need to, without disrupting the magic.

Re: Using Admin Passwords in Script
by tokpela (Chaplain) on May 23, 2011 at 21:51 UTC

    I have never personally used this module but would Win32::IntAuth work?