JasonSamuel.com
  • Home
  • About Me
  • Citrix
  • Microsoft
  • VMware
  • Security
  • Cloud
  • Enterprise Mobility
  • Tools
  • Cheat Sheets
    • Citrix, Microsoft, VMware Enterprise Mobility & Security Engineers Cheat Sheet
    • Home Automation – Internet of Things (IoT) Cheat Sheet
    • The How to Build A Windows Virtual Desktop (VDI) Experience Properly Cheat Sheet
  • Contact Me

SQL Express

Getting admin access on a Microsoft SQL Server when you don’t know the SA account password

By

Jason Samuel

on

August 9, 2011

Share

Tweet

Share

Share

5 comments

I was on an inherited SQL box this morning and didn’t know the SA account credentials. There was another admin service account but I didn’t have credentials for that either and unfortunately I wasn’t given any documentation on it. At first I was thinking through ways to go about resetting the SA account but then I remembered a trick to use your Windows local administrator credentials to override the SQL login and create an admin account for yourself. This ensures you don’t break anything by resetting an existing account. As long as you are logged into the server as a local administrator, this will work in a pinch.

1. Go to:

Start > Programs > Microsoft SQL Server 2005 > Configuration Tools > SQL Server Configuration Manager

2. Right click on the SQL server you want to add an account for and click Stop.

3. Once it has stopped, right click on it and open Properties. Go to the Advanced tab.

4. Under “Startup Parameters”, copy and paste that whole string to Notepad as a backup. Go back to the box and add the following right at the end of the string:

;–m

It should look like this:

Press OK when done. What this does is forces SQL to start in single user mode.

5. Now right click the SQL server you had stopped before and start it.

6. Now if you go to SQL Management Studio, it will error out. The only way to get into it is using sqlcmd. So open a command prompt and type:

sqlcmd

which should give you a “1>” prompt denoting line 1. If it gives you an error saying “Login failed for user ‘xxxxx’. Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461”, make sure all management consoles are closed and there are no other users logged in using it. Also go to your Windows services and stop all the following services:

SQL Server Agent
SQL Server FullText Search
SQL Server Integration Services
SQL Server Reporting Services
SQL Server VSS Writer

Only SQL Server and SQL Browser should be running. Try “sqlcmd” at the command prompt and it should let you through.

7. Now type the following to add your domain user account with admin privileges:

EXEC sp_addsrvrolemember 'domain\useraccount', 'sysadmin';
GO

it should give you blank line if it was successful.

8. Now go back into the SQL Server Configuration Manager and remove the “;–m” you had placed under Startup Parameters. Restart the SQL server again.

9. Now hit SQL Management studio and login using the account you have created using Windows Authentication. You should have full admin privileges to create additional accounts, reset the SA account, change server roles, change user mapping, etc.

Hope this helps. 🙂

Related ItemsMicrosoftreset sa accountreset sa passwordsecuritySQL

Share

Tweet

Share

Share

5 comments

← Previous Story Using Microsoft Log Parser to query huge log files and only display the results you need
Next Story → Citrix EdgeSight report server symmetric key error

About Jason Samuel

Jason Samuel lives in Houston, TX with a primary focus on strategic advisory and architecture of end-user computing, security, enterprise mobility, virtualization, and cloud technologies from Citrix, Microsoft, & VMware. He also has an extensive background in web architecture and networking over his 20+ year career in IT. He is an Author, Speaker, and Local User Group Community Leader. He is certified in several technologies and is 1 of 63 people globally that is a recipient of the prestigious Citrix Technology Professional (CTP) award. He is 1 of 42 people in the world that has been awarded as a VMware EUC Champion and VMware vExpert. He is a featured author on DABCC which provides the latest IT Community News on Cloud, Data Center, Desktop, Mobility, Security, Storage, & Virtualization. In his spare time Jason enjoys writing how-to articles and evangelizing the technologies he works with. Disclaimer: The content and opinions expressed in articles and posts are his own and are by no means associated with his employer.

Recommended for you

  • How to allow remote users to enroll smartcard certificates on a YubiKey over an HDX session for certificate lifecycle management using Citrix Virtual Apps and Desktops Service
  • FIDO Alliance Authenticate conference session recap on Citrix Workspace strong authentication with FIDO2
  • A deep dive into the Citrix HDX FIDO2 and Windows Hello optimized virtual channel with virtual desktops and apps using USB, NFC, BLE, and built-in authenticators

5 Comments

  1. Renjitha Kurian

    August 27, 2011 at 2:50 PM

    Hi Jason,
    This is the first time i am reading your blog.Really very helpful.Good job!!! Keep updating more stuff about the sql server.

  2. Jason Samuel

    August 27, 2011 at 8:52 PM

    @Renjitha Kurian

    Thanks!

  3. Dave Miler

    October 25, 2011 at 10:32 AM

    Jason,

    Thanks for your help in the SQL world. I tried this but get this error after starting with the -m option:
    C:\Program Files\Microsoft SQL Server\MSSQL10.SHAREPOINT\MSSQL\Binn>sqlcmd
    HResult 0x2, Level 16, State 1
    Named Pipes Provider: Could not open a connection to SQL Server [2].
    Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : A network-related or in
    stance-specific error has occurred while establishing a connection to SQL Server
    . Server is not found or not accessible. Check if instance name is correct and i
    f SQL Server is configured to allow remote connections. For more information see
    SQL Server Books Online..
    Sqlcmd: Error: Microsoft SQL Server Native Client 10.0 : Login timeout expired.

    Ideas?

  4. Alex

    December 12, 2011 at 12:40 PM

    Thanks Jason this worked for me 🙂

    The only trouble I had was this error:
    C:\Windows\system32>sqlcmd
    HResult 0x2, Level 16, State 1
    Named Pipes Provider: Could not open a connection to SQL Server [2].
    Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections..
    Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

    To resolve I just ran sqlcmd with parameters:
    sqlcmd -S np:\\127.0.0.1\pipe\MSSQL$instancename\sql\query

  5. Luke

    February 11, 2013 at 11:56 AM

    Hi, I am using SQLServer 2008 Express version. I tried to do whatever I can; Still I am getting the single user mode error. I could not do anything now. Could you please help? I dont have anything running, other than one SQL Server service. No other window opened…

Leave a Reply

Your email address will not be published. Required fields are marked *

 

  • Home
  • About Me
  • Tools
  • RSS Feeds
  • Past Articles
  • Contact Me
  • Privacy Policy
  • Citrix, Microsoft, VMware Enterprise Mobility & Security Engineers Cheat Sheet
  • Home Automation – Internet of Things (IoT) Cheat Sheet
  • The How to Build A Windows Virtual Desktop (VDI) Experience Properly Cheat Sheet
JasonSamuel.com

JasonSamuel.com began in 2008 as a way for me to give back to the IT community. This website features the latest news and how-to's on enterprise mobility, security, virtualization, cloud architecture, and other technologies I work with.

This website has evolved over time to become a go-to reference hub for these technologies. It receives hundreds of thousands of unique visitors from all over the world each month.

More details on the About Me page.

Copyright © 2008-2021 JasonSamuel.com