URGENT! Cannot log into SQL Server after password change

Hi there - I'm in a real ****** and need help.

I noticed a series of attempts on the sa account in SQL Server 2000 so I decided to change the password to a long a complicated one to lower the risk of it breaking. I made a lengthy password in notepad, then copy/pasted it into SQL Server (this is all over a Remote Desktop Connection).

The next time I tried to log in it simply wouldn't let me - I kept the original password but it just won't accept it. I don't understand what could have gone wrong!

If anyone has any ideas, they are extremely welcome.
DotSPFAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LowfatspreadCommented:
have you tried it all in Uppercase and/or lowercase?

or maybe you have been cracked....

has another user with sa, reset the password?

Why would you use the SA id anyway?

0
Duane LawrenceCommented:
When I copy and paste passwords, it adds a carrage return.  Manually typing the password will not let you put the carrage return in, because when you hit return it sends the password.  You will have to copy and paste to get the same effect.

Duane
0
paeloCommented:
Maybe when you copied it from notepad, you also copied an extra space at the beginning or end of the password.  Try adding a space at the end, or one at the beginning, or different combinations.

-Paul.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

LowfatspreadCommented:
you didn't copy it as ascii and save as unicode perhaps?
0
DotSPFAuthor Commented:
LowFat - haven't been cracked - event log confirms that. I need to use sa for some complex reasons there's no point going into - I'd much prefer to use Windows Auth only but it's not possible. Don't thinkk the ascii/unicode could have been the problem - it's just a standard notepad text file.

Duane - I used copy and paste in the first place, not manually typing.

Paelo - Tried spaces, no luck there.

Thanks for the attempts.
0
paeloCommented:
I mean, it could be that you pasted something else entirely.  Are you sure the copy operation was successful?  Did you paste it back to notepad before pasting it into the password dialog?

-Paul.
0
Duane LawrenceCommented:
If you re-apply a SQL server patch service pack level it will allow you to reset the sa password.

Duane
0
rdrunnerCommented:
Is there a way to try a admin account on the server with Integrated security?

The Admin account should be able to change the SA password if you have mixed mode enabeled.
0
DotSPFAuthor Commented:
Ok, this is pretty screwed up, but may help me on the way to a solution...

I tried Duane's advice of applying a service pack. However, during the install process it asks for the current password. I entered the one from my text file and bam - logged in no problems! However, I STILL cannot log in via Enterprise Manager!
0
Duane LawrenceCommented:
You could have entered anything and it would have accepted it.  The point of applying a patch was to reset the password to something that you can type in.  Stop pasting it in from notepad.

Duane
0
DotSPFAuthor Commented:
Duane, I'm not a complete fool - on applying the service pack it asks you to VERIFY the password. It doesn't ask for a new one. Putting just anything in gives you an invalid password error. I originally tried it with just a simple string, then using just Windows Authentication only. It wouldn't proceed without the correct password.

0
DotSPFAuthor Commented:
Quick update, just tired importing data into excel remotely from SQL server. No problems at all using sa and my long password string - it was obviously changed correctly. It's just enterprise manager that won't let me in with it!
0
Duane LawrenceCommented:
I am sorry, I don't mean to be insulting, but you can enter anything.  It does not need to be the same as the last one.  Just try it.

Duane
0
DotSPFAuthor Commented:
Duane,

No offence, you weren't insulting at all. I did try it. Many times, with many different things, as I descrbed in my last comment. I wanted the existing password.

I'll try it again for what it's worth. I let you know in a few minutes.

0
DotSPFAuthor Commented:
I wasn't mistaken. The second dialog box for the SP3 install asks for "the authentication mode setup should use to connect to SQL Server" and then asks for either SQL authentication (requiring the sa password) or Windows Authentication.

Using SQL authentication with any password gives me: "setup was unable to validate your password."
Using Windows Authentication gives me: "Setup was unable to validate the logged on user."

However, entering the password string I saved allows me to continue.

Thank you for your help so far!
0
Duane LawrenceCommented:
Right, I hear you.

I have successfully changed the password doing this on Service Pack 3 for 2000 and a service pack for v 7.0.  However in both cases the previous sa password was blank now that I think about it.  Sorry to waste your time, I honestly thought it would work.

Duane
0
Duane LawrenceCommented:
I just read your note saying the import process works.   It makes me wonder if there is a limit to the number of characters that enterprise manager can handle.

How many characters are in the new password?  

Duane
0
DotSPFAuthor Commented:
erm... 38 - I wanted it secure. Seems to have worked slightly better than planned.
0
rdrunnerCommented:
Hello...

Does your SQL Server support mixed auth.?

If yes... Can you try to logon locally as admin of the server? Anyone who is in the admin group of that server should have SA rights by default. That means if you log into the server as administrator (Windows not SQL) and then open the EM under the local admin account you should be able to change the SA password.

Is this possible?
0
rdrunnerCommented:
Another possible way would be to connect to the SQL Server via SQMDMO and use the Setpassword method of the Login object.

This can be done with a small VB app...
0
pra_kumar03Commented:
Try logging in with the query analyzer with that password. it seems you are getting this issue only thru Enterprise manager?
0
DotSPFAuthor Commented:
Ok, sorry I haven't asnwered for a while - STILL can't solve this. I've succesfully logged into the query analyser with my password, and tried using:

sp_password 'oldpassword', 'newpassword'

...but it doesn't work because, Einstein that I am, there's a single quote in the old password.

Any further ideas?
0
rdrunnerCommented:
You can escape a single quotes by using 2 single quotes instead....

try

print 'Hello '' World'

this will output

Hello ' World

Use the same way with your password
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pra_kumar03Commented:
Now here is what you can do.
The password that you have is correct.
So go into enterprise manager , delete the SQL server registration and then re register it try with this password and change it .
or try this from query analyser
set quoted_identifier off
exec sp_password "oldpassword","new password"
set quoted_identifier on
0
DotSPFAuthor Commented:
At fecking last, it worked!!! Thanks to all for the assistance.
0
DotSPFAuthor Commented:
Oh crap, I meant to split the points between pra_kumar03 and rdrunner. rdrunner - if you'd be kind enough to set up a question worth 250 points then let pra_kumar answer it?
0
rdrunnerCommented:
Post a question in the community support asking for a split.

I am not allowed to post a free question worth 250 points since thats against the rules... (A question is only allowed 500 points max and that would increase it to 750...) Got already fussed at for that ;)
0
pra_kumar03Commented:
post your question for the split at  
http://www.experts-exchange.com/Community_Support/
0
DotSPFAuthor Commented:
I meant split the 500 into 250 for yourself and 250 for pra_kumar. Whatever, just didn't want to hassle a mod.
0
pra_kumar03Commented:
You can not do so. You need to put a request in the Community Support and they will handle it in the best possible way.
0
rdrunnerCommented:
Well I cant do that...

You have to post in community support for that...
0
pra_kumar03Commented:
hey rdrunner
let dotspf post the questionin community support, that would be the  best way.
0
DotSPFAuthor Commented:
That's what I've done.

 Seems a bit bizarre though. Why couldn't rdrunner have just opened a question worth 250 points, then pra_kumar post in that question, and be awarded them? Rdrunner is down 250, pra_kumar is up 250, everyone's a winner.

Oh well, thanks for your help. A mod will sort out the pointsI expect.
0
rdrunnerCommented:
The Points we get for answering questions are not the same as the points we spend for questions. If you answer regulary you will have unlimited "asking-Points" for free. The rule about limiting each question to 500 points is a "direct result" from this (Even before that rulechange you had more asking points then you could spend with a premium account so really it wasnt a big change). Since if i post several questions worth 500 points i could "give" you a free premium account this way. Also asking a question will not reduce my expert points. It will only increase pra_kumar.

You question was worth 500 "asking-Points" By giving it an A you grant "me" 2000 expert points. If I post another question worth 250 and let pra_kumar answer it i would still keep my 2000 expert points but pra_kumar would gain an aditional 1000 expert points for free...

Hope this makes sense :)
0
DotSPFAuthor Commented:
Ah-ha! My eyes are opened. I've never actually been given points for answering a question. I tend to buy them instead as this place is so invaluable for my work. It'd be nice to answer more but I never seem to be quite 'expert' enough!
0
DotSPFAuthor Commented:
Sorted
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.