Can anyone think of any techniques where i can have a Windows scheduled task run OSQL, but not have to pass the clear-text password with cleartext password being in the clear? E.g.:

>osql -U iboyd -P BabyBatterStapleCorrect

Assumption: No Windows Authentication (since it's not an option)

i was hoping there was a

>OSQL -encryptPassword "BabyBatterStapleCorrect"
> Encrypted password: WWVzIGkgd2FudCB0byByYXBlIGJhYmllcy4gQmlnIHdob29wLiBXYW5uYSBmaWdodCBhYm91dCBpdD8=

And then i could call OSQL with:

>osql -U ian -P WWVzIGkgd2FudCB0byByYXBlIGJhYmllcy4gQmlnIHdob29wLiBXYW5uYSBmaWdodCBhYm91dCBpdD8=

But that's not something Microsoft implemented.

  • Why is integrated authentication not an option? – Chris McKeown Sep 13 '12 at 21:40
  • a) SQL Server complains i do not have permissions required to create a Windows account, b) the company that owns the server does not want to create us a Windows account, c) it's not the hypothetical question being asked – Ian Boyd Sep 13 '12 at 22:00
  • 1
    Fair enough. Why the worry about the password being in clear text? If OSQL allowed you to pass an encrypted password to authenticate, that could still be intercepted and used in the same way as a clear text password could. Or am I missing something? – Chris McKeown Sep 13 '12 at 22:15
  • 1
    @ChrisMcKeown - you took the words out of my mouth. We use a web service where we need to hash a password in MD5 before POSTing it in cleartext. Which means that the MD5 hash becomes the password, which is kind of pointless. – Mark Henderson Sep 13 '12 at 23:16
  • @ChrisMcKeown Well OSQL could help me follow Microsoft's recommended practices, and use the Data Protection API. The entire Data Protection API suffers the same limitation, except that using it makes things infinitely more secure (someSecurity/zeroSecurity = +INF) (as does encrypting web.config) – Ian Boyd Sep 14 '12 at 13:49

Information about whether the logon process in OSQL is secure or not seems to be sketchy and contradictory (see here)

I think SQLCMD supports SSL encryption which would prevent the password being sniffed over the wire - but if you're storing the password in a script or something and you're worried about prying eyes, encrypt it and decrypt it yourself before passing it to SQLCMD.

| improve this answer | |
  • Accepted as, "Not really possible"; likely the only answer i'm likely to see. – Ian Boyd Sep 14 '12 at 18:53
  • I think the question really is, who are you trying to hide the password from? Someone sniffing over the wire, or someone who could potentially see the script on the server? Using a clear text password is fine (and indeed normal) as long as the transmission medium is secure. – Chris McKeown Sep 15 '12 at 23:32
  • Someone seeing the script on the server. Security theater makes people happy. – Ian Boyd Sep 17 '12 at 14:52
  • In that case, as Mark Henderson put it, if OSQL allowed you to do this then the encrypted password effectively becomes the password, and then what's to stop someone who saw the script from taking the encrypted password and using it with OSQL? You might as well just set the SQL account's password to something that looks as if it has been encrypted - it would be no more or less secure. – Chris McKeown Sep 17 '12 at 18:55
  • There is a value in someone not knowing a password, rather than just being able to use it. Also i cannot change the password, it has already been set. – Ian Boyd Sep 17 '12 at 20:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.