hapy3sum
asked on
Resetting Unknown SA password
New to SQL Server and coming in as the new DBA (w/an Oracle background).
Previous DBA used Windows authentication in the old office location. When our office moved to a new location, the Network Admin backed up the image of SQL Server 2000 from an old machine to a new server when we transferred to the new location. The Network Admin gave me local as well as domain admin rights to login to the new instance of SQL Server on the new server. Logging in, I was able to register the SQL Services and can access and see some of the databases running on SQL Server, but I am obviously not coming in as a SYSADMIN. The problem is, I cannot even go to the registry and change my authentication from Windows-based to Mixed mode since I do not know what the SA password is (the previous DBA cannot be reached and nobody here knows what the SA password is). So, what other options do I have?
Appreciate the expert help ASAP as I need to start managing our databases and I cannot even get in as an "sa" to do my job.
Previous DBA used Windows authentication in the old office location. When our office moved to a new location, the Network Admin backed up the image of SQL Server 2000 from an old machine to a new server when we transferred to the new location. The Network Admin gave me local as well as domain admin rights to login to the new instance of SQL Server on the new server. Logging in, I was able to register the SQL Services and can access and see some of the databases running on SQL Server, but I am obviously not coming in as a SYSADMIN. The problem is, I cannot even go to the registry and change my authentication from Windows-based to Mixed mode since I do not know what the SA password is (the previous DBA cannot be reached and nobody here knows what the SA password is). So, what other options do I have?
Appreciate the expert help ASAP as I need to start managing our databases and I cannot even get in as an "sa" to do my job.
You might look in EM at the logins under security and see if there are any windows accounts that you have access to that are set up as sql server admins.
ASKER
Mastoo,
Checked every login account in EM and no account has 'sa' roles. I just asked the same question to Duane but wanted your feedback as well ---- what about reinstalling SQL Server in my situation? Would SQL Server reattach easily to the databases currently in the RDBMS if I did reinstall? And if so, what are the steps for it?
Thanks.
Checked every login account in EM and no account has 'sa' roles. I just asked the same question to Duane but wanted your feedback as well ---- what about reinstalling SQL Server in my situation? Would SQL Server reattach easily to the databases currently in the RDBMS if I did reinstall? And if so, what are the steps for it?
Thanks.
Isn't the previous dba's windows account showing up as a server admin (look at the login properties in EM)? Is so you could have your network/server admin guy reset the dba's windows password and then come in as him. That would let you be a sql server admin and you could change it to mixed mode security and se up sa as desired. You are saying that the server is using windows security only and not mixed mode currently?
But it might be faster to do as you describe. Detach your databases or back them up, uninstall sql server, install sql server, and then attach or restore your databases.
But it might be faster to do as you describe. Detach your databases or back them up, uninstall sql server, install sql server, and then attach or restore your databases.
ASKER
Mastoo,
I think the problem is that when we moved offices (and changed domains), the Network Admin created an image of the server and recovered it in our SAN here in the new office. So in essence (at least from my opinion), we broke the connection to the SQL Server db. Here are some items of interest:
1) I cannot see a BUILTIN group that supposedly is created by SQL Server as the OS group that has the SA role (if you are using Windows-based authentication, right?). So this means that the SA account got deleted, right?
2) I tried already what you suggested during the early stages of troubleshooting the problem, but since the SAN is now on a different domain, creating a user account for the old DBA account with the SA role (even if I was doing it with both domain and local admin rights) and using it for the purpose of trying to login as the Old DBA does not work.
3) I can see SOME of the databases and objects underneath when using EM, but I know that I am not coming in as an sa since I cannot even see the properties of each login account, or even create a new user for that matter.
4) I tried the SP_PROCEDURE to reset the "sa" account to a new password, but then again I can't since I do not have an "sa role".
If I were to reinstall, is there a white paper, or would you mind sharing some steps to make sure that I do it properly (aside from making sure to make an O/S backup of the machine as it is now)?
Thanks so much --- really in dire need of help ASAP.
I think the problem is that when we moved offices (and changed domains), the Network Admin created an image of the server and recovered it in our SAN here in the new office. So in essence (at least from my opinion), we broke the connection to the SQL Server db. Here are some items of interest:
1) I cannot see a BUILTIN group that supposedly is created by SQL Server as the OS group that has the SA role (if you are using Windows-based authentication, right?). So this means that the SA account got deleted, right?
2) I tried already what you suggested during the early stages of troubleshooting the problem, but since the SAN is now on a different domain, creating a user account for the old DBA account with the SA role (even if I was doing it with both domain and local admin rights) and using it for the purpose of trying to login as the Old DBA does not work.
3) I can see SOME of the databases and objects underneath when using EM, but I know that I am not coming in as an sa since I cannot even see the properties of each login account, or even create a new user for that matter.
4) I tried the SP_PROCEDURE to reset the "sa" account to a new password, but then again I can't since I do not have an "sa role".
If I were to reinstall, is there a white paper, or would you mind sharing some steps to make sure that I do it properly (aside from making sure to make an O/S backup of the machine as it is now)?
Thanks so much --- really in dire need of help ASAP.
I have to leave for lunch, but my 10 second answer is that I'm sure there are all sorts of tips out there on installing. I'll oversimplify due to my rush but some main things to consider before starting are backup before you start, how you'll configure licensing, using integrated or mixed security, where to install the databases and transaction log files and backup files, what kind of network protocols you'll use with sql server, might as well install current patch levels while you're at it, collation designator (mainly do you want case sensitive or not), and whether you run sql server and sql agent under the local system account or not (if you're not sure then use local account). Now for those White Castles...
ASKER
Mastoo,
I do appreciate your "10-second" answer; when you come back from your "White Castles" lunchbreak (and you have a moment), if you can kindly post a more detailed (and your recommended) resolution to my "bleak" scenario, this would be greatly appreciated.
Thanks so much!
I do appreciate your "10-second" answer; when you come back from your "White Castles" lunchbreak (and you have a moment), if you can kindly post a more detailed (and your recommended) resolution to my "bleak" scenario, this would be greatly appreciated.
Thanks so much!
Short answer
Yes
Long answer
I have detached many databases and copied them to different servers and reatached them with no problem. Detaching from the current server, uninstalling and then reinstalling should be no problem.
I have even detached a MS SQL Server 2000 32 bit database and attached it to a MS SQL Server 2000 64 bit and had no problem, it is still running. Matter of fact it is in use now.
I have not attached a 2000 db to a 2005 beta 2 copy. I must do that, that would be sooo cooool to know. I will try it tonight at home, and post what happens here.
Duane
Yes
Long answer
I have detached many databases and copied them to different servers and reatached them with no problem. Detaching from the current server, uninstalling and then reinstalling should be no problem.
I have even detached a MS SQL Server 2000 32 bit database and attached it to a MS SQL Server 2000 64 bit and had no problem, it is still running. Matter of fact it is in use now.
I have not attached a 2000 db to a 2005 beta 2 copy. I must do that, that would be sooo cooool to know. I will try it tonight at home, and post what happens here.
Duane
ASKER
Hi Duane,
While "Mastoo" is on his "White Castles" break, can you recommend in detail any links that I can review on how to reattach SQL Server to existing databases?
Thanks.
While "Mastoo" is on his "White Castles" break, can you recommend in detail any links that I can review on how to reattach SQL Server to existing databases?
Thanks.
I'm back. The detach and reattadh is the trivial part if you are going the reinstall route. You can even just do it from EM by right-clicking on a database to detach (under all tasks), or right click Databases to get to attach under all tasks. If there's any question that you won't be reinstalling the same version of Sql Server you'd be safer to back up the databases and then just restore them after the reinstall. Since nobody else has leaped in with an alternate solution I presume you're still headed down the reinstall path?
ASKER
Hi Mastoo,
Thanks for the feedback (hope the "White Castles" was good). Yes, as you presumed, I am now headed towards the "reinstall" path. We are on SQL Server 2000.
I was trying to check out some docs and/or links on how to properly (and safely) reinstall and reattach old databases ... do you happen to know of a good doc and/or link that you can pass along to refer to --- will appreciate it very much as I will be doing this first thing Monday AM.
Thanks a lot!
Thanks for the feedback (hope the "White Castles" was good). Yes, as you presumed, I am now headed towards the "reinstall" path. We are on SQL Server 2000.
I was trying to check out some docs and/or links on how to properly (and safely) reinstall and reattach old databases ... do you happen to know of a good doc and/or link that you can pass along to refer to --- will appreciate it very much as I will be doing this first thing Monday AM.
Thanks a lot!
I can't recall seeing one on your situation where you'd really like to just reinstall with exactly the same settings you have now, other than security.
Disclaimer: if the server has replication going, clustering, log shipping, analysis services, or possibly other things I'm forgetting, you'll want something more than my cursory explaination.
If you have Sql Agent jobs that you don't want to lose, you need to backup your msdb database and restore it later.
The key is to capture your configuration info so you'll have it available when you reinstall. Go to server properties in EM, copy the General tab and the startup parameters and network configuration on that tab, the security tab, the database settings tab, and the server settings tab. Detach (backing up first might be good) your databases, uninstall, install, look at the server properties to be sure they match, and then attach or restore your databases. Magic. Be sure you get the right collation on the install (this is on the general tab that you copied).
Disclaimer: if the server has replication going, clustering, log shipping, analysis services, or possibly other things I'm forgetting, you'll want something more than my cursory explaination.
If you have Sql Agent jobs that you don't want to lose, you need to backup your msdb database and restore it later.
The key is to capture your configuration info so you'll have it available when you reinstall. Go to server properties in EM, copy the General tab and the startup parameters and network configuration on that tab, the security tab, the database settings tab, and the server settings tab. Detach (backing up first might be good) your databases, uninstall, install, look at the server properties to be sure they match, and then attach or restore your databases. Magic. Be sure you get the right collation on the install (this is on the general tab that you copied).
ASKER
Mastoo,
Other than authentication (I am going to go mixed mode), I will most likely reinstall with the same settings as before.
Other items of interest after your last feedback:
1) No server replication, clustering, log shipping or analysis services going on.
2) I do have some SQL Agent jobs running - my question then is, how can I backup my db and restore it later when I don't have sysadmin-type access?
3) Can I detach a database I do not have sysadmin-type access to?
4) Will I be able to attach or restore my databases, again, if I did not have sysadmin-type access to those databases before ?
Thanks so much and will await your much-appreciated reply!
v/r
Hapy3sum
Other than authentication (I am going to go mixed mode), I will most likely reinstall with the same settings as before.
Other items of interest after your last feedback:
1) No server replication, clustering, log shipping or analysis services going on.
2) I do have some SQL Agent jobs running - my question then is, how can I backup my db and restore it later when I don't have sysadmin-type access?
3) Can I detach a database I do not have sysadmin-type access to?
4) Will I be able to attach or restore my databases, again, if I did not have sysadmin-type access to those databases before ?
Thanks so much and will await your much-appreciated reply!
v/r
Hapy3sum
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Mastoo,
I will go ahead and do as suggested ... we have the old image of our SQL Server box, so this is our backup for now. Since I am not logging in with "sa"-type rights, I could only start or stop jobs but cannot really edit or see it.
Thanks to you and Duane for your feedback, and will let you both know how this goes.
Hapy3sum
I will go ahead and do as suggested ... we have the old image of our SQL Server box, so this is our backup for now. Since I am not logging in with "sa"-type rights, I could only start or stop jobs but cannot really edit or see it.
Thanks to you and Duane for your feedback, and will let you both know how this goes.
Hapy3sum
It'd be nice if you have backups or can detach databases first. These you can easily restore.
If not, it would be preferable to take a image with sql server stopped because you end up having to trick sql server into using the databases by copying the files. It usually works but you don't want to have an image where sql server happened to be in the middle of updating a file.
If not, it would be preferable to take a image with sql server stopped because you end up having to trick sql server into using the databases by copying the files. It usually works but you don't want to have an image where sql server happened to be in the middle of updating a file.
IF I HAD A DIME FOR EVERY TIME THIS BIT ME:
A word of warning, when you change it, anything that is hard coded to use the old sa password will stop working. You will have to run around in emergency repair mode for a while. Not a comfortable situation. A better way would be to get the source code for everything that uses the db and search for the string 'sa', then read the password.
Duane