Lesson 2: Create a SQL Server credential using a shared access signature

THIS TOPIC APPLIES TO: yesSQL ServernoAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

In this lesson, you will create a credential to store the security information that will be used by SQL Server to write to and read from the Azure container that you created in Lesson 1: Create a stored access policy and a shared access signature on an Azure container.

A SQL Server credential is an object that is used to store authentication information required to connect to a resource outside of SQL Server. The credential stores the URI path of the storage container and the shared access signature for this container.

Note

If you wish to backup a SQL Server 2012 SP1 CU2 or later database or a SQL Server 2014 database to this Azure container, you can use the deprecated syntax documented here to create a SQL Server credential based on your storage account key.

Create SQL Server credential

To create a SQL Server credential, follow these steps:

  1. Connect to SQL Server Management Studio.

  2. Open a new query windows and connect to the SQL Server 2016 instance of the database engine in your on-premises environment.

  3. In the new query window, paste the CREATE CREDENTIAL statement with the shared access signature from Lesson 1 and execute that script.

    The script will look like the following code.

    
    USE master  
    CREATE CREDENTIAL [https://<mystorageaccountname>.blob.core.windows.net/<mystorageaccountcontainername>] – this name must match the container path, start with https and must not contain a forward slash.  
       WITH IDENTITY='SHARED ACCESS SIGNATURE' -- this is a mandatory string and do not change it.   
       , SECRET = 'sharedaccesssignature' –- this is the shared access signature key that you obtained in Lesson 1.   
    GO  
    
  4. To see all available credentials, you can run the following statement in a query window connected to your instance:

    SELECT * from sys.credentials  
    
  5. Open a new query windows and connect to the SQL Server 2016 instance of the database engine in your Azure virtual machine.

  6. In the new query window, paste the CREATE CREDENTIAL statement with the shared access signature from Lesson 1 and execute that script.

  7. Repeat steps 5 and 6 for any additional SQL Server 2016 instances that you wish to have access to the Azure container.

Next Lesson:

Lesson 3: Database backup to URL

See Also

Credentials (Database Engine)
CREATE CREDENTIAL (Transact-SQL)
sys.credentials (Transact-SQL)