This is another #InAJiffy blog post on SQL Server 2014, particularly if you want to create SQL Server data files on Azure Storage. Below is an example of my hybrid database, called HybridTestA1 where the database instance and its log file are located on prem, but the data file (“HybridTestA1_dat.mdf”) is located on my Azure storage.

Credential for Windows Azure Storage

As I was testing out SQL Server 2014 CTP 2 and playing around with hybrid database (one or more data / log files stored on Azure and the rest stored on-premise), I found out that the tutorial on MSDN on creating a Credential was not clear. You need to create a Credential in SQL Server 2014 database instance on-premise in order to access / create the file on Azure.

The CREATE CREDENTIAL syntax that lets you access storage on Windows Azure is

CREATE CREDENTIAL [<path to the container>]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE'
SECRET = '<SAS Key>'

As an example, I have a storage account, “mssqlgirldbstorage’ with a URL of https://mssqlgirldbstorage.blob.core.windows.net. In this storage, I have a couple of containers called “data” and “log”.

Below is the CREATE CREDENTIAL statement I execute for the “data” container

CREATE CREDENTIAL [https://mssqlgirldbstorage.blob.core.windows.net/data]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE'
SECRET = 'sr=c&si=sqlpolicy&sig=89oywMCStX4iQrIGYVIl%2Bfh3cMtEm5ZA3fSDxh2w8najd9';

The sig value above is just a random generated key that I have created, so it won’t work for you. To obtain the SAS Key that you can use as the SECRET value, you can use Azure Storage Explorer tool. The documentation (see page 18 – 20) on how to create a policy and SAS key is very good.

Please note that when you use Azure Sotrage Explorer, the Signature generated looks like below. You only want to use the value that starts with sr=c to the end of the URI as the SECRET value in the CREATE CREDENTIAL statement.

https://mssqlgirldbstorage.blob.core.windows.net/data?sr=c&si=sqlpolicy&sig=89oywMCStX4iQrIGYVIl%2Bfh3cMtEm5ZA3fSDxh2w8najd9

You can create a credential using SQL Server Management Studio (SSMS) too, but you still need to use Azure Storage Explorer to create the Policy and to generate the SAS Key based on the policy. In the example below, I am creating a credential to access my “log” container on my Azure storage. Please make sure that you enter “SHARED ACCESS SIGNATURE” as the Identity value on SSMS as shown here.

Wrap Up

Credential is needed for creating a hybrid SQL Server 2014 database, where one or more database file is located on Azure while creating a database on on-premise server.

Get familiar with Azure Storage Explorer to begin with when learning / exploring your data on Azure. The UI is really good.

Further Reading

Tutorial: SQL Server Data Files in Windows Azure Storage service on MSDN

Azure Storage Explorer on Codeplex