Archive for the ‘SQL Server’ category

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.

How to Recover SA Password on Microsoft SQL Server

October 24th, 2013 by Admin

If you ever lost SA password, you may have thought your only option is to reinstall SQL Server and re-attach all of the user databases. However, SQL server provides a much better disaster recovery method which preserves objects and data in the master database. Simply start SQL Server in single user mode and you can recover SA password easily with the OSQL command. Here are the detailed steps:

Part 1: Start SQL Server in Single User Mode

  1. Open SQL Server Configuration Manager.
  2. Find the SQL Server instance you need to recover the SA password.
  3. Stop the SQL Server instance.
  4. Right-click on the instance and select Properties.
  5. Click on the Advanced tab, and add -m; to the beginning of Startup parameter.
  6. Click OK and start the instance.

Part 2: Recover SQL Server SA Password

  1. Open an elevated command prompt and enter the command:
    osql -S myServer\instanceName -E
    Replace myServer\instanceName with the name of the computer and the instance of SQL Server that you want to connect to.
  2. 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
  3. Stop the SQL Server instance.
  4. Remove the -m option from the Start parameters field, and then start the SQL Server service.

At this point you should be able to login to SQL Server using the SA user account and the new password you gave it. Besides recovering SA password with OSQL, you can also reset / change your forgotten SA password using the third-party software such as SQL Server Password Changer.

How to Find SQL Server 2012 Product Key from Windows Registry

October 15th, 2013 by Admin

Need to perform a repair installation of SQL Server 2012 and can’t locate your original CD key? How can I recover lost product key from SQL Server 2012 installation? If you’ve misplaced your SQL Server installation key, you can retrieve it from Windows registry using a key finder program.

Product Key Finder is a simple program that can help you find lost product key of SQL Server 2012/2008/2005/2000 from Windows registry. Since SQL Server 2012, Microsoft has changed its algorithm to encode the product key. This cause most of key finder program become useless. Product Key Finder is the unique program available on the internet that can decrypt SQL Server 2012 product key flawlessly.

How to Find SQL Server 2012 Product Key from Windows Registry?

  1. Start the Product Key Finder program.
  2. Click the Start Recovery button, it will bring up a drop-down menu that provides three recovery mode: From current system, From non-booting system and From remote computer.

    Recover Product Key from Local Computer

  3. Select the From current system mode from the drop-down menu. This program will automatically scan your Windows registry, quickly find and recover your lost SQL Server 2012 product key, along with license keys of Windows, Office and other programs.
  4. Now click on the “Save To File” button. It allows you to export the product keys into a text file.

How to Reset Forgotten SA Password in SQL Server 2008

May 29th, 2013 by Admin

I installed SQL Server 2008 sometime ago and forgot the sa password I set during setup. How can I connect to SQL server now? Is there a way I can retrieve sa password in SQL Server 2008?

If you ever forgot a SA password, you may have thought that the only option is to reinstall SQL Server. But this is not true. If you can still log in to your SQL Server with Windows authentication mode, or log in using other user account that is a member of sysadmin role, then you can reset the lost SA password easily! However I don’t think this approach will help you, otherwise you wouldn’t be sitting here reading article, would you?

Other method is to use this professional password recovery software – SQL Server Password Changer, which provides you with an easy way to reset forgotten SA password in SQL Server 2008 (R2). It also supports SQL Server 2012/2005/2000. Follow this tutorial and you can reset SQL Server password and regain access to your database easily and quickly.

How to Reset Forgotten SA Password in SQL Server 2008?

  1. Download and install SQL Server Password Changer on your computer.
  2. After installing, start the program and then click on “Open File” button.
  3. It will display a dialog box that prompts you to choose the master.mdf database of your SQL Server. But at this time you need to stop your SQL Server serivce in advance so this password recovery program is able to regain full control over the master.mdf file.
  4. Once you’ve chosen the master.mdf file, click on “Open”. The program will display all user accounts existing in your SQL Server database.

  5. Choose the SA user account and then click on “Change Password” button. Enter your new password and click OK. Now you’ve successfully reset your forgotten SA password in SQL Server 2008.
  6. Start the SQL Server service and then you can log in to the SA account using the new password.

Using SQL Server Password Changer you can also reset the password of any other user accounts existing in your SQL Server 2008 database. Resetting the password of SA is a breeze!

How to Change SQL Server Password

June 25th, 2012 by Admin

Forgot SQL Server password? A former employee forgot to leave you with the sa password for your SQL Server?  How to change the password or reset it? If you couldn’t fix this problem, you may have to reinstall SQL Server.  But if you have some very important data in the database, this may prevent you from reinstalling. Then, is there any effective way to recover or change SQL Server password?

This tutorial will explain how to change SQL Server password with SQL Server Password Changer, if you forgot sa password and can’t connect to your database using any user account. Once SQL Server is installed, a master.mdf file will be created and be used to stored the logins and passwords for all available SQL Server accounts. With SQL Server Password Changer you can easily change forgotten SQL Server passwords stored in the master.mdf database.

How to Change SQL Server Password:

  1. Download SQL Server Password Changer on your local PC, install and launch it.
  2. Click the Open File button. Select the SQL Server master database file (master.mdf). Typically it is located in the folder: C:\Program Files\Microsoft SQL Server\MSSQL.1\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. Select the SA account, click the Change Password button. Type a new password and click OK.SQL Server Password Recovery
  4. The old password will be replaced with your new password. Now you are able to log into the SA account using your new password.

Follow the steps above you can also change other user account passwords.  SQL Server Password Changer is a really useful utility and it can help you easily reset SQL Server passwords on SQL Server 2008/2005/2000.