InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 08:34:11 GMT20http://www.sqlservercentral.com/Forums/Topic812701-471-1.aspxHello Everyone,Just wanted to comment on the security aspect of this article.The only purpose of this article is to help DBA's to get into a SQL server that the access has been removed, tampered, password changed, etc. That's it. Yes this shows that SQL server is not the safest application in the world (nether is any other database vendor out there) but if you are only relying on SQL server for all your security needs then I feel sorry for you.There are many applications/appliances our there to help with security. I personally recommend Secure Sphere from Imperva. This appliance acts as a SQL firewall and records anything and everything you do remotely and/or from the console with no impact on performance. You really need to look else where for additional help on security and compliance.If someone has physical access to the SQL server then you have much bigger problems then worrying about logging into SQL server.So do yourself a favor, talk to your security analyst, your boss, etc. and get proper security for all your SQL servers. Don't just rely on the functions built-in to SQL server and/or the OS or you will be sorry.Just my 2 cents worth,RudyFri, 18 Mar 2011 07:06:50 GMTRudy Panigashttp://www.sqlservercentral.com/Forums/Topic812701-471-1.aspxAs per my opinion, we have two kinds of logins 1) local computer logins2) sql server loginsSo according to given scenario we are totally logged out of sql server logins and we cannot login there and neither we can make any changes into sql server configuration like to enable DAC etc. connecting through it.so recommended solution is connect the instance through single user mode(.exe-mode).Thu, 17 Mar 2011 00:43:14 GMTyuvi50http://www.sqlservercentral.com/Forums/Topic812701-471-1.aspxI think the SOX issue is overblown, without using encryption you simply can't keep someone who has administrative access to the server away from the database data. We can add layers such as taking out the built-in admins but I just don't think this is truly an issue. Think about it for a second... An admin could stop the sql server and copy the database files and then attach them elsewhere and read the data, this is true for most database products. What sox requires is due diligence and due care to prevent access it also demands some auditing. So if we had our servers setup with a good amount of auditing we would see a server stop (system log and the errorlog) and the files being read by a user that is not usual (security log) then a server restart (system log and the errorlog). In the shops that I have worked in that had SOX/JSOX compliance requirements all prod server stops/starts needed to be reviewed. If an admin were to follow these steps it would be visible. I was told a story about a staff member who did some looking around on the network, that isn't what burned him, it was the altering of the logs to hide that he was looking.. For the same reason I don't know EXACTLY what logging/auditing my server team is doing, they are not privy to all that the DB team is doing, nor would most know how to circumvent it.SOX/JSOX doesn't demand the controls be absolute, just that when they can't be that auditing and reviews of the logs are done.In the cases where we have highly personal PII then encryption is appropriate, this effectively negates all the rest of what I was talking about, your admin might have the DB and a lot of data, but he can't decode it all..CEWIIWed, 16 Mar 2011 23:16:35 GMTElliott Whitlowhttp://www.sqlservercentral.com/Forums/Topic812701-471-1.aspxThere are many different scenarios, but if what you need is access to the database itself, then you can always attach a copy of the mdf file(s) to a SQL Server instance that you do have a working SYSADMIN account on. This would be useful in a scenario where an abandoned or inherited SQL Server instance contains some needed data or code.Wed, 16 Mar 2011 07:44:52 GMTEric M Russellhttp://www.sqlservercentral.com/Forums/Topic812701-471-1.aspxIf you cannot connect because the error msg is stating that there is already a connection, then you have to first see what application is still running and stop it before you can proceed.When SQL server is running in single user mode it can still accept an application's connection and therefore not allowing you to connect.See previous posts as someone else was having the same issue.Before starting check your running processes and stop all application related processes then start SQL server in single user mode. You should be fine.Hope this helps,RudyTue, 15 Mar 2011 07:11:25 GMTRudy Panigashttp://www.sqlservercentral.com/Forums/Topic812701-471-1.aspx[quote][b]Olalekan Omodara (3/15/2011)[/b][hr]It did not work for me, I have only one user Builtin\Administrator as member of public. I started the SQL server in single user mode. Open another window and typed SQLCMD -E and I got this error msgC:\Documents and Settings\Administrator.XXXXXXX>SQLCMD -EMsg 18461, Level 14, State 1, Server XXXXX, Line 1Login failed for user 'xxxxxx\Administrator'. Reason: Server is in single user mode. Only one administrator can connect at this time.[/quote]Hello,Are you sure that there is no services or program still working that connects automatically to your server? Because the error message tells that there is already someone connected on your server.You can try to create a batch file that starts sql server in a single command window and just after it starts a SQLCMD in another command window.Normally, it should solve you problemTue, 15 Mar 2011 07:01:33 GMTRegis Marchalhttp://www.sqlservercentral.com/Forums/Topic812701-471-1.aspxIt did not work for me, I have only one user Builtin\Administrator as member of public. I started the SQL server in single user mode. Open another window and typed SQLCMD -E and I got this error msgC:\Documents and Settings\Administrator.XXXXXXX>SQLCMD -EMsg 18461, Level 14, State 1, Server XXXXX, Line 1Login failed for user 'xxxxxx\Administrator'. Reason: Server is in single user mode. Only one administrator can connect at this time.Tue, 15 Mar 2011 05:48:06 GMTOlalekan Omodarahttp://www.sqlservercentral.com/Forums/Topic812701-471-1.aspxHello,I tried the same way to add the builtin\administrators to a SQL SERVER 2008 instance and it works. Thanks a lot for your article, I was facing a simiolar problem in our DEV environment.Note that to connect to a named instance, you have to type: SQLCMD -SServerName\InstanceName,PortNumber -ERegards,Régis MarchalMon, 14 Mar 2011 14:03:43 GMTRegis Marchalhttp://www.sqlservercentral.com/Forums/Topic812701-471-1.aspx[quote][b]Akkare (3/14/2011)[/b][hr]If your windows accout dont have permission how will use sqlcmd and create login ?????/[/quote]Akkare,If you read the article carefully, you will see that by starting the SQL in Single Mode, every member of the Windows Administrators group becomes a sysadmin in SQL automagically.Mon, 14 Mar 2011 08:27:48 GMTMiguelSQLhttp://www.sqlservercentral.com/Forums/Topic812701-471-1.aspxIf your windows accout dont have permission how will use sqlcmd and create login ?????/Mon, 14 Mar 2011 07:46:10 GMTAkkarehttp://www.sqlservercentral.com/Forums/Topic812701-471-1.aspxYes, this is scary and I can' think of any other way to provide a safer back door. If you have ever worked on a SQL server who's DBA was let go or fired then you can see why this is a good thing.Scary can be good.RudyMon, 14 Mar 2011 07:12:41 GMTRudy Panigashttp://www.sqlservercentral.com/Forums/Topic812701-471-1.aspxIt is scary, but requiring local access mitigates it a little. It's a back door that's designed this way, specifically for the people that forget SA or have a rouge administrator. I wasn't thrilled with it at first, but it probably makes some sense to have this.Sat, 12 Mar 2011 13:20:05 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic812701-471-1.aspxUseful article - scary but useful! I didn't realise this was possible.Also works on SQL 2008 R2 by the way...Fri, 11 Mar 2011 17:59:33 GMTJKGhttp://www.sqlservercentral.com/Forums/Topic812701-471-1.aspx[quote][b]MiguelSQL (3/11/2011)[/b][hr]SOLVE IT.It is a problem with the domain trust.So I created a local account, login using the LOCAL account, and was able to start SQL on single mode and connect using CMD.Thanks all for your support.[/quote]Glad we could help, and thanks for letting us know. (At least you should have the SA password now so you can get on to the server and look at/adjust things.)[quote]PS: no idea how to solve that problem with the trust. [/quote]Your Windows Server/AD team will probably have to work on that, it is likely related to delegation and/or SPNs. It might end up being easier to join the server to the actual Domain you want, unless there is a reason for it to be different.Fri, 11 Mar 2011 16:20:35 GMTUMG Developerhttp://www.sqlservercentral.com/Forums/Topic812701-471-1.aspxSOLVE IT.It is a problem with the domain trust.So I created a local account, login using the LOCAL account, and was able to start SQL on single mode and connect using CMD.Thanks all for your support.PS: no idea how to solve that problem with the trust. Now I added a Windows Group for all DBAs, but the DBAs can't connect to the server (get the trusted domain error) their account is not listed on the sysadmin group. Even though they are part of the Windows Group.But that's another story for another post.Miguel SQLFri, 11 Mar 2011 15:48:32 GMTMiguelSQLhttp://www.sqlservercentral.com/Forums/Topic812701-471-1.aspxGood point. I think this will correct the problem you are having.RudyFri, 11 Mar 2011 13:13:38 GMTRudy Panigashttp://www.sqlservercentral.com/Forums/Topic812701-471-1.aspx[quote][b]MiguelSQL (3/11/2011)[/b][hr]My windows ID is part of the Local Administrators GroupWhen I start the server in single mode, and I try to log in, I get the error:Login Failed for user ". The user is not associated with a trusted SQL Server connection.[/quote]That sounds like your account might be in a different domain and that the trust between the two domains isn't working 100%.I would try creating a local account on the server, add it to the local administrators group, then log on to the server using the local account and start SQL Server in single user mode. That way you take all the domain portions out of the picture. Once you have regained SA access you can just delete the local account.Fri, 11 Mar 2011 13:11:08 GMTUMG Developerhttp://www.sqlservercentral.com/Forums/Topic812701-471-1.aspxYes, I am starting SQL using the -m parameter, for single use.I guess I'll give it another try tonight... I'll keep you guys posted.Fri, 11 Mar 2011 12:36:40 GMTMiguelSQLhttp://www.sqlservercentral.com/Forums/Topic812701-471-1.aspxDAC and command prompt are not the same. DAC can be used from Management Studio. If you have it open when you start up the instance, connect with "Admin:" and the server. Note that this usually must be used for a local connection. You cannot be on a remote workstation[url]http://voiceofthedba.wordpress.com/2010/12/28/getting-a-dedicated-admin-connection/[/url]You can also use SQLCMD from a command prompt.And you are starting SQL Server with -m, correct?Fri, 11 Mar 2011 12:32:21 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic812701-471-1.aspxSteve,Yes, I actually tried using the DAC.I had the impression that using the DAC or the command prompt are the same.It is recommended to use the CMD becasue it is easier to ensure that you have only ONE connection, instead of the Studio Mgt that also opens the Object explorer, which already account for one connection.I get another shot tonight and I will try to use the CMD prompt.Thank you allFri, 11 Mar 2011 12:30:01 GMTMiguelSQLhttp://www.sqlservercentral.com/Forums/Topic812701-471-1.aspx[quote][b]jswong05 (11/5/2009)[/b][hr]David is smart. That is exactly right. http://support.microsoft.com/kb/937682 How would this feature pass SOX audit? which is trying to control access.[/quote]This is one of many reasons why SOX compliance needs to include auditing actions by privileged users. Which anyone with sysadmin is. Although I wonder what user shows up in sql trace if you come in this way.Fri, 11 Mar 2011 11:58:41 GMTcdesmarais 49673http://www.sqlservercentral.com/Forums/Topic812701-471-1.aspxDid you start the SQL Server in Single user mode, and are you connecting with the DAC connection?Fri, 11 Mar 2011 10:34:45 GMTSteve Jones - SSC Editorhttp://www.sqlservercentral.com/Forums/Topic812701-471-1.aspxRudy,That's what is so puzzling about this.I am part of the Local Admin groups, but I sill can't access it.At this point, I think the next option would be to nuke it and start over.Thank youFri, 11 Mar 2011 10:15:54 GMTMiguelSQLhttp://www.sqlservercentral.com/Forums/Topic812701-471-1.aspxThanks Rudy,Once I found all the application services and shut them down I was able to add my group to SQl and then login.BillFri, 11 Mar 2011 09:59:45 GMTBill Stuttershttp://www.sqlservercentral.com/Forums/Topic812701-471-1.aspxGreat article thanks for sharing!Some have mentioned that one must still have a local administrator account in order use this technique. Not entirely true because there are boot CD's available that have utilities to reset the local administrator account. So if anyone has physical access and the server can be booted by CD or USB the local administrator account can be reset and the technique from the article used to gain access to the SQL Server instance.It's tough to trump physical access but if enough security and encryption is in place it may be possible but this will result in the original problem. How to gain access if "completely" locked out?Fri, 11 Mar 2011 09:50:47 GMTDave62http://www.sqlservercentral.com/Forums/Topic812701-471-1.aspxI think you need to be part of the domain administrator's group. Check with your network team to see if they can add you until you have connected.Hope this helps,RudyFri, 11 Mar 2011 09:38:51 GMTRudy Panigashttp://www.sqlservercentral.com/Forums/Topic812701-471-1.aspxGreat article - I've been locked out of servers by vendors who won't share the sa password and have had to go through hoops like this just so I could add a link to their server on the SQL server we use for monitoring all the SQL servers at our site.Fri, 11 Mar 2011 09:30:11 GMTCraig A. Silvishttp://www.sqlservercentral.com/Forums/Topic812701-471-1.aspxRudy,Thank you so much for posting this article.I found myself in this situation several times and this trick works well. However I find myself in a case where I cannot recover the SA password (or connect to the server in question)Scenario:MSSQL 2008 EEServer build by a DBA that left the companyI don't have the SA password.There are no SYSADMIN than can be found.[b]Server was build outside the domain, and the it joined the domain.[/b] (not sure if this is important)My windows ID is part of the Local Administrators GroupWhen I start the server in single mode, and I try to log in, I get the error:Login Failed for user ". The user is not associated with a trusted SQL Server connection.Any suggestions?I've been in this situation for weeks and I've been just lucky that the server is running smoothly, but it's a matter of time before it breaks and I'll be in a pickle!Thank youFri, 11 Mar 2011 09:27:31 GMTMiguelSQLhttp://www.sqlservercentral.com/Forums/Topic812701-471-1.aspx[quote][b]Bill Stutters (3/11/2011)[/b][hr]SQL Server is 2005. All SQL default services were shut down and then SQL restarted with -m. There are other SQL instances running on the server. The error states that since SQL is in single user mode, no other administrator can log on. I get this erro in the second command prompt window when the sqlcmd -E command.[/quote]Looks like something or someone else is connected (maybe the application has a service running) and therefore is not letting you in.You should see if that's the case and try again.RudyFri, 11 Mar 2011 08:58:45 GMTRudy Panigashttp://www.sqlservercentral.com/Forums/Topic812701-471-1.aspxi think this instance was installed as part of a vendor application. That could be why it is locked down.Fri, 11 Mar 2011 08:54:56 GMTBill Stuttershttp://www.sqlservercentral.com/Forums/Topic812701-471-1.aspxSQL Server is 2005. All SQL default services were shut down and then SQL restarted with -m. There are other SQL instances running on the server. The error states that since SQL is in single user mode, no other administrator can log on. I get this erro in the second command prompt window when the sqlcmd -E command.Fri, 11 Mar 2011 08:53:32 GMTBill Stuttershttp://www.sqlservercentral.com/Forums/Topic812701-471-1.aspxAre you sure the server is in single user mode? What version of SQL server are you using?No sure why you cannot connect. RudyFri, 11 Mar 2011 08:41:43 GMTRudy Panigashttp://www.sqlservercentral.com/Forums/Topic812701-471-1.aspxWhen I try the sqlcmd -E i get an error saying sql is in single user mode and no one else can login.Fri, 11 Mar 2011 08:25:41 GMTBill Stuttershttp://www.sqlservercentral.com/Forums/Topic812701-471-1.aspxThanks for the info and I'll see what has happened to the images.RudyFri, 11 Mar 2011 08:23:40 GMTRudy Panigashttp://www.sqlservercentral.com/Forums/Topic812701-471-1.aspxIt was a good article. I have not read all the comments but has anyone mentioned that you reference images, but I did not see them in the article?Fri, 11 Mar 2011 07:52:19 GMTShawn Meltonhttp://www.sqlservercentral.com/Forums/Topic812701-471-1.aspxHello and Happy New Year to everyone :)I have just tested this process on a SQL 2008 SP2 (x32 and x64) and it does work. You can also add a windows users instead of the builtin\administrators group and assign it sysadmin rights.I will be testing this soon on SQL 2008 R2 (x32 and x64) environment.Thanks,RudyTue, 04 Jan 2011 07:31:26 GMTRudy Panigashttp://www.sqlservercentral.com/Forums/Topic812701-471-1.aspxCreated a PowerShell Advanced Function to perform this as well you can find it here: [url=http://www.sqlmashup.com/powershell-advanced-function-for-when-you-are]http://www.sqlmashup.com/powershell-advanced-function-for-when-you-are[/url]Fri, 31 Dec 2010 17:12:53 GMTrobertmcookhttp://www.sqlservercentral.com/Forums/Topic812701-471-1.aspxHey,Is there any other way when dealing with SQL Server 2008; I tried the same trick on 2008 but it didn't work.So, will this trick only works for SQL 2005??[font="Comic Sans MS"]In 'Thoughts',[i]Lonely Vampire.[/i][/font]Fri, 30 Apr 2010 09:12:17 GMTLonely Roguehttp://www.sqlservercentral.com/Forums/Topic812701-471-1.aspxIn this scenario, start the SQL server in single user mode. When server starts in single user mode, all the builit admins of the machine will be the admins of SQL Server in single user mode.. So once logged in, you can add any other user as the admin..Sun, 21 Feb 2010 22:38:51 GMTKranthi Babuhttp://www.sqlservercentral.com/Forums/Topic812701-471-1.aspxHow To export To Excel data From Database in sql server 2005Mon, 18 Jan 2010 23:54:40 GMTvijaykumar.kakde1980