Source.
Working at Microsoft provides many opportunities to receive pearls of
wisdom from individuals at other parts of the organization.
Saleem Hakani
sent along this little gem a few days ago and I wanted to share it with
you. It outlines how to recover an SA password if you forgot it –
though you do need to shutdown your SQL Server instance and start it in
maintenance mode to accomplish this.
What I have done in the past as well is recommend that customers
write the SA password down, put it in an envelope and put that envelope
in a safe that requires two people to get authorized access to. This
way no one person can get the SA password (two are required) and you
don’t need to shut down the server to get it, if needed.
Enjoy!
Here’s the scenario:
You
are a proud and a trusted DBA of your organization who is responsible
for some important services running on SQL Servers in the production
environment. To prevent any unauthorized access to your production
environment, you have decided to perform the following steps that are
kind of best practices to secure your company’s SQL Servers from any
unauthorized access:
þ You have removed any and all built-in administrators account from SQL Server logins.
þ You
have removed all users (except SA) that were part of SYSADMIN server
role (Including any Windows Accounts and/or SQL Server logins)
þ You have set the password of SA account to something extremely complex which would be hard for anyone to guess or remember.
þ For
day-to-day operations on SQL Server, you use your domain user account
which has DBO permissions on user databases but doesn’t have SYSADMIN
privileges on the system.
þ You
have not documented the SA password anywhere to prevent others from
knowing the SA password. (Also, it’s not a good practice to document the
password)
Since
you have set the SA password to be complex and you have been using your
domain account and not the SA account for all your daily database
related activity on SQL Server, the unthinkable has happened “you forgot
your SQL Server’s SA password”. You are the only person who knew the SA
password in your team and now you don’t remember what it was and you
need to make some server level configuration changes to your production
SQL Server boxes.
What would you do now? Here are some of the options I think you may do:
1. You will try logging in as SA with all possible passwords you have in your mind.
2. You
will look for the SA password on your computer hard-drive or in your
emails (If you had stored it in some file which is a bad practice)
3. Try
to restore MASTER database from database backup. However, this will not
help because you will run in to the same issue as you don’t remember
the SA password.
4. Rebuild
Master database. This may not help as you will lose all system/server
level configurations and settings including logins, permissions and any
server level objects.
5. Re-install
SQL Server 2012 and attach all user databases. This may not work as you
may experience same issues that you would experience with #4.
All
your attempts to login in to the system using SA password have failed
and now it is time for you to call up the world’s best product support
services team “Microsoft PSS”
Here’s what you can do:
Many
folks have asked me about this and today, I’d like to share with you a
backdoor to SQL Server 2012 which would help you gain SYSADMIN
access to your production SQL Servers. However, that would mean your
Windows account will need to be a member of the local administrators
group on Windows Servers where SQL Server 2012 Services are running.
SQL Server allows any member of Local Administrators group to connect to SQL Server with SYSADMIN privileges.
Steps to take control of your SQL Server 2012 as an SA:
1. Start
the SQL Server 2012 instance using single user mode from command prompt
by launching the command prompt as an administrator. (Note: You can
also start SQL Server 2012 using minimal configuration which will also
put SQL Server in single user mode)
2. From the command prompt (Run as Administrator) type: SQLServr.Exe –m (or SQLServr.exe –f) and let the SQL Server 2012 database engine start. Make sure you do not close this command prompt window.
You
can locate SQLServr.exe in the Binn folder of your environmental path.
If you don’t have SQL Server 2012 Binn folder in your environmental
path, you can always navigate to the Binn folder of your SQL Server 2012
machine. Usually the Binn folder is located @ C:\Program
Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn>
3. Once
SQL Server 2012 service has been started in single user mode or with
minimal configuration, you can now open up another command line window
as an administrator and use SQLCMD command from command prompt to
connect to SQL Server 2012 instance:
SQLCMD –S <Server_Name\Instance_Name>
Example:
SQLCMD –S “SALEEMHAKANI”
You
will now be logged in to SQL Server. Keep in mind that you are now
logged in as an Admin on SALEEMHAKANI SQL Server 2012 instance.
4. Once
you are logged into the SQL Server 2012 using SQLCMD from the command
prompt, you have the option of creating a new account and granting it
any server level permission.
Let’s create a new login in SQL Server 2012 called “Saleem_SQL” and then add this account to SysAdmin server role.
To create a new login from the command prompt after performing Step 3:
1> CREATE LOGIN ‘<Login_Name>’ with PASSWORD=’<Password>’
2> GO
Example:
1> CREATE LOGIN SQL_SALEEM WITH PASSWORD=’$@L649$@m’
2> GO
To
Once the new login “SQL_SALEEM” has been created, now let’s add this
login to System Admin server role on SQL Server 2012 instance.
From the same command prompt window, execute the following statement:
1> SP_ADDSRVROLEMEMBER ‘<Login_Name>’,’SYSADMIN’
2>go
Example:
1> SP_ADDSRVROLEMEMBER SQL_SALEEM,’SYSADMIN’
2> GO
The above operation will take care of granting SYSADMIN privileges to “SQL_SALEEM” login.
5. Once
the above steps are successfully performed, the next step is to stop
and start SQL Server services using regular startup options. (This time
you will not need –f or –m)
6. Log
in to the SQL Server 2012 management studio or from the command prompt
using “SQL_SALEEM” account and it’s respective password, you now have
system admin access to your SQL Server 2012 instance. You may now reset
the SA password and take control of your production SQL Server boxes.
