Depending on the size of your organization, managing all the user requests to add, update, and delete SQL Server Reporting Services (SSRS) 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." 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.
Step 3.Next, you create the trigger on the Subscriptions table to translate the user login to a valid email address. Listing 2 shows the FIX_EMAIL_TR trigger code, which fires when a user adds a new subscription to Report Server. Reporting Services offers two types of subscriptions, so the trigger'sWHERE clause checks and updates only the Send email to type, not the File system publication subscriptions type. After verifying that the added subscription is the email type, the trigger stores the login in a variable and selects the correct email address from the ADSI_USERS table. The trigger then updates the new subscription with the correct email address.
Step 4. You're now ready to create the stored procedure that deletes subscriptions for users no longer in AD.The nightly SQL Server Agent job that you create in the next step will execute this stored procedure after running the stored procedure that updates the ADSI_USERS table. Listing 3 shows the code for the DELETE _ SUBSCRIPTIONS_SP stored procedure.The procedure parses all email-type subscriptions and compares them to the list of email addresses from AD. If the email address for the subscription doesn't exist in the ADSI_USERS table, the procedure removes the subscription.The code also ensures that the transactions won't commit if errors occur; you can tailor the text in the RAISERROR statement to your needs.
Step 5. The final step is to schedule a SQL Server Agent job to execute the stored procedures that load the ADSI_USERS table and delete subscriptions. Web Listing 1 shows the Manage_RS_ Subscriptions job definition. The code first adds a job category called Batch if that category doesn't already exist, then determines whether a job with the same name exists, and if so, drops the existing one. The job steps specify the job, category, and type as well as the notification method. If an operator hasn't been defined in SQL Server Agent or you don't want to net send to an operator, eliminate this notification code. Last, the definition sets the job schedule, which you can modify to fit with nightly batch processing and database maintenance.
Note that you need to replace the domain and account names as well as the log file path and notification name in the code example.You might also want to adjust the execution time based on your nightly batch cycle.You can run the code manually, or you can use Enterprise Manager to add a new job that performs these tasks. Remember that SQL Server Agent must be enabled and running to add and schedule jobs.
Reducing the Load
Scheduling the SQL Server Agent job to run during off-peak hours alleviates any additional load on AD. And besides increasing in-house and mobile user access to reports, using subscriptions can reduce the load on SQL Server by enabling scheduled delivery of reports during off-peak database-access times.
The only caveat for user-managed subscriptions is that users who already have email-type subscriptions will get an error message when they try to update the subscription because their login won't match their email address. Users who want to modify an email-type subscription will need to delete the old subscription, then recreate the subscription, incorporating any new schedule or file-format changes.This task might be a one-time event, but you still need to train users about best practices for creating subscriptions so that they can successfully manage report delivery. Knowing what report delivery formats are available and understanding when to schedule subscriptions empowers users to better help themselves.
