Connecting to Secure Databases

If your report’s data source is a secure database, you need to take an additional step to pass the user credentials to the report. Otherwise, the user will always be prompted to enter their login information every time they preview the report.

To connect to a secure data source, first make sure to open the form with your report viewer on it. Drag and drop a SQLDataConnection control onto the form. This control is found in the Toolbox window under the Data tab.

After adding the SQLDataConnection, click on its Smart Tasks panel (it has one too) and walk through the steps of setting the security credentials necessary to open the report.

The last step requires that you create the SQL SELECT statement that extracts data from the report. You have to specify the tables, linking properties, and fields that will be printed. Figure 3-3 shows the Configure Data Source dialog box where you enter this information.

Figure 3-3. Selecting data with the Configure Data Source dialog box.

This dialog box lets you build the entire SQL statement and it displays the actual Transact SQL statement at the bottom as it is created. It doesn’t take much thought to realize that this is going to create a huge maintenance nightmare for you. Every time you add tables or fields to your report, you will have to modify the SQLDataConnection object to match the report’s data source. Not very practical, if you ask me.