By: Sergey Gigoyan | Updated: 2020-09-02 | Comments (1) | Related: > SQL Server Management Studio
Problem
Sometimes it's necessary to start SQL Server in single-user mode, like for recovering system databases or changing server configuration options.
In single-user mode, only one user from the administrator's group of the server can connect as a sysadmin fixed server role member. To connect and execute T-SQL commands in single-user mode, both SQL Server Management Studio (SSMS) and sqlcmd utility can be used. However, many users are used to using a graphical interface and feel more comfortable with SSMS rather than working with the command line. In turn, using SSMS in single-user mode can present some difficulties. In this article, we are going to describe and solve a specific problem when a user connects to the server in a single-user mode from SSMS and receives the error that only one user can connect in single-user mode as shown below.
Solution
If you're the only one connecting to SQL Server why would you still get this error "Login failed for user... Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461)".
Let's reproduce the error and then suggest a solution. Let's assume that we need to connect to the SQL Server Service in single-user mode to perform a specific operation which is only possible in this mode, such as restoring the master database.
Put SQL Server Instance in Single User Mode
To start SQL Server in the single-user mode, we add the corresponding startup parameter -m using "SQL Server Configuration Manager".
To do this, open "SQL Server Configuration Manager", choose "SQL Server Services", then choose the corresponding SQL Server instance, right-click on it and choose "Startup Parameters". As a startup parameter, we specify "-m" that means that the service will start in single-user mode.
Click "Add" and then "Apply" and the following message will appear.
As we can guess, this means that the service restart is required to start the server with the changed option. So, we click "OK" on the warning window and then "OK" to close the "Properties" window. Then, we restart the SQL Server service.
Connect to SQL Server in Single User Mode with SSMS Error
After the restart, open SSMS and try to connect. We receive the same error.
Thus, it is clear from the error message that there is an existing connection to the SQL Server and therefore other connections are not possible.
Let's understand which service could make a connection to the SQL Server. When we open "SQL Server Configuration Manager", we can see that the "SQL Server Agent" service is running.
The agent could be using the only available connection to the SQL Server, so let's eliminate this and stop the agent and restart the SQL Server again.
After stopping SQL Server Agent, we try to connect again with SSMS and now we are able to connect to the SQL Server.
Restore Master Database with SSMS in Single User Mode
Now that we are connected via SSMS in single user mode, let's try to restore the master database.
Restore with SSMS GUI
We execute a restore as normal via SSMS as shown below.
However, we end up getting the same error message again.
The reason for this is that "Object Explorer" has already made a connection and in turn the restore task tries to initiate a new connection and therefore fails to connect.
Restore with a Query Window
Well, let's try to restore using T-SQL code instead of the visual interface.
To perform this, we try to open a new "Query Window", but this also fails, because it tries to open a new connection.
Hence, we understand that we need to eliminate all possible connections to the SQL Server to be able to connect in the single-user mode. As we saw, SQL Server related services, such as SQL Server Agent, could use this one connection. Additionally, the "Object Explorer" and a "Query Window" also issue a new connection to the server.
Making a Successful Query Window Connection
Note: you could restart SQL Server again to make sure there are no remaining connections.
To avoid multiple connections, we will open "SQL Server Management Studio", but without connecting to the server. In other words, we will close the "Connect to Server" window, and also close "Object Explorer".
Then we will click on "New Query".
From this new query window, we will connect to the database.
At this time, the connection is successful. This means that we managed to avoid multiple connections to the server and kept the single connection for our query window.
Now, we can execute our T-SQL restore command in the query window and restore the master database.
Hence, our problem is solved – we used the only connection in single-user mode to connect to the server from SSMS.
Other Notes
It is important to mention that we can use –m<Client Application Name> startup option to allow only the specified application to connect in single-user mode. So, -m"Microsoft SQL Server Management Studio - Query" can be used as a startup parameter to connect in single-user mode only from the "Query Editor".
Having said that, this startup option cannot be used with "SQL Server Configuration Manager" because it uses a dash character which is not allowed in this tool, so this can only be done via the command line to start SQL Server this way.
As this article is aimed at helping those who prefer to use SSMS instead of the command line, we have discussed the option which allows us to change the configuration from the "SQL Server Configuration Manager" visual tool.
Conclusion
It is possible to connect to SQL Server in single-user mode only if there are no other connections to the server. Therefore, it is important to be sure that all services that can connect to SQL Server, such as SQL Server Agent, are stopped. Additionally, in the case of connecting from SSMS, it is essential to eliminate all other connections from SSMS. Closing the "Object Explorer" and connecting by opening just one query window can be a solution.
After this, you can remove the startup parameter and restart SQL Server in multi-user mode.
Next Steps
Additional topic-related information can be found in the following links:
- Start SQL Server in Single-User Mode
- Database Engine Service Startup Options
- Start, stop, pause, resume, restart SQL Server services
About the author
Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2020-09-02
