Archive for the ‘SQL Server’ category

2 Methods to Start SQL Server in Single User Mode

October 26th, 2014 by Admin

What is single-user mode?

There are certain situations when user may have to start a SQL Server instance in single-user mode. For example, you might need to restore your master database from a backup in the event of a failure or damage, detach the database and kill all the connections. Both actions require starting an instance of SQL Server in single-user maintenance mode.

When a database is started in single-user mode, the following events occur:

  • Any current connections to the database are dropped without warning
  • Only one user can connect to the database
  • The CHECKPOINT process is not started

I’m going to walk you through the process of putting your SQL Server database into single-user mode. This can be either done via SQL Server Configuration Manager by setting the startup parameters or through the Command Prompt.

Method 1: Start SQL Server in single-user mode through SQL Server Configuration Manager

Run SQL Server Configuration Manager. Choose SQL Server Services from the left panel and then right-click on desired SQL Server service that needs to run in single-user mode. Select Properties from the drop-down menu.

configuration-manager

In SQL Server 2014 or 2012, click Startup Parameters tab. Type -m in the Specify a startup parameter box and then click Add.

sql-startup-parameters

If you’ve installed SQL Server 2008, 2005 or an earlier version, click Advanced tab. Append the ;-m to the end of the Startup Parameters box.

startup-parameters

Click Apply to save your changes. Restart your SQL Server instance and it should then run in single-user mode.

Method 2: Start SQL Server in single-user mode through Command Prompt

First of all, you need to figure out the name of your SQL Server instance. Press Windows key + R to bring up the Run box. Type services.msc and press Enter to open the Services window.

Find your desired SQL Server service in the list, double click it. This will open the Properties dialog box for that service. Copy the service name listed there. In my example, the service name is MSSQL$SQLEXPRESS.

sqlserver-service

Open up a Command Prompt with administrative privileges and run the following command to stop the SQL Server service.
net stop service_name

The next step is to start the service in single-user mode. This is done by specifying /m parameter with net start command.

single-user-mode

How to know if a running SQL Server instance is in single-user mode

When your SQL Server instance is started in single-user mode, it prevents multiple clients from connecting to the server at the same time. For example, if you’ve already connected to your DB with the SA account, and then try to login again through SQL Server Management Studio (SSMS), you’ll get the following error message:

Login failed for user”. Reason: Server is in single user mode. Only one administrator can connect at this time. (Microsoft SQL Server, Error: 18461)

sqlserver-connect-error

To fix this issue, you need to remove -m from the startup parameters and restart your SQL Server instance, which will bring the database back to multi-user mode.

How to Enable Remote Connections in SQL Server 2014/2012/2008

October 23rd, 2014 by Admin

When you try to connect to a SQL Server instance from a remote computer, you might receive the following error message:

Cannot connect to SQL Server.
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2008, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 – Error Locating Server/Instance Specified) (Microsoft SQL Server)

After installing SQL Server, the remote connection feature is disabled by default, and you’re only allowed to connect to your database locally. This article explains how to configure remote connections for a default SQL Server 2014/2012/2008 instance that you want to connect to from a remote computer.

How to Enable Remote Connections in SQL Server 2014/2012/2008?

Open the SQL Server Management Studio and log on to your DB locally. Right-click server name in the left pane and select Properties.

Select the Connections page in the left pane and make sure that checkbox Allow remote connections to this server is selected. Once this is done, exit SQL Server Management Studio.

allow-remote-connection

Open the SQL Server Configuration Manager, expand the node SQL Server Network Configuration and select Protocols for MSSQLServer (or whatever the name of your SQL Server instance is). Make sure that TCP/IP protocol is enabled.

enable-tcpip

Right-click on TCP/IP and select Properties. In the TCP/IP Properties dialog, select the IP Addresses tab and scroll down to IPAII. Make sure TCP Dynamic Ports is blank and that TCP Port is set to 1433.

tcpip-properties

Click OK to save your changes and restart your SQL Server instance. Now you should be able to connect to your SQL Server instance remotely using SQL Server Management Studio.

connect-to-sqlserver

If there is still problem in remote connection, you most likely need to configure your firewall settings and make sure the TCP port 1433 is not blocked by your firewall.

How to Reset SQL Server 2008 Password without Reinstalling

June 7th, 2014 by Admin

What to do when the SA account password is lost in SQL Server 2008? You may have faced the issue of losing the SQL Server SA password. Perhaps you followed the security best-practice of removing the built-in\Administrators from the sysadmin server role and now you’ve lost admin access to your SQL Server instance. At this point you may think that the only option is to reinstall SQL Server and attache the database.

In this tutorial I’ll show you how to reset SQL Server 2008 (R2) password without reinstalling. SQL Server Password Changer is a great recovery utility that allows you to change SA password that is stored in the master database file (master.mdf). Here’s how.

How to Reset SQL Server 2008 Password without Reinstalling?

  1. Click here to download and install the SQL Server Password Changer program on your computer.
  2. Click the Open File button. Select the SQL Server 2008 master database file (master.mdf). Typically it is located in the folder: c:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\.
  3. The program will automatically decrypt the master database file and display all user accounts in your SQL Server. Select the SA account, click the Change Password button. Type a new password and click OK.

    SQL Server Password Recovery

  4. The program will replace the existing password with your new password, and also unlock your SA account if it’s already locked out or disabled. Now start your SQL Server 2008 instance again and you can then log into the SA account with the new password.

How to Disable Windows Authentication in SQL Server

May 4th, 2014 by Admin

MS SQL Server supports two types of authentication models: Windows Authentication and SQL Server Authentication, which are configured during the installation of SQL Server. Windows Authentication is the default option for new SQL Server installation and it allows you to access the database without providing a username or password.

sql-windows-authentication

If you don’t want the users to open the database using Windows Authentication for security purposes, you can disable it easily.

How to Disable Windows Authentication in SQL Server?

  1. Login into SQL Server using Windows Authentication or SQL Server Authentication.
  2. In Object Explorer, open Security folder, open Logins folder. Right click on the local account and go to Properties.
  3. In the Login Properties window, select the Status tab.disable-windows-authentication
  4. Set Login to Disabled, or set Permission to connect to database engine to Deny.

After disabling the Windows Authentication in SQL Server, make sure you remember your SQL Server logins. If you forgot the SA password or mistakenly disable the SA account, there are also ways for you to unlock SA account in SQL Server.

SQL Server Management Studio Saved Password

May 2nd, 2014 by Admin

Is there a way to recover or retrieve the stored passwords in SQL Server Management Studio? How to clear the saved login for SQL Server Management Studio? SQL Server Management Studio always forgets the password to all the servers you connect even if you do have the “Remember password” checked?

management-studio-password

In this article we’ll explain how to fix all these problems you might have with SQL Server Management Studio (SSMS).

Where are SQL Server Management Studio passwords saved?

In SQL Server Management Studio 2005, all credentials are stored in the mru.dat file. However, SQL Server Management Studio 2008 and higher versions save the logins in the SqlStudio.bin file. Here are the default locations of SQL Server Management Studio stored passwords:

SQL Server Management Studio 2014:
C:\Users\%username%\AppData\Roaming\Microsoft\SQL Server Management Studio\12.0\SqlStudio.bin

SQL Server Management Studio 2012:
C:\Users\%username%\AppData\Roaming\Microsoft\SQL Server Management Studio\11.0\SqlStudio.bin

SQL Server Management Studio 2008:
C:\Users\%username%\AppData\Roaming\Microsoft\Microsoft SQL Server\100\Tools\Shell\SqlStudio.bin

SQL Server Management Studio 2005:

  • In Windows 8/7/Vista: C:\Users\%username%\AppData\Roaming\Microsoft\Microsoft SQL Server\90\Tools\Shell\mru.dat
  • Windows XP: C:\Documents and Settings\%username%\Application Data\Microsoft\Microsoft SQL Server\90\Tools\Shell\mru.dat

If you want to clear / remove the remembered login and password list in SQL Server Management Studio, simply delete the SqlStudio.bin or mru.dat file.

How to Recover stored passwords in SQL Server Management Studio?

If you open the SqlStudio.bin file with a text editor, you can find out the server names and login for the saved connections easily. However, the passwords are not stored in plain text.

Currently there is no any password recovery tools out there that can decrypt the stored passwords in SqlStudio.bin. If you’ve forgotten your password, you should just reset it on the server. SQL Server Password Changer is a reliable tool that can also help you unlock / reset sa password in SQL Server.

SQL Server Management Studio not saving password?

It seems to be a known issue with SQL Server Management Studio but to date I have not seen a fix. I also have experienced this problem on numerous occasions. However there is a simple workaround for this problem. Try to delete the SqlStudio.bin or mru.dat file and the “Remember password” function should work. I have done this on two different PCs with no problems, and its working.

How to Change SQL Server 2014 Password After Forgotten

April 27th, 2014 by Admin

Forgot the SA password on MS SQL Server 2014 instance? How can I reset or change SQL Server 2014 password? Microsoft SQL Server stores passwords as a hash in a system table named sql_logins. The old versions of SQL Server 2008/2005/2000 use only the SHA1 hashing algorithm to encrypt the password whereas SQL Server 2014/2012 have moved to use a stronger algorithm SHA512.

In theory, the SQL Server password hash can be cracked using brute-force attack. But currently there is no effective tool out there that can crack the password in an acceptable amount of time. Here I’ll introduce a simple way to change SQL Server 2014 password by replacing the password hash in the Master database (master.mdf).

How to Change SQL Server 2014 Password After Forgotten?

SQL Server Password Changer is the only tool we will be using for this tutorial. Before getting started, you need to stop your SQL Server instance so we can then use this program to edit the password hash resides in the master.mdf file, by following these steps:

  1. Click here to download and install SQL Server Password Changer on your local computer.
  2. Start the program and click the Open File button. Select the master database file (master.mdf) for your SQL Server 2014 instance. Typically it is located in the folder:
    C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA

    Select the SQL Server master database file

  3. The program will automatically decrypt the master database file and display all user accounts in your SQL Server 2014 database.

    SQL Server Password Recovery

  4. Select a user account whose password you want to change, click the Change Password button. Type a new password and click OK.

    Change SQL Server Password

  5. The old password will be replaced with your new password. Now start your SQL Server instance and you can then connect to your SQL Server 2014 database with your new password.

Besides resetting SQL Server 2014 password, you can also use SQL Server Password Changer to change forgotten SA password on SQL Server 2012, 2008, 2005 and 2000.

How to Reset Forgotten SA Password without Stopping SQL Server Instance

November 18th, 2013 by Admin

Forgot your SA password or get locked out of SQL Server? How to connect to SQL Server database in case the ‘BUILTIN\Administrators’ group is removed from SYSADMIN server role? One of my previous articles covers how to recover sa password through single user mode, but that approach requires of restarting your SQL Server instance and causing some downtime. Sometimes this would be unacceptable. In this article we’ll provide you with another way to reset forgotten SA password in SQL Server 2012/2008/2005 without any downtime.

How to Reset Forgotten SA Password without Stopping SQL Server Instance?

  1. To get started, we need to run the SQL Server Management Studio as the system account. This can be done easily with the PsExec program. Download PsExec from Sysinternals website. Extract the PsExec.exe program and save it to a folder on your computer such as C:\.
  2. Open an elevated Command Prompt and run the following command:
    C:\PsExec.exe -s -i "C:\Program Files (x86)\Microsoft SQL Server\110\Tools\Binn\ManagementStudio\Ssms.exe"
    Note: Your actual path of SQL Server Management Studio may be different from the one above.
  3. It will start SQL Server Management Studio as the system account. You’ll see that the username is shown as “NT AUTHORITY\SYSTEM”. Simply choose the “Windows Authentication” mode and click on the Connect button.

    connect-to-server

  4. Once you connect, in the Object Explorer view, click the plus sign next to Security, and then the plus sign next to Logins. Once expanded, right-click on the SA user, and choose Properties. You can then change the forgotten SA password with a new one.

    reset-sa-password

It is quite common to get locked out of a SQL Server instance, or simply forgot the SA password. With this approach you can reset forgotten SQL Server password easily! Quickly regain access to your SQL Server database without even any downtime.