Configuring SQL Server 2012 Data Alerts

Data alerts is one of the most exciting new features of SQL Server 2012 that allows users set up alerts based on Reporting Services reports.  These alerts generate email notifications bases on the query parameters specified.  Several things need to be configured in order to enable this functionality:

1. Report Datasources must use stored user credentials..
   1. In order to configure that, the easiest way is to click on the arrow that appers when you mouse over the report and then pick Edit in Rerport Builder option
   2. Click on Data Sources and right click on the properties of the data source used in the report.
   3. Pick the second radio box option and enter the required credentials.. (don’t forget that if your source is Analysis Services cube, then you have to specify Windows Credentials and check the corresponding check box below)
   4. Click OK and then Save at the top bar
2. Reporting Services Service application must be configured as well
   1. Go to the Central Administration and Click on Manage Service Applications
   2. Click on your Reporting Services service application and click Manage button on the ribbon above (or double click the SSRS service application) (Note, if the reporting services application is not on the list then it has not been created.  This article will get you started)
   3. three bottom sections need to be configured
      1. Execution Account – make sure to specify this account to enable the use of report data sources that do not require credentials or to connect to remote servers that store external images used in reports. Be sure to specify a domain user account with minimal permissions for performing read-only operations. Avoid using an account that has more permissions than you actually need. The account you specify should be different from the service account to ensure you do not compromise security on your report server instance.
      2. E-mail settings – specify the URL for the outbound SMTP server, and also the email address that the alert emails will be set from (this is just a caption, the actual email account does not have to be created) so any email address will work
      3. Provision Subscriptions and Alerts – Click on Download Script botton.  That will open the SQL Server management studio and paste the script into the query window… Just run the scipt and you are done with that step.  After that, specify the account to connect to the SQL Server Agent (if you are using a windows account, make sure to check the “User as Windows Credentials” box

You should be good to go to start creating Alerts.  Click on Actions menu option on the Report Tool Bar and follow the Wizard to specify the alert criteria and deliver options for your alerts.