SQL Server Single User Mode Connection with SQL Server Management Studio


By:   |   Updated: 2020-09-02   |   Comments   |   Related: More > 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.

single user mode error
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.

sql server configuration manager

Click "Add" and then "Apply" and the following message will appear.

sql server configuration manager

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.

sql server configuration manager

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.

sql server mangement studio

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.

sql server configuration manager

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.

sql server configuration manager

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.

sql server mangement studio

However, we end up getting the same error message again.

single user mode error

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.

sql server mangement studio

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".

sql server mangement studio new query

From this new query window, we will connect to the database.

connect to sql server

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.

query

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.

error

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:



Last Updated: 2020-09-02


get scripts

next tip button



About the author
MSSQLTips author Sergey Gigoyan 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.

View all my tips
Related Resources





Comments For This Article





download





Recommended Reading

Different Options for Query Results in SQL Server Management Studio

Display Line Numbers in a SQL Server Management Studio Query Window

Connect to SQL Servers in another domain using Windows Authentication

Executing large scripts in SQL Server Management Studio with Insufficient Memory Failures

SQL Server Database Diagram Index was Outside the Bounds of the Array








get free sql tips
agree to terms


Learn more about SQL Server tools