1

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"
>
> OSQL
> Encrypted password: WWVzIGkgd2FudCB0byByYXBlIGJhYmllcy4gQmlnIHdob29wLiBXYW5uYSBmaWdodCBhYm91dCBpdD8=

And then i could call OSQL with:

>osql -U ian -P WWVzIGkgd2FudCB0byByYXBlIGJhYmllcy4gQmlnIHdob29wLiBXYW5uYSBmaWdodCBhYm91dCBpdD8=

But that's not something Microsoft implemented.

Ian Boyd
  • 5,131
  • 14
  • 57
  • 79
  • 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](http://msdn.microsoft.com/en-us/library/aa213088(v=sql.80).aspx), and use the [Data Protection API](http://msdn.microsoft.com/library/aa376210.aspx). 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
  • I assume you're referring to this part: `If Windows Authentication is not available, prompt users to enter their credentials at run time. Avoid storing credentials in a file. If you must persist credentials, you should encrypt them with the Win32 cryptoAPI.' To me, that's not talking about passing an encypted password over the wire. It's simply recommending that if you do need to store the password somewhere, encrypt it - but you're gonna have to handle the decryption yourself before you pass the password to OSQL. – Chris McKeown Sep 14 '12 at 18:29
  • Incidentally, I think `SQLCMD` supports [SSL encryption](http://support.microsoft.com/kb/316898) which would prevent the password being sniffed over the wire - but the above caveats still apply - 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`. I'll add this as an answer in case you think it's useful. – Chris McKeown Sep 14 '12 at 18:33

1 Answers1

1

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.

Community
  • 1
Chris McKeown
  • 7,128
  • 1
  • 17
  • 25
  • 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
  • If that's literally all you want to do, then you could invoke OSQL from a script that has the password Base64 encoded, and decodes it before passing it to OSQL - that way the plain text of the password would never appear in the file (but obviously easily retrievable by anyone who knew what they were looking at). – Chris McKeown Sep 17 '12 at 21:19