Reporting Services with SQL Azure : Creating the SQL Azure Data Source

Continue with these steps:

1. In the Report Data window, right-click the Data Sources node, and select Add Data Source from the context menu, as shown in Figure 1.

   Figure 1. Adding a data source

   

2. In the Data Source Properties dialog, you define the type of connection and the connection properties. Select the "Embedded connection" option, and then click the Type down arrow. You read earlier that SQL Server 2008 R2 includes SQL Azure–specific providers, and this is where you find them. As shown in Figure 2, select the new data provider for SQL Azure called Microsoft SQL Azure (what else are you going to call it?).

When you select this provider, the Connection String text box defaults to

Encrypt=True;TrustServerCertificate=False

Although these two parameters and associated values are defaulted for you, it's recommended that you not change them. The Encrypt parameter indicates that SQL Server will use SSL encryption for all data sent between the server and client if the server has a cert installed. The TrustServerCertificate property tells the transport layer to use SSL to encrypt the channel and bypass walking the cert chain to validate trust. When both Encrypt and TrustServerCertificate are set to True, the encryption level specified on the server is used even if the value of the Encrypt parameter in this connection string is set to False.

Figure 2. Data Source Properties dialog

However, even with this default string set, you still need to add the SQL Azure connection information, so continue as follows:

1. Click the Edit button to open the Connection Properties dialog, shown in Figure 3. Enter your SQL Azure database, username, and password. You should know by now that you can't use Windows Authentication with SQL Azure, so make sure you enter the SQL Azure account username and password.

   Figure 3. Connection Properties dialog

   

2. Select (or type in) the database you want to pull the data from, and then click Test Connection to ensure that all your settings are correct.

3. Click OK to close this dialog and take you back to the Data Source Properties dialog. It should now look like Figure 4, which shows the appropriate connection type and connection string.

   Figure 4. Completed Data Source Properties dialog

   

Granted, these steps are no different than those for connecting to a local database. But although the steps are the same, some of the key selection components are different, such as those in Figures 2 and 3, where you select the specific Microsoft SQL Azure provider and the SQL Azure–specific connection information.

Your data source definition also allows you to specify the credentials with which to connect to your data source. Selecting Credentials at left in the Data Source Properties dialog shows you four options.

• Windows Authentication (integrated security)

• Prompt for credentials

• Specify a user name and password

• Do not use credentials

Obviously, you need to use credentials, so not specifying credentials isn't the option you want. And integrated security isn't available with Azure, so that won't work either. You can either prompt for credentials or specify a username and password. The default value is to prompt for credentials; if you leave that setting, the report prompts you to enter a username and password every time you run the report. Continue as follows:

1. Best practice says that in a production environment, you should use integrated security. But because that isn't an option with SQL Azure, select the "Specify a user name and password" option, and enter the username and password of an account that has access to the appropriate database.

With your data source created, you now need to add a dataset for the report. For each data source, you can create one or more datasets. Each dataset specifies the fields from the data source that you would like to use in the report. The data set also contains, among other things, the query used to get the data and any query parameters to filter the data:

1. In the Report Data window, right-click the Datasets node, and select Add Dataset from the context menu. Doing so opens the Dataset Properties window, shown in Figure 5.

2. The Query page of the Dataset Properties window allows you to do two primary things: specify the data source on which this dataset is based, and specify the query type and associated query. For this example, base your dataset on the data source you created earlier. The query type is Text, meaning you type a T-SQL statement in the Query field. For this example, you want to return everything (all rows and columns) from the Users table, so enter the SELECT statement shown in Figure 5. The Name of the dataset defaults to DataSet1, which is fine for this example. Click OK.

   Figure 5. Dataset Properties dialog

   

There is nothing else you need to do for your dataset; you're ready to define and lay out your report.