Edit

Create a database master key

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

This article describes how to create a database master key in SQL Server by using Transact-SQL. The database master key encrypts other keys and certificates inside a database. Create the database master key once per database, and back it up to a secure off-site location so you can restore it if it's deleted or corrupted.

Permissions

Requires CONTROL permission on the database.

Create the database master key

The code samples in this article use the AdventureWorks2025 or AdventureWorksDW2025 sample database, which you can download from the Microsoft SQL Server Samples and Community Projects home page.

  1. Connect to the SQL Server instance where you want to create the database master key. You can connect to an instance of SQL Server using any familiar SQL Server client tool, such as sqlcmd, SQL Server Management Studio (SSMS), or the MSSQL extension for Visual Studio Code.

  2. Choose a strong password for encrypting the database master key. Your password should follow the SQL Server default password policy. By default, the password must be at least eight characters long and contain characters from three of the following four sets: uppercase letters, lowercase letters, base-10 digits, and symbols. Passwords can be up to 128 characters long. Use passwords that are as long and complex as possible.

  3. Review and run the following Transact-SQL script in the context of the database where you want to create the database master key. Change the password to match your environment.

    Caution

    You need the password to open the database master key. Make sure you store this password safely and securely.

    USE AdventureWorks2025;
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<password>';

  4. Back up the newly created database master key. For more information, see Back up a database master key.

Related content

  • Last updated on