Archive for the ‘SQL Server’ category

« Older Entries

How to Reset Lost SA Password for SQL Server 2022

May 15th, 2023 by Admin

Cannot authenticate using the sa account credential? The default sa password does not work? If you cannot establish a connection using SQL Server Management Studio or restore the master.mdf database, doing a password reset should be the fastest way to regain access to your SQL Server instance. In this tutorial we’ll walk you through the steps of resetting lost sa password for SQL Server 2022.

Part 1: Stop SQL Server Service

To get started, we need to stop the SQL Server service and find where the master.mdf database is located using File Explorer. Follow these steps:

  1. Click the Start button. When the Start menu appears, expand the “Microsoft SQL Server 2022” folder and click “SQL Server 2022 Configuration Manager“.

  2. In the left-hand side of the window, click the “SQL Server Services” entry. In the right pane, right-click “SQL Server (MSSQLServer)” or a named instance, and then choose Stop.

  3. Next, double-click the “SQL Server (MSSQLServer)” service to open the Properties dialog. Go to the “Startup Parameters” tab, and you’ll see the full path of the master.mdf file which is displayed in the “Existing parameters” list box.

    For SQL Server 2022, the default location is C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA.

  4. Open File Explorer and browse to the above location, and check if the master.mdf file can be found.

Part 2: Reset Lost SA Password for SQL Server 2022

  1. After opening the SQL Server Password Changer utility, click the Open File button in the right side.

  2. Choose the master.mdf file for your SQL Server 2022 instance, and then click Open.

  3. You’ll get the list of all database users. Simply choose the sa account and click the “Change Password” button.

  4. Enter a new sa password and click OK. Close the SQL Server Password Changer utility.

Part 3: Connect to SQL Server 2022 with New SA Password

Open the SQL Server 2022 Configuration Manager and start the SQL Server service, then launch the SQL Server Management Studio, you can then log in to the sa account with your new password!

If you get the error message “Login Failed for User (Microsoft SQL Server, Error: 18456)“, it’s possible that the authentication method of the SQL server is not properly configured. You need to use the second method descried in this article to enable mixed mode authentication.

That’s it!

Comments Off on How to Reset Lost SA Password for SQL Server 2022 »

Posted in Others, SQL Server, Tips & Tricks

Tags:

How to Change Product Key for SQL Server 2017 / 2016 / 2014 Installation

July 16th, 2018 by Admin

Do you want to change SQL Server license key on production server? How to upgrade your SQL Server evaluation edition to the Standard or Enterprise edition? In this tutorial we’ll walk you through the steps to change product key for any existing SQL Server 2017 / 2016 / 2014 installation, without having to reinstall.

How to Change Product Key for SQL Server Instance?

  1. Click the Start button, and then navigate to the “SQL Server [version]” folder. Expand it and you can launch the SQL Server Installation Center.

    Another way of opening SQL Server Installation Center is to run setup.exe from SQL Server installation media.

  2. Under the Maintenance tab, click on Edition Upgrade as shown below.

  3. Enter the new product key and click Next.

  4. Accept the license term and click Next to proceed.

  5. Select the existing SQL Server instance for which you are going to change product key.

  6. After checking all rules for edition upgrade, review the summary information and click Upgrade to begin the upgrading process.

  7. The upgrade process will be done in a minute or two.

    Now you’ve successfully changed the product key for the SQL Server instance running on your machine.

Comments Off on How to Change Product Key for SQL Server 2017 / 2016 / 2014 Installation »

Posted in Others, SQL Server, Tips & Tricks

Tags:

3 Ways to Check Which Version or Edition of SQL Server is Running

July 15th, 2018 by Admin

In this tutorial we’ll show you 3 ways to check if a SQL Server instance is licensed and determine which version or edition of SQL Server is installed on your computer.

Method 1: Check SQL Server Version Using T-SQL

  1. Open a Command Prompt. Connect to your SQL Server instance by executing this command:
    SQLCMD -S server_name\instance_name

    Where server_name is your server name and instance_name is the SQL instance name. If you’re trying to connect to the default instance, just type SQLCMD.

  2. Next, run the following T-SQL query:
    select @@version
    go

    This will display the version and license type of your connected SQL Server instance. If you have multiple instances, use the same method to check each instance.

Method 2: Check SQL Server Version Using SQL Server Installation Center

  1. Click the Start button, then expand the SQL Server folder and launch SQL Server Installation Center.

  2. Select Tools in the left-hand navigation area of SQL Server Installation Center. On the right side, choose Installed SQL Server features discovery report.

  3. It will create a report of the installed SQL Server instances, and you can open it with your Web browser. With this report you can determine your SQL Server version and edition, service pack level and license type.

Method 3: Check SQL Server Version Using SQL Server Management Studio

  1. Open up SQL Server Management Studio and connect to your SQL Server database. Right-click the server in Object Explorer, and then click Properties.

  2. On the General page, look for the Product field, which will display the version of SQL Server that is running on your machine.

That’s it!

Comments Off on 3 Ways to Check Which Version or Edition of SQL Server is Running »

Posted in Others, SQL Server, Tips & Tricks

Tags:

Find Lost Product Key from SQL Server 2017 / 2016 / 2014 Instance

July 14th, 2018 by Admin

Lost SQL Server 2016 product key from an installed instance? When you need to reinstall SQL Server or move the license to a new machine, you can use KeyFinder Plus to decrypt and locate SQL Server product key stored in the Windows registry. This tutorial will walk you through the steps to find SQL Server 2017 / 2016 / 2014 product key from an existing instance.

How to Find SQL Server Product Key from an Installed Instance?

  1. To begin with, download and install KeyFinder Plus on the server running a SQL Server instance.
  2. The program comes with a neat and pretty simple interface. Just click on the Start Recovery button and you’ll see a list of options for recovering product keys from local machine, unbotable system or network PC.

  3. Once you have chosen the first option, it will quickly find out product keys for all editions of SQL Server, Windows, Office and other softwares installed on your local computer.

    If you want to write down or print out the product keys so you can access them later, click on the Save To File button.

  4. We’d recommend saving to the desktop for easy access.

    Afterwards, you can open that file with your preferred text editor and print it out.

Finding lost SQL Server product key is as simple as that!

Comments Off on Find Lost Product Key from SQL Server 2017 / 2016 / 2014 Instance »

Posted in Others, SQL Server, Tips & Tricks

Tags:

How to View or Restrict the Number of Connections in SQL Server

December 30th, 2016 by Admin

SQL Server allows a maximum of 32,767 user connections. In this tutorial we’ll show you how to find out the number of connections to a database in SQL Server, and restrict the number of connections to a SQL Server instance.

Part 1: View Total Number of Connections in SQL Server

Need to check the number of active connections for each database? This can be done easily using the following script. The script displays the databaseName, the number of connections and the login name:

SELECT DB_NAME(dbid) as DBName,
loginame,
COUNT(dbid) as NumberOfConnections
FROM sys.sysprocesses
WHERE dbid > 0
GROUP BY dbid, loginame

If you’re running SQL Server 2012, 2014 or 2016, run this script instead:

SELECT DB_NAME(database_id) as DBName,
login_name,
COUNT(session_id) as NumberOfConnections
FROM sys.dm_exec_sessions
GROUP BY database_id, login_name

Part 2: Restrict Concurrent Database Connections in SQL Server

To configure the maximum user connections, open SQL Server Management Studio and connect to your database, then follow these steps:

  1. In Object Explorer, right-click a server and click Properties.

    sql-server-properties

  2. Click the Connections node on the left page.
  3. Under Connections, in the Maximum number of concurrent connections box, type or select a value from 0 through 32767 to set the maximum number of users that are allowed to connect simultaneously to the instance of SQL Server.

    max-connections-in-sql-server

  4. Click OK. Restart SQL Server to apply your changes.

Comments Off on How to View or Restrict the Number of Connections in SQL Server »

Posted in Others, SQL Server, Tips & Tricks

Tags:

5 Ways to Stop or Start SQL Server Service

October 11th, 2016 by Admin

How to stop a local SQL Server instance from command line or PowerShell? In this tutorial we’ll show you 5 simple ways to stop or start SQL Server service in SQL Server 2016/2014/2012/2005.

Method 1: Stop / Start SQL Server Service from Microsoft Services Console

  1. Press the Windows + R keys to open the Run box. Type services.msc and press Enter.
  2. This will open the Microsoft Services console. Find the service called SQL Server (instant name) and then double-click on it.

    sql-server-service

  3. In the Properties dialog box, you can see that the service name of my SQL Server instance is MSSQL$SQLEXPRESS. From there you can click the Stop button if your SQL Server service is running, or click the Start button to get the service up and running.

    stop-sql-server-service

Method 2: Stop / Start SQL Server Service from Command Line

  1. Open an elevated Command Prompt.
  2. To stop a named SQL Server instance, type the following command. Replace SQLEXPRESS with the instance name.
    net stop MSSQL$SQLEXPRESS

    start-sql-server-command-line

    To start a named SQL Server instance, type:
    net start MSSQL$SQLEXPRESS

Method 3: Stop / Start SQL Server Service from SQL Server Configuration Manager

  1. To get started, you need to open SQL Server Configuration Manager. In Windows Server 2008 or 2003, you can click Start > Programs -> Microsoft SQL Server > Configuration Tools > SQL Server Configuration Manager.
  2. In the left pane of SQL Server Configuration Manager, click SQL Server Services. We can see all services in right pane. Right-click on SQL Server service and you can then select Stop or Start from the context menu.

    sql-server-configuration-manager

Method 4: Stop / Start SQL Server Service from PowerShell

  1. Open the PowerShell as administrator.
  2. You can use the Start-Service cmdlet to stop your SQL Server service. Replace SQLEXPRESS with the instance name.
    Start-Service -Name 'MSSQL$SQLEXPRESS'

    start-sql-server-powershell

    Or use the Stop-Service cmdlet to start your SQL Server service.
    Stop-Service -Name 'MSSQL$SQLEXPRESS'

Method 5: Stop / Start SQL Server Service from SQL server Management Studio

  1. Open SQL server Management Studio and connect to your SQL Server instance.
  2. Right-click on the server in Object Explorer, you’ll have options to Stop, Start, Pause, Resume and Restart your SQL Server service. If we are not already connected, we can’t start a stopped instance.

    stop-sql-server-via-ssms

Comments Off on 5 Ways to Stop or Start SQL Server Service »

Posted in Others, SQL Server, Tips & Tricks

Tags:

How to Install SQL Server 2016 CTP 3.0 on Windows Server 2012 R2

November 26th, 2015 by Admin

SQL Server 2016 Community Technology Preview 3.0 (CTP 3.0) was released to the public on 28th October, and I downloaded a fresh copy to start trying it out. In this article I’m going to explain, step-by-step, how to install SQL Server 2016 CTP 3.0 instance on a Windows Server 2012 R2 system.

Preparing for installation

Before beginning, you should check the minimum hardware and software requirements before you install SQL Server 2016. both .NET framework 3.5 and Windows 8.1 Update (KB2919355) are required to be installed. Check out the following articles on how to install .NET framework 3.5 and KB2919355 on Windows Server 2012 R2:

Install .NET Framework 3.5 on Windows Server 2012 with / without CD
Fix: Windows 8.1 Update (KB2919355) is not applicable to your computer

How to Install SQL Server 2016

SQL Server 2016 CTP 3.0 is free for trial and you need to sign-in to download it from Microsoft’s web site. The ISO file I’ve downloaded is SQLServer2016CTP3.0-x64-ENU.iso that takes around 2.58 GB of space on disk. Once downloaded, you can burn the ISO image to a DVD for making a installation media. Here’s how to install SQL Server 2016 on Windows Server 2012 R2:

  1. Insert the SQL Server 2016 installation DVD. From the root folder, double-click Setup.exe and you’ll be greeted with SQL Server Installation Center. To perform a new stand-alone installation, click New SQL Server stand-alone installation or add features to an existing installation.

    sql-server-installation

  2. Enter the product key and click Next. As the final version of SQL Server 2016 is not released yet, so just select evaluation edition.

    sql-product-key

  3. In the License Terms window, tick the box I accept the license terms and then click Next to continue.

    accept-license-terms

  4. A new option appears here to use Microsoft updates if you would like to. I keep it unchecked as I would patch SQL Server manually at the right time, after testing.

    use-microsoft-update

  5. Run the setup rules checks. I have a warning to tell me that the firewall is on and just ignore it if you haven’t configured Windows firewall to block SQL Server access.

    sql-install-rules

  6. On the Setup Role page, choose SQL Server Feature Installation and click Next.

    sql-server-feature-install

  7. Select the required SQL features and click Next. Make sure that Database Engine Services is checked. I have also chosen to install the Management Tools – Basic so I can use Management Studio to manage SQL Server.

    feature-selections

  8. At this step another check will run, this time to identify problems that might block the setup, based on your choices of the last step. Just wait for its completion and click Next.

    sql-server-feature-rules

  9. This step lets you decide if you want to go with Default Instance or a Named Instance. Be aware that you can only install one default instance but you can install multiple named instances on the same computer.

    configure-sql-server-instance

  10. On the Server Configuration screen, you can specify the service accounts and startup type, or simply leave it at the default configuration as you can change them after the installation.

    sql-service-accounts

  11. Now you need to choose the authentication mode of your SQL Server instance. For the purposes of this article, I will be choosing Mixed Mode Authentication. You will also need to specify the SQL Server administrators to be used; in this example I will use the current logged in user by clicking Add Current User button.

    sql-server-password

  12. This page shows a tree view of installation options that were specified during Setup. Review and click Install.

    ready-to-install-sql-server

  13. The installation will begin — wait for completion.

    sql-installation-progress

  14. Once the installation is complete, SQL Server 2016 Setup will confirm this showing which items of the install succeeded.

    sql-install-succeeded

Comments Off on How to Install SQL Server 2016 CTP 3.0 on Windows Server 2012 R2 »

Posted in SQL Server, Tips & Tricks

Tags:

2 Options to Reset Forgotten SQL Server 2012 Password

June 10th, 2015 by Admin

Forgot the SA password for your SQL Server 2012 instance? If you have tried to access the database too many times with wrong passwords, the SA account may be locked out. In this tutorial we’ll show you two options to take control of your SQL Server 2012 by either adding a sysadmin account or resetting the SA password.

Option 1: Reset SQL Server 2012 Password in Single-user Mode

  1. Launch the SQL Server Configuration Manager utility. Look for your SQL Server 2012 instance and stop the service.

    stop-sql-server

  2. Open a Command Prompt with admin privileges, run the following command to start SQL Server 2012 in single-user mode:
    net start MSSQL$SQLEXPRESS /m"SQLCMD"

    Be sure to change MSSQL$SQLEXPRESS to the service name of your SQL Server instance. Each instance has its own SQL Server service. The service name could be found within the Windows Services panel (services.msc).

    sql-server-service

  3. Use the sqlcmd command to connect to your SQL Server 2012 database locally. If your SQL Server is installed on a remote computer, make sure you substitute the . (dot) with the host name of that server.
    sqlcmd -E -S .
  4. Type the following SQL statement to create a new account and granting it any server-level permission.
    CREATE LOGIN tom WITH PASSWORD = 'P@ssw0rd'
    GO
    ALTER SERVER ROLE sysadmin ADD MEMBER tom
    GO

    add-sql-user

  5. Type exit to quit sqlcmd. Restart your SQL Server 2012 service to get out of the single-user mode. You can then connect to the database using the new login you just created.

Option 2: Offline Reset SQL Server 2012 Password

SQL Server creates the user’s password hash, which is stored in the master database (sysxlogins). With SQL Server Password Changer you can unlock / reset SQL Server 2012 passwords for the SA and other user accounts. Here’s how:

  1. First of all, you need to stop your SQL Server 2012 instance from within the SQL Server Configuration Manager utility.
  2. Download and install the SQL Server Password Changer program.
  3. Start the SQL Server Password Changer program. Click on Open File button to browse for the master.mdf file. Usually the master.mdf file is located at C:\Program Files\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQL\DATA.

    open-master-mdf

  4. Once you have clicked on the Open button, the program will display a list of user accounts for your SQL Server instance. Select the SA account and click on Change Password button.

    sql-server-2012-password

  5. In the pop-up window, type a new password and click OK. The new password can be as short or simple as you want, no need to meet the password complexity requirements.
  6. After resetting the SA password, start your SQL Server instance and you can then login to the SA account successfully.

Comments Off on 2 Options to Reset Forgotten SQL Server 2012 Password »

Posted in SQL Server, Tips & Tricks

Tags: