[This article was contributed by the SQL Azure team.]

In this former blog post I discussed how to enable access to SQL Azure from PowerPivot. Now that access is enabled and the data is available to you, let’s examine how PowerPivot and SQL Azure work from a security perspective.

Accessing the Data

In order to use PowerPivot against your SQL Azure data you need to download the data into the Excel workbook. How to enable the user access to the SQL Azure firewall, and create a user account in SQL Azure for accessing the data is covered in this earlier blog post.

A Copy of the Data

When using PowerPivot the analysis of the data is done by PowerPivot and Excel on the user’s computer. In order to do that, the data is imported from SQL Azure to Excel by PowerPivot and stored in the Excel workbook. In fact all the data that is imported is readable by the user in Excel via the PowerPivot Window.

In other words, a copy of the data now exists in the Excel workbook and that data is outside the access control of SQL Azure and the SQL Azure Administrator. Even if the SQL Azure Administrator restricts the user’s access to SQL Azure after they downloaded the data, they can still access the data in their Excel workbook.

Encrypting the Excel Workbook

Once the data is in the Excel Workbook, the workbook can be protected using the encryption feature in Excel, for more about that click here. This allows the Excel user to password protect the workbook and the data inside it.

Connecting to SQL Azure

When PowerPivot connects to SQL Azure to import data, it is done through a secure connection. All traffic in or out of SQL Azure is encrypted, regardless of the client. This is done automatically in the .NET Data Provider for SQL Server when you create a connection with PowerPivot; see this blog post for information about creating a connection.

Login and Password Information

When you create a connection in PowerPivot you are asked for your login and password to SQL Azure. Right below the request for information is a check box that says: Save my password.

If you check this box, the password to SQL Azure is saved encrypted in the Excel workbook. The connection string is used in two places: by the PowerPivot user interface and by the VertiPaq storage engine that processes the data. The PowerPivot user interface doesn’t save the password in the Excel workbook; however it does store it in memory over the life of the application. If you close Excel, and reopen your workbook, and want to add additional tables (see this blog post for how to add additional table) you will have to reenter the password; the user interface requires it.

If you just refresh the data in the Excel workbook after you open it and you requested that the password be saved when you created the connection, the VertiPaq storage engine in the Excel has stored the password. During the refresh it can query SQL Azure and is able to import the new data. If you didn’t check the box to save the password when you created the connection, then you will be prompted for the password.

Bottom line: if you don’t want your SQL Azure password to be saved in the Excel workbook, don’t check the Save my password checkbox when you create the connection.

Summary

Do you have questions, concerns, comments? Post them below and we will try to address them.