| Author |
Topic |
|
walnutacres
Starting Member
7 Posts |
 Posted - 2010-07-29 : 12:12:53
|
| How can we reset/unlock the sa account if that is the only sysadmin account? Can we restore from a backup? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
walnutacres
Starting Member
7 Posts |
Posted - 2010-07-29 : 13:10:28
|
| We know the password and it is the same on our secondary server. Can we restore just the account information or will we need to restore the whole database? |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-07-29 : 13:43:43
|
| you don't have a windows account that is admin? |
|
|
|
walnutacres
Starting Member
7 Posts |
Posted - 2010-07-29 : 13:54:18
|
| We do not use Windows Authentications for the database. I don't think our Windows admin accounts have the necessary privileges. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
walnutacres
Starting Member
7 Posts |
Posted - 2010-07-29 : 14:00:53
|
| We were forced to remove BUILTIN\Administrators by the DoD. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-07-29 : 14:04:59
|
DoD contractor lost the sa pwd? Oh the irony   no offense intended...just having a little fun with ya |
|
|
|
walnutacres
Starting Member
7 Posts |
Posted - 2010-07-29 : 14:07:31
|
| Didn't lose the password. DoD STIG security requirements forced the removal of the user/group. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-07-29 : 14:12:16
|
| oh sa is locked out? |
|
|
|
walnutacres
Starting Member
7 Posts |
Posted - 2010-07-29 : 14:17:02
|
| Yes, sa is locked out. Unless someone knows a back way in, I'm afraid we are going to have to restore the master database from backup. |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-07-29 : 14:55:56
|
| There are several ways to get into a SQL Server 2005 instance as long as you are a local Windows administrator.You can gain access to SQL Server 2005 by adding your Windows login to one of the following local groups on the server , depending on if it is default or named instance (replace ComputerName and InstanceName with the actual names)Default instance: SQLServerMSSQLUser$ComputerName$MSSQLSERVERNamed instance: SQLServerMSSQLUser$ComputerName$InstanceName You can refer to the following article and the related discussion for other methods:How to Connect to a SQL 2005 Server When You Are Completely Locked Outhttp://www.sqlservercentral.com/articles/Administration/68271/CODO ERGO SUM |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-07-29 : 16:18:51
|
quote:
Originally posted by tkizer
Wow, what's the point of locking it down if you can "break-in" like that?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog
There was a lively debate about that on the SSC article discussion, but in the end it just comes down to "that's the way Microsoft did it, so that’s the way it is". It does make it important to secure the Windows server.I'm not giving away any secrets, since Microsoft was kind enough to document the procedure in the article in SQL Server 2005 Books online. The trick of adding your login to the SQLServerMSSQLUser$ComputerName$MSSQLSERVER group is something I came up with myself, but it seems to work, and you do not have to restart SQL Server in single user mode. I have found it's a very convenient way to gain access when someone sets up a SQL Server and forgets to give me access.  CODO ERGO SUM |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2010-07-30 : 18:46:00
|
| Well, I have seen that a lot of people get local administrator access on servers. I've even seen where desktop support were added to servers as local administrators because they were included in one of the groups added to the server.At least with this, they won't be automatically added by any AD GPO's and won't have access by default.Of course, if you are really looking to lock down SQL Server - add the service account directly, disable those groups and you have the same thing. |
|
|
|
walnutacres
Starting Member
7 Posts |
Posted - 2010-07-31 : 08:12:54
|
| Thanks everyone for your help. I was able to unlock the sa account and enable it.However, I still have a problem. My server is set to mixed authentication. I cannot log into the sa account but I can log into another SQL Server Authentication account (both via SQL Server Management). Any ideas on this? |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-07-31 : 15:12:15
|
quote:
Originally posted by walnutacres
Thanks everyone for your help. I was able to unlock the sa account and enable it.However, I still have a problem. My server is set to mixed authentication. I cannot log into the sa account but I can log into another SQL Server Authentication account (both via SQL Server Management). Any ideas on this?
Either you don't know the correct SA password, or the SA login is locked out or disabled.CODO ERGO SUM |
|
|
|
|
SQL Server 2005 sa account problem