How to reset lost SA password in SQL Express

Overview

If you ever find yourself in a situation where you need to reset the sa password on a SQL Express instance then you are not alone. I recently inherited a PC from a previous developer that already had SQL Express installed. This normally wouldn’t be a bad thing, except in this case it was not setup in mixed mode and my logged in account had no sysadmin rights on the instance.

In this article I hope to enlighten you as to how to reset the sa password in SQL Express without having any sort of administrator rights to the SQL instance.

You will need to be somewhat comfortable with editing the registry, starting and stopping windows services and working with a command prompt.

Steps to SQL Express sysadmin access

In order to gain sysadmin access to the SQL instance there were a few things that I needed to accomplish. Here is the basic goals I needed to achieve:

  • Change the login method from Windows Authentication to Mixed mode
  • Enable the sa user account
  • Reset the password to the sa user account

Doesn’t seem like that daunting of a list does it? I didn’t think so either.

Change the login method

In order to change the login method I had to change a registry setting. Why, you ask? Because, I say, we don’t have sysadmin rights so we can’t just change the database properties using SSMS.

  • Open the registry editor
  • Navigate to…
    HKEY_LOCAL_MACHINE
       \SOFTWARE
          \Microsoft
             \Microsoft SQL Server
                \MSSQL.10.SQLEXPRESS
                   \MSSQLServer
    
  • Change the value of LoginMode from 1 to 2
  • Close the registry editor

One thing to note is I had a few different options under the “Microsoft SQL Server” branch so you may need to take an educated guess if yours is different.

Enable the sa user account and reset its password

I lumped these two into one section because the commands for them are entered at the same place.

  1. Open the Services applet in the Control Panel
  2. Find the “SQL Server (SQLEXPRESS)” entry and open its properties
  3. Stop the service
  4. Enter “-m” into the “Start parameters” field
  5. Start the service
  6. Open a Command Prompt
  7. Enter the command:
    osql -S PC_NAME\SQLEXPRESS -E

    Be sure to change PC_NAME to whatever your PC name is or you will get a big long message saying you messed up.

  8. At the next prompts enter the following commands:
    1> alter login sa enable
    2> go
    1> sp_password NULL,'new_password','sa'
    2> go
    1> quit
  9. Stop the “SQL Server (SQLEXPRESS)” service
  10. Remove the “-m” from the Start parameters field
  11. Start the service

At this point you should be able to login to SSMS using the sa user account and the new password you gave it.

How to generate a SHA256 certificate and how to install SHA256 certificate in IIS

  1. Download and install OpenSSL from Shining Light. while installing please remember the path (Here my installation path is c:\OpenSSL-Win32)
  2. Create a folder in any location (My folder location is C:\OpenSSL)
  3. Open command prompt[cmd] exicute the below given command.
    set OPENSSL_CONF=c:\OpenSSL-Win32\bin\openssl.cfg
  4. Generate your Certificate request (CSR), specifying an SHA256 signature hash . Execute the below given command.[point in to the OpenSSL installation folder\bin (C:\OpenSSL-Win32\bin)]
    openssl req -nodes -sha256 -newkey rsa:2048 -keyout C:\OpenSSL\PrivateKey.key -out C:\OpenSSL\CertificateRequest.csr
  5. You’ll be prompted for a few certificate fields , enter those feilds as they come up.
  6. This will generate two files – 1) PrivateKey.key (which contains the un-encrypted version of your private key – protect this file, as somebody who obtains it along with your signed public key can impersonate you) 2) CertificateRequest.csr (your certificate signing request, which is not sensitive).
  7. Just check what hash algorithm is currently used, execute this below given command
    certutil -getreg ca\csp\CNGHashAlgorithm
    if this returns SHA256, skip to step 9.
  8. By default the above should return SHA1. Run this below given command to configure the CA to use SHA256 for CNG hashes.
    certutil -setreg ca\csp\CNGHashAlgorithm SHA256.
  9. Restart Certificate Services:
    net stop CertSvc && net start CertSvc
  10. Execute the steps no 7 and make sure that, the current HashAlgorithm is SHA256 .
  11. Go to your bowser, open http://localhost/CertSrv -> Click on Request a certificate
  12. ssl-sha256-1
  13. Then Click on Advanced certificate request.ssl-sha256-2
  14. Then Click on the Second link as given below.ssl-sha256-3
  15. Go to the folder where the CertificateRequest.csr is located [C:\OpenSSL]. Open the file CertificateRequest.csr in a notepad and copy the encoded value.
  16. Go back to the browser, paste your copied encoded values in to the Base-64-encoded certificate request as given below.ssl-sha256-4
    then click on submit.
  17. Click on Base 64 encoded option, then click on Download certificate link. It will download your .cer file [I am saving this CertNew.cer in C:\OpenSSL].ssl-sha256-5
  18. Copy your PrivateKey.key and CertNew.cer [from C:\OpenSSL] to OpenSSL installation folder\bin [C:\OpenSSL-Win32\bin]
  19. Open your Command Prompt [run->cmd] execute the below given script.[point in to the OpenSSL-Win32 installation folder\bin (C:\OpenSSL-Win32\bin)]
    openssl pkcs12 -inkey PrivateKey.key -in CertNew.cer -export -out CertNew.pfx
  20. Open your IIS[Run->inetmgr],go to the server certificates option as given below.ssl-sha256-6
  21. Click on the Import option as given below.-> select the CertNew.pfx file from the location where we created [C:\OpenSSL-Win32\bin\CerNew.pfx].ssl-sha256-8

FreeRDP HTML5 proxy on Windows

FreeRDP-WebConnect is an open source HTML5 proxy that provides web access to any Windows server and workstation using RDP. The result is amazing, especially considering that no native client is required, just a plain simple web browser!

Platform support

HTML5 has came a long way in the last few years, with any major web browser (including mobile platforms) supporting WebSockets, the underlying communication mechanism employed by FreeRDP-WebConnect.

Here’s a list of supported desktop and mobile browsers:

  • FireFox >= 11.0
  • Chrome >= 16.0
  • Internet Explorer >= 10
  • Safari >= 6
  • Opera >= 12.10
  • Safari Mobile >= 6
  • Android Browser >= 4.4

Supported client desktop OSs:

Windows, OS X, Linux

The FreeRDP-WebConnect service itself can be installed on most recent Linux distributions and on every x86 and x64 Windows versions starting with Windows Server 2008:

  • Windows Server 2008 / Windows Vista
  • Windows Server 2008 R2 / Hyper-V Server 2008 R2 / Windows 7
  • Windows Server 2012 / Hyper-V Server 2012 / Windows 8
  • Windows Server 2012 R2 / Hyper-V Server 2012 R2 / Windows 8.1

How to install FreeRDP-WebConnect on Windows

The installation on Windows is really easy. To begin with, download the installer from their website and run it:

freerdp_installer_1

Accept the license, select the installation type and optionally change the install location:

freerdp_installer_3

Next comes the HTTP and HTTPS configuration. You can just accept the defaults and go on with “Next” or replace the options to match your environment. Make sure to choose ports not used by other services. The installer will create a self signed certificate for HTTPS, no need to worry about it. Windows firewall rules are also automatically created if enabled.

freerdp_installer_4

The OpenStack settings are required only if you intend to use this service with OpenStack, otherwise you can skip them. Authentication URL, tenant name, username and password can be retrieved from your OpenStack deployment, while the Hyper-V host username and password are required to connect to RDP consoles and can be either local or domain credentials.

freerdp_installer_5

We’re done with the configuration, press “Next” and the the installer will complete the installation.

freerdp_installer_8

Once done, point your browser to “http://localhost:8000″ (or a different port if you changed it above) and you’ll see the initial connection screen (using Chrome on OS X in this example, but any of the options listed above is also valid):

freerdp_browser

Set the host, username and password and click connect:

freerdp_browser2

That was it, connected! A native client will still have an edge in terms of performance, but for a lot of scenarios a pure web client enables an amazing whole lot of new possibilities!

Integration with OpenStack

We integrated RDP support in Icehouse, on both Nova and Horizon. All you have to do to make it work is to specify the url of your FreeRDP-WebConnect service in the Hyper-V Nova compute nodes as detailed below and restart the nova-compute service. The Hyper-V Nova compute installer takes care of these settings as well of course!

Download the application

FreeRDPWebConnect_Beta.msi (6.4MB)

MDaemon Messaging Server (free)


MDaemon Messaging Server, email server software for Windows, is a trusted alternative to Microsoft Exchange or SBS. MDaemon mail server supports IMAP, SMTP, POP3, and ActiveSync protocols and delivers solid performance from its feature-rich and user-friendly design. 
  •  WorldClient (Web-Based Email)
  • MDaemon Remote Administration
  • WorldClient Instant Messenger
  • Microsoft Outlook Integration (Outlook Connector)
The free version has limited functionality.

Advertsing

125X125_06

Planet Xamarin

Planet Xamarin

Calendar

<<  September 2017  >>
MonTueWedThuFriSatSun
28293031123
45678910
11121314151617
18192021222324
2526272829301
2345678

View posts in large calendar

Month List