July 22, 2020 at 12:17 pm
Hello,
I have this 3 lines:
sqlcmd -S "Server1" -d <db> -U <user> -P <password> -i query.sql -o result.txt
sqlcmd -S "Server2" -d <db> -U <user> -P <password> -i query.sql >> result.txt
sqlcmd -S "Server3" -d <db> -U <User> -P <DifferentPassword> -i query.sql >> result.txt
If I copy and paste this code to a cmd window this is running well.
If I save this code to a .bat file (e.g.: sqlrun.bat) and I try to call it from a new cmd window (C:\Scripts>sqlrun.bat) I'm getting this error for first two connections:
Sqlcmd: Error: Microsoft SQL Server Native Client <xx.x> : Login failed for user '<User>'...
Can you help me what is wrong?
July 22, 2020 at 3:38 pm
Yes, the login isn't correct. That's what the error is.
July 22, 2020 at 5:06 pm
But I didn't change the login parameters. So I don't know why is working the 1st case and why is not working the 2nd case?
July 22, 2020 at 8:38 pm
Salliven,
What is the error on the server side? Look at the SQL Server Logs in the Management folder of Object Explorer, or run a SQL command like:
EXEC master.dbo.xp_readerrorlog 0, 1, N'Login Failed';
This error is often more detailed than the error you receive on the client side, and will help you troubleshoot this issue easier.
July 31, 2020 at 1:14 pm
shake up your troubleshooting. don't save one copy of that batch file, fail, then give up. create another batch file. run a hexdump on your batch and look for hidden characters (happened to me). try a batch file with a single line, with the failing line if you know which line fails. check the logs like previously recommended. type the sqlcmd line manually in a cmd window. run ssms and connect to the server with the failing login using the failing credentials.
errors can literally come from anywhere 🙂
July 31, 2020 at 1:22 pm
reminds me of a story from one of my favorite books, a truly geeky must read in my opinion, "the soul of a new machine" by tracy kidder. they're bringing a new minicomputer to life, its going to be a shiny new line of 32 bit supermini's (the book has been out for awhile heh), and they're deep into the debug on a prototype, having random nondeterministic errors that are often the case with complex systems running with a large number of internal states, etc, and they're trying to recreate this random error or at least encourage it to happen more often, and the VP of engineering comes in, gets a run down of what is happening, logic probes everywhere detailing intently internal signals etc etc
The guy steps up to the prototype with the board on an extender, grabs it, and shakes it quite violently and it fails. the engineers are then tasked to rewire the thing as connections just got too loose heh
July 31, 2020 at 2:21 pm
reminds me of a story from one of my favorite books, a truly geeky must read in my opinion, "the soul of a new machine" by tracy kidder. they're bringing a new minicomputer to life, its going to be a shiny new line of 32 bit supermini's (the book has been out for awhile heh), and they're deep into the debug on a prototype, having random nondeterministic errors that are often the case with complex systems running with a large number of internal states, etc, and they're trying to recreate this random error or at least encourage it to happen more often, and the VP of engineering comes in, gets a run down of what is happening, logic probes everywhere detailing intently internal signals etc etc
The guy steps up to the prototype with the board on an extender, grabs it, and shakes it quite violently and it fails. the engineers are then tasked to rewire the thing as connections just got too loose heh
Too funny... we had something similar happen on board the submarine I was on. A relatively new piece of equipment started to suffer from sporadic errors after just a couple of months being underway. They'd trace the problem to a board and replace it only to have a different problem appear after even just a couple of minutes later.
They ran out of replacement boards and, since we were underway, there was no where to go to get new ones.
I took a look at some of the old "failed" boards under a bright light and noticed the edge connectors appeared to have a bit of a coating on them. The current failure required a board that they ran out of replacements for, so I took a good old fashioned pink eraser and polished up the edge connectors, removing the apparent coating that had built up in the process. I installed the board and fired the system up and it worked at least for a couple of minutes.
I powered down the machine, took out all the boards, did the pink eraser thing to all the boards, put all the boards back in, powered it up, and no more problems.
It was the tiny bit of amine (a chemical used to remove CO2 from the air) that gets into the air and seemed to love plating out on gold edge connectors in machines. The pink eraser was a bit too abrasive for continued use but we figured out that we could wipe the connectors with a bit of white "gillie" (our pet name for pure grain alcohol, which was used to clean compensatory plates on our sonar systems) to remove the coating and added that process to our monthly maintenance on the machine. We even built a special tool (basically, something that looked a bit like a gutter with two fine grain sponges held close together that also acted as wicks for the alcohol) to run the edge connectors through that did the cleaning "auto-magically" and quickly.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 31, 2020 at 2:28 pm
Yes, the login isn't correct. That's what the error is.
But I didn't change the login parameters. So I don't know why is working the 1st case and why is not working the 2nd case?
It's been a long time since I've worked with batch files and so I have to apologize for not having the precise answer you need to get these commands running in a batch file. I'm with you that it seems that they should run just fine in a batch file.
My real concern is you putting logins and passwords in a clear text batch file. At the very least, there needs to be a common active directory login that has access to all of these machines and SQLCmd should be setup to use a "trusted connection". Then, to use the batch file, login with that particular login and run the batch file.
You simply must NEVER include clear text logins in any batch file or similar things. Period.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 31, 2020 at 3:22 pm
Steve Jones - SSC Editor wrote:Yes, the login isn't correct. That's what the error is.
salliven wrote:But I didn't change the login parameters. So I don't know why is working the 1st case and why is not working the 2nd case?
It's been a long time since I've worked with batch files and so I have to apologize for not having the precise answer you need to get these commands running in a batch file. I'm with you that it seems that they should run just fine in a batch file.
My real concern is you putting logins and passwords in a clear text batch file. At the very least, there needs to be a common active directory login that has access to all of these machines and SQLCmd should be setup to use a "trusted connection". Then, to use the batch file, login with that particular login and run the batch file.
You simply must NEVER include clear text logins in any batch file or similar things. Period.
So what should I do about my open ssh private keys?
August 1, 2020 at 8:34 pm
Jeff Moden wrote:Steve Jones - SSC Editor wrote:Yes, the login isn't correct. That's what the error is.
salliven wrote:But I didn't change the login parameters. So I don't know why is working the 1st case and why is not working the 2nd case?
It's been a long time since I've worked with batch files and so I have to apologize for not having the precise answer you need to get these commands running in a batch file. I'm with you that it seems that they should run just fine in a batch file.
My real concern is you putting logins and passwords in a clear text batch file. At the very least, there needs to be a common active directory login that has access to all of these machines and SQLCmd should be setup to use a "trusted connection". Then, to use the batch file, login with that particular login and run the batch file.
You simply must NEVER include clear text logins in any batch file or similar things. Period.
So what should I do about my open ssh private keys?
Dunno... good luck with those. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
August 3, 2020 at 1:00 pm
x wrote:Jeff Moden wrote:Steve Jones - SSC Editor wrote:Yes, the login isn't correct. That's what the error is.
salliven wrote:But I didn't change the login parameters. So I don't know why is working the 1st case and why is not working the 2nd case?
It's been a long time since I've worked with batch files and so I have to apologize for not having the precise answer you need to get these commands running in a batch file. I'm with you that it seems that they should run just fine in a batch file.
My real concern is you putting logins and passwords in a clear text batch file. At the very least, there needs to be a common active directory login that has access to all of these machines and SQLCmd should be setup to use a "trusted connection". Then, to use the batch file, login with that particular login and run the batch file.
You simply must NEVER include clear text logins in any batch file or similar things. Period.
So what should I do about my open ssh private keys?
Dunno... good luck with those. 😀
Apologies for being a bit off topic, but even though open ssh keys are plain text credentials saved to disk, they have a unique characteristic which doesn't share the problems that OP would have with plain text credentials in batch files. Took me a while to get a handle on it, I'm not the sharpest stick in the woods LOL
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply