Share via

Lesson 2: Create a SQL Server Credential

  • Article

Credential: A SQL Server credential is an object that is used to store authentication information required to connect to a resource outside of SQL Server. Here, SQL Server backup and restore processes use credential to authenticate to the Azure Blob storage service. The Credential stores the name of the storage account and the storage account access key values. Once the credential is created, it must be specified in the WITH CREDENTIAL option when issuing the BACKUP/RESTORE statements. For more information about how to view, copy or regenerate storage account access keys, see Storage Account Access Keys.

For general information about credentials, see Credentials.

For information, on other examples where credentials are used, see Create a SQL Server Agent Proxy.

Important

The requirements for creating a SQL Server credential described below are specific to SQL Server backup processes (SQL Server Backup to URL, and SQL Server Managed Backup to Azure). SQL Server, when accessing Azure storage to write or read backups uses the storage account name and access key information. For more information on creating credentials for storing database files in Azure storage, see Lesson 3: Create a SQL Server Credential

Create a SQL Server Credential

To create a SQL Server Credential, use the following steps:

  1. Connect to SQL Server Management Studio.

  2. In Object Explorer, connect to the instance of Database Engine that has AdventureWorks2012 database installed, or use your own database you plan to use for this tutorial.

  3. On the Standard tool bar, click New Query.

  4. Copy and paste the following example into the query window, modify as needed.

    CREATE CREDENTIAL mycredential   
WITH IDENTITY= 'mystorageaccount' - this is the name of the storage account you specified when creating a storage account (See Lesson 1)   
, SECRET = '<storage account access key>' - this should be either the Primary or Secondary Access Key for the storage account (See Lesson 1)

    mapping storage account to sql credentials

  5. Verify the T-SQL statement and click Execute.

For more information about the Azure Blob storage service for backup concepts and requirements, see SQL Server Backup and Restore with Azure Blob Storage Service.

Next Lesson

Lesson 3: Write a Full Database Backup to the Azure Blob Storage Service.