Solved

Incorrect sa password when installing MSSQL 2000 service pack 3a

Posted on 2004-09-10
6
405 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
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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 500 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

708 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now