The Problem
I came across a strange and rare situation at a client recently, where they asked me to resolve a problem on Microsoft SQL Server but did not give me any credentials to connect to the system. I asked them to state the actual problem and the reply was “we lost all credentials”. A number of questions started to generate in my mind. How are applications running? The Answer was “The application login is embedded in a DLL and it’s a third party application which we don’t have support”. How can you lose all the SQL Server credentials? The Answer “It was saved in a file and it was lost and the employee who knew it is no more working with the company”. Anyways, I had no choice but to have a solution for this strange problem.
The Solution (Concept)
Luckily, we have a solution to still log in to the Microsoft SQL Server even if we do not have a SQL or Windows Login. For the solution to work perfectly fine, you need to be a local admin on the server and have access to the SQL Server Configuration Manager. If you have the right amount of privileges on the system then you do not have to worry, just sit back and understand what I will explain here.
As soon as you implement my solution, you will be able to connect to the SQL Server (to which you do not have access to), Create new login and assign SYSADMIN role to the newly created login.
The Implementation
Let us start with implementing the solution. You have a situation where you do not have SQL Server login access to an Instance neither SQL nor Windows Authentication is working.
First, you need to have local admin access on the system (your windows login should be a member of the Administrators Group) on which the problematic SQL Server Instance is running. Log in to the system and access the SQL Server Configuration Manager.
Next you need to start the SQL Server Instance in “Single User” mode. To do this, you need to open the properties window of the particular instance in SQL Server Configuration Manager.
The Startup options are powerful ways for the initiation of the SQL Server Instance. There are plenty of options in SQL Server Instance, which we can use in different kind of situations to start the instance in normal or some special mode. Sometimes we need to start SQL Server in a special mode for some specific reason (like the one we are using) and at times, we need to add some additional functionality to the instance like Trace Flags.
To know more about SQL Server Traces please visit the link here. I will not explain the trace flags here as it is a separate topic and it is much more advanced and needs a detailed explanation.
In the Configuration Manager, Click on the “Startup Parameters”. Here you can add parameters to startup so we will be using the “-m” parameter which will start the Instance in “Single User Mode”. When supplied with the “-m” parameter, SQL Server instance will start in a single user mode. Any user, who tries to connect to SQL Server in this mode and succeeds, will be the only user for that instance. This mode is powerful, so only experienced DBAs should use it as you can end up in having no connections to the SQL Server.
Keep in mind the all the applications, which are connected to this instance, should be disabled before you proceed with restarting the SQL Server. This is necessary because if the application tried to connect and succeeds, then it will be the only connection to the instance and you will not be able to log in.
As shown in the below snapshot, you will add the parameter and apply the changes. You need to restart the instance for the changes to be applicable.
Please see the snapshot below for reference.
Next, you need to start SQLCMD utility to connect to the instance. To do this, you need to connect to the CMD in Administrator Mode and type the command mentioned.
To start the command prompt you can go to the start menu and type “CMD”. When you find the Command Prompt, right click on the application and launch it with “Run as Administrator” mode. You will see the mentioned below command prompt.
Now, to connect to the SQL Server, you need to enter the command mentioned below with –S parameter and mentioning the Instance name to connect. SQLCMD is a great utility to work with SQL Server in command line. I often use it to perform multiple activities, so do have a look at this utility by going to the link here.
Command: SQLCMD -S MUSAB-AIO\SQL2016
As of now, you will have the instance in single user mode and you will be the only user connected to the SQL Server. If you receive an error that you cannot connect to SQL Server then there might be other users trying to connect as well and one of them has the session. In that case, you need to restrict the other users first and restart SQL Server Instance.
If you have successfully logged into the instance then next step would be to create the login. The new login will use SQL Authentication and will be identified by the password you will specify.
Now, create a login and assign the rights to the SQL Server SYSADMIN role to the new user using the mentioned below TSQL.
Note: The solution mentioned here is only applicable if you have SQL Server Authentication enabled.
|
1 2 3 4 |
CREATE LOGIN mynewadmin WITH PASSWORD=N'mystrongpassword'; GO |
The above command has created a user “mynewadmin” which is identified with the password “mystrongpassword”. Please always use a strong password (not the one which I provided, it is not strong) which should include alphabets, symbols and numeric as well.
|
1 2 3 4 |
ALTER SERVER ROLE [sysadmin] ADD MEMBER [mynewadmin]; GO |
The above-mentioned command added the SYSADMIN role to the newly created SQL Server login. If the command executed successfully, then you are almost done and you have to disconnect the SQLCMD now.
Type “exit”, press enter to exit from SLQCMD, and close the window.
Now you have created a new SQL Server Authentication login with the SYSADMIN role so we will connect to the instance. However, before going further, we need to revert the changes done on the instance level i.e. remove the –m startup parameter and restart the SQL Server Instance.
After the restart, you can connect to the SQL Server Instance using the new credentials and perform all the SYSADMIN activities.
After you connect to the instance, you can go and verify that the newly created login is a SYSADMIN login. Please see the snapshot below for reference.
For the instance for which you have lost the credentials, this is the easiest and quickest way to connect and recover. Secure the logins after you have connected to the server. Also, keep in mind that this is the power of the local administrator, so, you must make sure not to assign local admin rights to unnecessary users.
He has 9+ Years of Database Development & Administration experience with Medical Billing, Startup & Financial Companies. He is a Microsoft Certified Expert for Data Platform (SQL Server 2012/2014).
Currently, performing duties as Senior SQL Server Consultant at multiple Saudi Private and Governmental Organizations.
View all posts by Musab Umair
- How to identify slow running queries in SQL Server - May 31, 2017
- Adaptive Query Processing in SQL Server 2017 - April 28, 2017
- What is the SQL Server Virtual Log file and how to monitor it - April 26, 2017