Ms Sql Server 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 policies of this site About Us Learn more about Stack Overflow the microsoft sql server 2012 error 18461 company Business Learn more about hiring developers or posting ads with us Server Fault Questions error 18461 sql server 2008 r2 Tags Users Badges Unanswered Ask Question _ Server Fault is a question and answer site for system and network administrators. Join them; -m"microsoft sql server management studio - query" 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 Can't login to SQL Server in Single login failed for user . only administrators may connect at this time 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 '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
Microsoft Sql Server 2008 R2 Error 18461
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 aren't an administrator. Another confusing error from Microsoft. I suppose the same issue will exist with a sqlcmd too Regards Liam share|improve this answer answered Aug 19 '14 at 14:38 Liam Wheldon 7315 2 Running SSMS as Administrator is quite an important point. It wasn't working for me without that. –MrEdmundo Oct 20 '14 at 12:38 add a comment| up vote 4 down vote Despite Microsoft saying otherwise you shouldn't quote af
are Locked Out Read Comments Introduction SQL Server Security is one of the key responsibilities of a Database Administrator. However, there can be scenarios when a DBA will be asked to manage SQL Server which doesn’t have any valid System
Sql Server Is In Single User Mode
Administrator or SA Accounts. This usually happens when a DBA who was managing the SQL sql server single user mode to multi user mode Server Instance has left the job or SA account is disabled or when no one remembers the SA password or even when accidentally someone sql remove 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 http://serverfault.com/questions/338290/cant-login-to-sql-server-in-single-user-mode us go though the steps which will help you regain SA privileges on SQL Server where all System Administrators 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 http://www.mytechmantra.com/LearnSQLServer/Steps-to-Connect-to-SQL-Server-When-all-System-Administrators-are-Locked-Out/ 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 SQL Server Using Trace Flag 1222 and 1204. 6. Once you have added “-m” as a startup parameter, SQL Server Service must be restarted to start SQL Server Database Engine in Single-User Mode. For more information on different startup parameters supported in SQL Server, see Database Engine Service Startup Options. 7. Once the SQL Server Service is restarted successfully, SQL Server will start in Single User Mode. This means at this time only one user connection is allowed to the Database Engine. Hence, you must avoid starting SQL Server Agent Service or using SQL Server Object Explorer in SSMS etc. In case a user connection already exists when SQL Server Database Engine starts in single user mod
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 http://dba.stackexchange.com/questions/50797/cannot-use-sqlcmd more about hiring developers or 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 http://stackoverflow.com/questions/10741187/sql-server-2008-in-single-user-mode-allows-no-connections 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 sql server Cannot use SQLCMD up vote 4 down vote favorite 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 microsoft sql server is a domain administrator. Before I tried this, the user I am logged on as 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 i
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 posting ads with us Stack Overflow Questions Jobs Documentation Tags Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 6.2 million programmers, just like you, helping each other. Join them; it only takes a minute: Sign up SQL Server 2008 in single-user mode allows no connections up vote 5 down vote favorite I don't know the SA password so I restarted my SQL Server 2008 Standard Edition instance in Single-user mode with the hope of changing the SA password. Immediately afterwards I tried connecting with sqlcmd -S serverName\ss2008 Msg 18461, Level 14, State 1, Server serverName\SS2008, Line1 Login failed for user 'serverName\Administrator'. Reason: Server is in single user mode. Only one administrator can connect at this time. My attempts at changing the password right out yields C:\Documents and Settings\Administrator>sqlcmd -U sa -z p@ssword Password: Msg 18470, Level 14, State 1, Server serverName, Line 1 Login failed for user 'sa'. Reason: The account is disabled. Does -m really stand for zero-user mode, or is there something horribly awry with my server? sql-server sqlcmd share|improve this question asked May 24 '12 at 16:05 Blanthor 1,32322946 add a comment| 1 Answer 1 active oldest votes up vote 7 down vote accepted Your connection may be being taken immediately by some other service (e.g. SQL Agent, Reporting Services) You can pass an optional additional client name parameter with the -m switch. e.g. using -m"sqlcmd" Will only permit the single available connection to be taken by a program that identifies itself as the sqlcmd client program. See the docs for more about this. share|improve this answer answered May 24 '12 at 17:13 Martin Smith 263k34416488 3 This is good information. I also made the mistake of not explicitly setting the correct account on the "Log On" tab when I started the service in the Sql Server Configuration Manager. This has to be the same account as you're using with sqlcmd. This might be obvious, but alas not to me. –Blanthor May 29 '12 at 19:40 Also, try running Management Studio as Administrator. I followed this and it works: technet.microsoft.com/en-us/library/dd207004.aspx –Richard Jun 26 '13 at 11:23 add a comment| Your Answer draft saved draft discarded Sign up or log in Sign up using Google Sign up using Facebook Sign up using Email and Password Post as