February 25, 2009 at 2:15 pm
I forgot the password of sa account, therefore I don't be able to login to sql server 2008
I have another account, but with no especial privileges
I know that exists tools that retrieve SQL Server password based on hash algoritm and the value of the column called "password" on the table sysxlogins in SQL Server 2000. (I downloaded a project in Visual Basic from this site called SQLPasswordRetireve that doing this.)
But, in SQL Server 2008, there is no table sysxlogins. I assume that the equivalent table is sys.sql_logins, but here the value for the column "password" displays NULL.
How could I retrieve the password of sa account ?
Thanks in advance
PD
Due to there is no another account with sysadmin server rol, I can not reset the password.
February 25, 2009 at 2:20 pm
There might be a way to hack it, not sure. I know you could stop the SQL service, save your database and log files, unistall SQL and reinstall it. That would allow you to reset it, but it's a bit of overkill, and eliminates anything you have saved in msdb (jobs, SSIS packages, etc.), model, etc.
I use Windows authentication on my servers. That eliminates the need for sa, and I usually don't like to allow mixed-mode authentication. If I do, I put a seriously strong password on sa, record that securely, and then make sure nobody ever uses it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 25, 2009 at 10:07 pm
If you have forgotten it, you might have to simply reset it. One workaround (I haven't tried this) is to try and transfer the old one. In sp_helprevlogin, comment out the "AND p.name <> 'sa'" and see if you can get this to transfer over. It won't give you the password, but it could keep things moving. sp_helprevlogin comments this to make everyone reset the thing on the new installation anyway.
February 26, 2009 at 12:18 pm
Here's what I did when somebody installed the server themselves and didn't remember what they put for the password...
I used the Server Management thingy to log in using windows authentication. Then I could go to the sa account and set the password to something known.
[font="Arial"]Halfbubble ------------
You just can't do this stuff unless you're at least half a bubble off center.[/font]
February 26, 2009 at 2:06 pm
Video (works in SQL Server 2008 as well):
How to recover from being locked out of a SQL Server 2005 database
Basically you stop SQL Server. You restart it in single user mode. Then you connect locally (on the server) with an account that is a local administrator on the server. SQL Server, when running in single user mode, will automatically treat that user as a member of the sysadmin fixed server role.
K. Brian Kelley
@kbriankelley
March 1, 2009 at 7:58 am
Add user to windows group named "SQLServer2005MSSQLUser$ServerName$SQLServerInstanceName" and login to machine with that account and login to sql server and try to change the password for "sa" account. Hope this helps!
Manu
March 1, 2009 at 1:41 pm
MANU (3/1/2009)
Add user to windows group named "SQLServer2005MSSQLUser$ServerName$SQLServerInstanceName" and login to machine with that account and login to sql server and try to change the password for "sa" account. Hope this helps!Manu
Unless you can help it, you should not use these groups. Especially since on a clustered server they aren't local to the server, they are actually at the domain level.
K. Brian Kelley
@kbriankelley
March 3, 2009 at 5:05 pm
Dear Brian:
Thanks for you reply
I watch the video:
"How to recover from being locked out of a SQL Server 2005 database"
but I could not log on the SQL Server
I have a named instance of SQL Server called "SQLSERVER2008"
I started in single mode with:
sqlservr.exe -m -s SQLSERVER2008
then
When I run:
sqlcmd -E -S WIN005\SQLSERVER2008
where WIN005 is the server and SQLSERVER2008 is the named instance
it show:
Login failed for user 'DOMAIN1\user1'. Reason: The account is disabled.
where DOMAIN1 is the windows domain and user1 is my account in that domain.
I thought that the connection with sqlcmd is local, with no domain user.
Have I start windows session in the server with local administrator instead of domain user ?
March 3, 2009 at 5:25 pm
You need to use an account that is a local administrator.
March 10, 2009 at 6:37 pm
Dear Brian:
Thank you very much for your help.
The video is very demonstrative
In my case, it works with few adjustment due to named instance. It works in SQL Server 2008
I added the option -s to command sqlservr.exe . Also, I added -S to command sqlcmd
Finally, I not used [BUILTIN\administrator], but [SERVERxx\administrator]
Best regards
Roger
November 23, 2009 at 11:02 pm
How to retrieve Password in SQL 2005 witout creating/logging windows authentication?
Also how to identify passwords using TSQL or through VB programme
November 24, 2009 at 9:13 am
If you have locked yourself out of SQL Server, look at single-user mode. As far as retrieving passwords? Can't be done. SQL Server stores a hash of the password, not the actual password. You can use a tool like NGS SQuirreL for SQL Server, which will brute force the password, but it's not actually a retrieval.
K. Brian Kelley
@kbriankelley
June 15, 2010 at 7:11 am
Hi Brian,
How do I get your solution to work on a server that is the DC?!
I'm running SQL Server 2008 on Windows Server 2008, and this server is the AD DC, and so there are no local accounts.
I stupidly removed the Administrator login in SQL Server Management Studio, and I don't know the sa password!
Regards,
Barry
June 15, 2010 at 3:22 pm
Apologies Brian, please ignore my previous post. I was able to carry out your procedure using the domain admin account.
It failed the first time because I hadn't closed ALL the SQL Server services and so it was erroring saying there was another user logged in.
I've now back in and all is well with the world!
Many thanks,
Barry
May 19, 2014 at 4:18 am
Change SA Password in SQL Server Using SQL Server Management Studio
1.Connect to SQL Server Using SQL Server Management Studio 2. Expand Security -> Logins -> Right click sa to open up Login Properties in SQl management studio, Login section.
SSMS Expert
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply