?
Solved

URGENT! Cannot log into SQL Server after password change

Posted on 2004-11-04
37
Medium Priority
?
675 Views
Last Modified: 2008-01-09
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.
0
Comment
Question by:DotSPF
  • 14
  • 7
  • 6
  • +3
36 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 12496781
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
 
LVL 6

Expert Comment

by:Duane Lawrence
ID: 12496873
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
 
LVL 9

Expert Comment

by:paelo
ID: 12496892
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 50

Expert Comment

by:Lowfatspread
ID: 12497138
you didn't copy it as ascii and save as unicode perhaps?
0
 

Author Comment

by:DotSPF
ID: 12497334
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
 
LVL 9

Expert Comment

by:paelo
ID: 12497382
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
 
LVL 6

Expert Comment

by:Duane Lawrence
ID: 12497462
If you re-apply a SQL server patch service pack level it will allow you to reset the sa password.

Duane
0
 
LVL 11

Expert Comment

by:rdrunner
ID: 12497534
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
 

Author Comment

by:DotSPF
ID: 12500055
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
 
LVL 6

Expert Comment

by:Duane Lawrence
ID: 12500142
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
 

Author Comment

by:DotSPF
ID: 12500197
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
 

Author Comment

by:DotSPF
ID: 12500211
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
 
LVL 6

Expert Comment

by:Duane Lawrence
ID: 12500285
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
 

Author Comment

by:DotSPF
ID: 12500312
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
 

Author Comment

by:DotSPF
ID: 12500358
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
 
LVL 6

Expert Comment

by:Duane Lawrence
ID: 12500386
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
 
LVL 6

Expert Comment

by:Duane Lawrence
ID: 12500405
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
 

Author Comment

by:DotSPF
ID: 12500419
erm... 38 - I wanted it secure. Seems to have worked slightly better than planned.
0
 
LVL 11

Expert Comment

by:rdrunner
ID: 12503661
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
 
LVL 11

Expert Comment

by:rdrunner
ID: 12503688
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
 
LVL 3

Expert Comment

by:pra_kumar03
ID: 12509432
Try logging in with the query analyzer with that password. it seems you are getting this issue only thru Enterprise manager?
0
 

Author Comment

by:DotSPF
ID: 12613157
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
 
LVL 11

Accepted Solution

by:
rdrunner earned 1000 total points
ID: 12614350
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
 
LVL 3

Assisted Solution

by:pra_kumar03
pra_kumar03 earned 1000 total points
ID: 12614904
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
 

Author Comment

by:DotSPF
ID: 12614979
At fecking last, it worked!!! Thanks to all for the assistance.
0
 

Author Comment

by:DotSPF
ID: 12614996
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
 
LVL 11

Expert Comment

by:rdrunner
ID: 12615143
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
 
LVL 3

Expert Comment

by:pra_kumar03
ID: 12615160
post your question for the split at  
http://www.experts-exchange.com/Community_Support/
0
 

Author Comment

by:DotSPF
ID: 12615224
I meant split the 500 into 250 for yourself and 250 for pra_kumar. Whatever, just didn't want to hassle a mod.
0
 
LVL 3

Expert Comment

by:pra_kumar03
ID: 12615252
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
 
LVL 11

Expert Comment

by:rdrunner
ID: 12615282
Well I cant do that...

You have to post in community support for that...
0
 
LVL 3

Expert Comment

by:pra_kumar03
ID: 12615325
hey rdrunner
let dotspf post the questionin community support, that would be the  best way.
0
 

Author Comment

by:DotSPF
ID: 12615366
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
 
LVL 11

Expert Comment

by:rdrunner
ID: 12615506
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
 

Author Comment

by:DotSPF
ID: 12615697
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
 

Author Comment

by:DotSPF
ID: 12615797
Sorted
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question