?
Solved

Incorrect sa password when installing MSSQL 2000 service pack 3a

Posted on 2004-09-10
6
Medium Priority
?
426 Views
Last Modified: 2008-06-10
I have had a recurring problem with SQL 2000 where sql agent keeps getting stopped at 10:30 each night and interupting the running database maintenance plan.

I wondered whether the Slammer virus might be at work although my up to date Sophos scan doesn't detect it.

I decided it was time to install service pack 3a to protect against Slammer but when I install using SQL user/password verification with user sa and no password, I am informed that the user and/or password can not be verified. I can log into the existing sql database or use query analyser without any problem with the sa log in with no password.

To add to my problems, when I now try to install the service pack, I am informed that "A previous program installation created pending file operations. You must restart the computer before running setup"

I have rebooted the server several times (running Win 2000 server) without sucess and even tried deleting some temporary files in the windows temp directory.

Any suggestions on what I do to resolve both the sa password and previous program installation problems would be most welcome.

Regards
John Dempsey
0
Comment
Question by:John_Dempsey
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 10

Expert Comment

by:AustinSeven
ID: 12024861
I suggest you re-install SQL Server at this point.  

1. Script the things you want to keep - jobs, logins, save DTS packages to file and so on.

2. uninstall SQL Server (control panel | Add remove...)

3. restart server

4. install SQL Server  (suggest you apply a password to SA)

5. restart server

6. install SP3a

7. restart server

8. re-attach databases

9. re-apply step 1 things - jobs, logins, DTS and so on


AustinSeven
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12025144
I agree with austins

but would add point 10

10.  Assign a password to sa!


In addition yo may find that this probelm still occurs when trying to uninstall:
"To add to my problems, when I now try to install the service pack, I am informed that "A previous program installation created pending file operations. You must restart the computer before running setup"

The answer to this is pretty straightforward,   there is a key in the registry called RunOnce  the default value of this key is set to 1 when there is a "pending reboot" during an install    sometimes things get messed up and this flag is left there so you just need to find the RunOnce key and unset the default value.
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12025156
oop didnt read austins "(suggest you apply a password to SA)"  

in that case scrub point 10. but i would suggest that a rewording to "(very strongly recommend you apply a password to SA or prepare to be hacked by every school kid in the world)"
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 1

Expert Comment

by:ratb3rt
ID: 12025188
the quick route could be a combination of the above, i.e. fix the registry entry, apply a password to sa, then try the sp install again - if that fails, then backup/reinstall/recreate....
0
 
LVL 1

Accepted Solution

by:
ratb3rt earned 2000 total points
ID: 12025202
btw, another 'pending file operation' place is:
HKLM\SYSTEM\CurrentControlSet\Control\Session Manager
there may be PendingFile* values in there....
0
 

Author Comment

by:John_Dempsey
ID: 12033733
Thank you to all of you.

A search of the registry did not find any RunOnce keys set but ratb3rt was bang on the button.

There was an entry in HKLM\SYSTEM\CurrentControlSet\Control\Session Manager for a PendingFile. Deleting this fixed both the Pending installation and sa passowrd problems.

SQL SP3 now installed AND a password put on sa.

You are all stars.

John
0

Featured Post

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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 UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

770 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