Self-Service Subscriptions

Depending on the size of your organization, managing all the user requests to add, update, and delete SQL Server Reporting Services subscriptions could consume your days—and nights. Fortunately, Reporting Services lets you give users the power to manage their own subscriptions.You simply use the Reporting Services Configuration tool to allow report delivery through email. And as long as the security credentials for the report's data source are stored on the server, users can add or delete subscriptions by clicking the New Subscription button under the Subscriptions tab on the report page. However, implementing user-managed subscriptions raises two questions:

• How do you translate Reporting Services (Windows) logins to email addresses for report delivery?
• How do you manage the subscriptions of employees who leave?

For security, some organizations require user logins to be different from Active Directory (AD) email aliases. Using a login that's different from the email alias helps reduce the risk of hackers using a known login to break into the system. But Reporting Services uses a user's login to create the email alias for a subscription. This article outlines a five-step process for linking AD data with Reporting Services subscriptions so that Reporting Services can translate a user's login to an email address without administrator intervention and without compromising AD security. The solution also automates the removal of subscriptions for employees who are no longer in AD.

5 Steps to Automation
As Figure 1, illustrates, using AD to automatically translate logins to email addresses and verify that current subscriptions match active users is simple and efficient. The solution involves creating a linked server to AD, using a SQL Server Agent batch job to perform a nightly load of a table of users' logins and emails from AD and delete subscriptions that no longer match a user in AD, and implementing a trigger on the Subscriptions table in the Report Server database to translate the login to an email address whenever a user adds a subscription.

Figure 2 shows the Reporting Services screen for adding a subscription. The "To:" field contains the user'sWindows login.When a user clicks the New Subscription button, the "To:" field defaults to his or her login, which might not be a routable email address, and can't be modified. But with the AD solution, after the user enters the subscription parameters and clicks OK, the trigger fires and updates the subscription with the email address from the Subscriptions table. Let's look at each step in the solution.

Step 1. The first step in automating usermanaged subscriptions is to create the linked server to AD Services, as described in the Microsoft article "Distributed Query" at http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adsi/adsi/distributed_ query.asp. To allow distributed query permissions, you need to add a linked server login using a domain account that has permissions in AD. AD access uses the LDAP dialect. The first DC= argument corresponds to the Base Distinguished Name. For example, http://www.polkaudio.com would be DC=polkaudio. To create the linked server and login, execute the following code, substituting the appropriate network domain, distinguished name, login, and password-for your scenario:

EXEC sp_addlinkedserver 'ADSI',
  'Active Directory Services 2.5',
    'ADSDSOObject',
     'adsdatasource' 

GO

EXEC sp_addlinkedsrvlogin 'ADSI',
  false, 'DOMAIN\Administrator',
    'CN=Administrator,
      DC=DISTINGUISHED_NAME,
       DC=com', 'PASSWORD' 

GO

I first tried to update the Subscriptions table through an OPENQUERY statement to Active Directory Service Interfaces (ADSI) in the trigger, but that approach resulted in a permissions error because my login credentials didn't validate in AD. Instead, I needed to create a table and execute the trigger against that table. Note that the LDAP connection through the linked server to AD is sensitive to SQL syntax and permissions.

Step 2. Now, you're ready to create the stored procedure that the nightly SQL Server Agent job uses to load the ADSI_USERS table of user names and email addresses from AD. Listing 1 shows the code for the LOAD_ADSI_ USERS_SP stored procedure. Note that I used all caps for the naming convention to distinguish this user-created stored procedure from the Reporting Services stored procedures. I also added the letters sp at the end so that the server doesn't look for the stored procedure in the master database first. In addition, you should specify that the procedure is created by dbo.

The procedure first checks to see whether the table already exists; if the table does exist, the procedure drops it.Then, for all users who have email addresses in AD, the procedure inserts the users' names, email addresses, and nicknames into the ADSI_ USERS table. Last, the procedure creates an index on the table, which speeds up the lookup process the trigger uses to translate a user login to an email address when a user adds a subscription. After you create the stored procedure, execute it to create, load, and index the table. The table will contain only users who have an email address.