Only One Administrator Can Connect At This Time Error 18461
Contents |
Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and microsoft sql server 2012 error 18461 policies of this site About Us Learn more about Stack Overflow the login failed for user . only administrators may connect at this time company Business Learn more about hiring developers or posting ads with us Server Fault Questions Tags Users Badges Unanswered error 18461 sql server 2008 r2 Ask Question _ Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute: Sign up Here's how it works: Anybody can
Microsoft Sql Server 2008 R2 Error 18461
ask a question Anybody can answer The best answers are voted up and rise to the top Can't login to SQL Server in Single User mode up vote 5 down vote favorite 3 I've been trying all sorts of different combinations of startup flags to SQL Server 2008 R2 Express and I can not get past this error: Login failed for user -m"microsoft sql server management studio - query" 'LOCALSERVER\Administrator'. Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461) I've tried starting it up in Single User mode by both -m"SQLCMD" and -m"sqlcmd" and Iv'e even tried to start it up with -m"Microsoft SQL Server Management Studio - Query" and connecting with the appropriate clients I ALWAYS get this error. I'm 99% sure that nothing else is stealing my connection and when connecting with SSMS I make sure to do the "new query-connect` method to ensure it only uses 1 connection. Has anyone else had this problem? How do I fix it and login? sql-server login sql-server-2008-r2 share|improve this question asked Dec 6 '11 at 20:01 Earlz 5025824 add a comment| 3 Answers 3 active oldest votes up vote 7 down vote Here's a solution I just found to this old issue: Stop all SQL services Start with -m Run MSSQL Management Studio As administrator If not started as administrator (as I wasn't), then you'll receive the generic an admin is already connect. Instead of saying you can't connect because you
are Locked Out Read Comments Introduction SQL Server Security is one of the key responsibilities of a Database Administrator. However, sql server is in single user mode there can be scenarios when a DBA will be asked to manage
Sql Server Single User Mode To Multi User Mode
SQL Server which doesn’t have any valid System Administrator or SA Accounts. This usually happens when a DBA
Sql Remove Single User Mode
who was managing the SQL Server Instance has left the job or SA account is disabled or when no one remembers the SA password or even when accidentally someone http://serverfault.com/questions/338290/cant-login-to-sql-server-in-single-user-mode has removed the SA Privileges. This article explains the steps to follow to add a new login with SA privileges. The steps mentioned in this article are applicable to all editions of SQL Server 2005 and new releases. Let us go though the steps which will help you regain SA privileges on SQL Server where all System Administrators http://www.mytechmantra.com/LearnSQLServer/Steps-to-Connect-to-SQL-Server-When-all-System-Administrators-are-Locked-Out/ are locked out. Starting SQL Server in Single User Mode 1. Click Start -> Programs -> Microsoft SQL Server -> Configuration Tools -> SQL Server Configuration Manager 2. In SQL Server Configuration Manager, Stop the SQL Server Service (Database Engine). 3. Once SQL Server Service is stopped, right click the SQL Server Service on the right side panel and click on Properties. 4. In SQL Server 2008 R2 and below versions within the SQL Server Properties screen, click on Advanced Tab. Click and expand Startup Parameters and enter “;-m” as show in the snippet below. To save the configuration changes click OK. 5. In SQL Server 2012 and higher version Startup Parameters screen has improved and it’s now much easier to add or remove SQL Server Startup options. In the Startup Parameters tab add the “-m” parameter and click OK to save the changes. For more information on this see, Improved Startup Parameters in SQL Server 2012. For more information on adding trace flags for example to identify deadlocks issues see, Identify Deadlocks in
log in tour help Tour Start here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn more about hiring developers or http://dba.stackexchange.com/questions/50797/cannot-use-sqlcmd posting ads with us Database Administrators Questions Tags Users Badges Unanswered Ask Question _ Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute: Sign up Here's how it works: Anybody can ask a question Anybody can answer The best answers are voted up and rise to the top Cannot use SQLCMD up vote 4 down vote favorite sql server 1 I have started SQL Server in single user mode like this: c:\....\sqlservr -m When I try to connect to it with SQLCMD -E, I get the following error: Msg 18461, Level 14, State 1, Server SERVERNAME, Line 1 Login failed to user 'USER\Name'. Reason: Server is in single user mode. Only one administrator can connect at this time. The user I am logged onto the server as is a domain administrator. Before I tried this, the user I am logged on as microsoft sql server had nothing to do with this SQL server. (I am in fact, trying to create a new SA as we don't know what the current password is) Edit It seems that even if I do: c:\....\sqlservr -m"sqlcmd" I am still getting the above error. I am not sure if there are other SQLCMD calls happening from elsewhere. sql-server sql-server-2008-r2 sqlcmd share|improve this question edited Oct 2 '13 at 8:01 asked Oct 1 '13 at 9:57 rhughes 1911211 Being a domain admin might not have any effect on login rights. Since SS2008, local administrators group is no longer added into to sysadmin role without user interaction. –vonPryz Oct 1 '13 at 12:32 add a comment| 2 Answers 2 active oldest votes up vote 7 down vote accepted This case is explicitly called out in Connect to SQL Server When System Administrators Are Locked Out: When you start an instance of SQL Server in single-user mode, first stop the SQL Server Agent service. Otherwise, SQL Server Agent might connect first and prevent you from connecting as a second user. When you use the -m option with sqlcmd or SQL Server Management Studio, you can limit the connections to a specified client application. For example, -m"sqlcmd" limits connections to a single connection and that connection must identify itself as the sqlcmd client program. Use this option when you are starting SQL Server in single-user mode and an unknown client application is taking the only available conn