— these commands execute against a single database…
— …not a whole server\instance
USE MyDatabase
GO

–create a role that will have one permission (execute) granted
CREATE ROLE db_exec
GO

–grant execute permissions to our newly created role
GRANT EXECUTE TO db_exec
GO

–add a single user (MySqlUser) to the role
EXEC sp_addrolemember ‘db_exec’, ‘MySqlUser’
GO

Advertisements

I created a folder in my solution named “UserControls”. That is where the following .ascx files are located:

StateFilter.ascx

<%@ Control Language=”C#” AutoEventWireup=”true” CodeBehind=”StateFilter.ascx.cs” Inherits=”Sandbox2010.UserControls.StateFilter” %>

<asp:DropDownList ID=”DdlState” runat=”server” DataSourceID=”DsState”
DataTextField=”Name” DataValueField=”StateProvinceID” AutoPostBack=”True”
onselectedindexchanged=”DdlState_SelectedIndexChanged”>
</asp:DropDownList>

<asp:SqlDataSource ID=”DsState” runat=”server”
ConnectionString=”<%$ ConnectionStrings:AdventureWorks2012_ConnectionString %>”
SelectCommand=”
select ” as StateProvinceID, ‘(Select a State)’ as Name
UNION ALL
SELECT DISTINCT StateProvinceID, Name
FROM Person.StateProvince
WHERE ISNULL(@CountryRegionCode, ”) = ” or CountryRegionCode = @CountryRegionCode ORDER BY Name”
onselecting=”DsState_Selecting”>
<SelectParameters>
<asp:Parameter Name=”CountryRegionCode” Type=”String” />
</SelectParameters>
</asp:SqlDataSource>

StateFilter.ascx.cs

using System;
using System.Web.UI.WebControls;
using System.ComponentModel;

namespace Sandbox2010.UserControls
{
public partial class StateFilter : System.Web.UI.UserControl
{
public event SelectedIndexChangedEventHandler SelectedIndexChanged;
public delegate void SelectedIndexChangedEventHandler(object sender, SelectedIndexChangedEventArgs args);

public string CountryRegionCode { get; set; }

public ListItem SelectedItem
{
get { return DdlState.SelectedItem; }
}

public string SelectedValue
{
get { return DdlState.SelectedValue; }
}

[Description(“CSS Class”), Category(“Configuration”)]
public string CssClass { get; set; }

protected void Page_Load(object sender, EventArgs e)
{
DdlState.CssClass = CssClass;
}

protected void DdlState_SelectedIndexChanged(object sender, EventArgs e)
{
if (this.SelectedIndexChanged != null)
{
this.SelectedIndexChanged(this, new SelectedIndexChangedEventArgs(DdlState.SelectedItem));
}
}

protected void DsState_Selecting(object sender, SqlDataSourceSelectingEventArgs e)
{
e.Command.Parameters[“@CountryRegionCode”].Value = string.IsNullOrEmpty(CountryRegionCode) ? string.Empty : CountryRegionCode;
}
}
}

CountryFilter.ascx

<%@ Control Language=”C#” AutoEventWireup=”true” CodeBehind=”CountryFilter.ascx.cs” Inherits=”Sandbox2010.UserControls.CountryFilter” %>

<asp:DropDownList ID=”DdlCountry” runat=”server” DataSourceID=”DsCountry”
DataTextField=”Name” DataValueField=”CountryRegionCode” AutoPostBack=”True”
onselectedindexchanged=”DdlCountry_SelectedIndexChanged”>
</asp:DropDownList>

<asp:SqlDataSource ID=”DsCountry” runat=”server”
ConnectionString=”<%$ ConnectionStrings:AdventureWorks2012_ConnectionString %>”
SelectCommand=”
select ” as CountryRegionCode, ‘(Select a Country)’ as Name
UNION ALL
select distinct c.CountryRegionCode, c.Name
from
Person.CountryRegion as c
join Person.StateProvince as s
on c.CountryRegionCode = s.CountryRegionCode
order by Name
“>
</asp:SqlDataSource>

CountryFilter.ascx.cs

using System;
using System.Web.UI.WebControls;
using System.ComponentModel;

namespace Sandbox2010.UserControls
{
public partial class CountryFilter : System.Web.UI.UserControl
{
public event SelectedIndexChangedEventHandler SelectedIndexChanged;
public delegate void SelectedIndexChangedEventHandler(object sender, SelectedIndexChangedEventArgs args);

public string CountryRegionCode { get; set; }

public ListItem SelectedItem
{
get { return DdlCountry.SelectedItem; }
}

public string SelectedValue
{
get { return DdlCountry.SelectedValue; }
}

[Description(“CSS Class”), Category(“Configuration”)]
public string CssClass { get; set; }

protected void Page_Load(object sender, EventArgs e)
{
DdlCountry.CssClass = CssClass;
}

protected void DdlCountry_SelectedIndexChanged(object sender, EventArgs e)
{
if (this.SelectedIndexChanged != null)
{
this.SelectedIndexChanged(this, new SelectedIndexChangedEventArgs(DdlCountry.SelectedItem));
}
}
}
}

I created a web form that contains my user controls and a text box that displays the final selected drop down list option:

WebForm1.aspx

<%@ Page Language=”C#” AutoEventWireup=”true” CodeBehind=”WebForm1.aspx.cs” Inherits=”Sandbox2010.WebForm1″ %>
<%@ Register Src=”UserControls/StateFilter.ascx” TagPrefix=”uc” TagName=”StateFilter”%>
<%@ Register Src=”UserControls/CountryFilter.ascx” TagPrefix=”uc” TagName=”CountryFilter”%>
<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd”&gt;
<html xmlns=”http://www.w3.org/1999/xhtml”&gt;
<head runat=”server”>
<title></title>
<link rel=”Stylesheet” href=”Styles/Rubrik.css” />
</head>
<body>
<form id=”form1″ runat=”server”>
<br /><br />
<uc:CountryFilter id=”CountryFilter1″ runat=”server” onselectedindexchanged=”CountryFilter1_SelectedIndexChanged” />
<br /><br />
<uc:StateFilter id=”StateFilter1″ runat=”server” onselectedindexchanged=”StateFilter1_SelectedIndexChanged” />
<br /><br />
You selected:<br />
<asp:TextBox ID=”MySelection” runat=”server”></asp:TextBox>
</form>
</body>
</html>

WebForm1.aspx.cs

using System;
using Sandbox2010.UserControls;

namespace Sandbox2010
{
public partial class WebForm1 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}

protected void CountryFilter1_SelectedIndexChanged(object sender, SelectedIndexChangedEventArgs e)
{
if (e.SelectedItem != null)
{
StateFilter1.CountryRegionCode = e.SelectedItem.Value;
StateFilter1.DataBind();
}
}

protected void StateFilter1_SelectedIndexChanged(object sender, SelectedIndexChangedEventArgs e)
{
if (e.SelectedItem != null)
{
MySelection.Text = e.SelectedItem.Text;
}
}
}
}

 

 

This code does not filter by folder path, so if you have more than one report on the server with the same report name, one report will be chosen arbitrarily and only one subscription will be executed.

declare
@report_name as varchar(100) = ‘My Report Name’
,@my_job_name as uniqueidentifier

–get job (subscription) GUID
SELECT top 1 @my_job_name = Schedule.ScheduleID
FROM ReportServer.dbo.Subscriptions
INNER JOIN ReportServer.dbo.ReportSchedule
ON ReportSchedule.SubscriptionID = Subscriptions.SubscriptionID
INNER JOIN ReportServer.dbo.Schedule
ON ReportSchedule.ScheduleID = Schedule.ScheduleID
INNER JOIN ReportServer.dbo.[Catalog]
ON ReportSchedule.ReportID = [Catalog].ItemID
AND Subscriptions.Report_OID = [Catalog].ItemID
WHERE
[Catalog].Name = @report_name

–email subscription report
EXEC msdb.dbo.sp_start_job @job_name = @my_job_name

Use the following steps to encrypt/decrypt the connectionStrings section in a web.config file:

  1. Open the web.config file and write down the “targetFramework” attribute value in the <compilation> section. (ex: 4.0)
  2. Start -> All Programs -> Accessories -> (right click) Command Prompt -> Run as administrator -> Yes
  3. Enter “cd\” to get to the root folder
  4. In the following step, replace “MyTargetFramwork” with the available version number that best matches the “targetFramework” value you wrote down in step 1. (ex: v4.0.30319)
  5. Enter “cd Windows\Microsoft.NET\Framework\MyTarketFramework\”
  6. To choose a web.config file based on file location, skip to step 7 and 8; To choose a web.config file based on IIS web application path, skip to step 9 and 10.
  7. To encrypt the connectionStrings section, enter: aspnet_regiis –pef “connectionStrings” C:\inetpub\wwwroot\MyWebApplicationPath
  8. To decrypt the connectionStrings section, enter: aspnet_regiis –pdf “connectionStrings” C:\inetpub\wwwroot\MyWebApplicationPath
  9. To encrypt the connectionStrings section, enter: aspnet_regiis –pe “connectionStrings” – app “/MyWebApplicationPath
  10. To decrypt the connectionStrings section, enter: aspnet_regiis –pd “connectionStrings” – app “/MyWebApplicationPath

The <connectionStrings> section of the web.config file should be encrypted/decrypted now.

After deploying a new ASP.NET web application with Windows authentication, hosted by IIS, I encountered a repeated “Authentication Required” popup folled by a 401 Error screen.

authreq401error01

Here are some of the configuration settings I used to resolve the issue:

authreq401error02

IIS -> MyServer -> Application Pools -> (right click) Add Application Pool
authreq401error03

By default, IIS will create a virtual account (a server account, not a domain account) named “IIS AppPool\MyApplicationPool”

 

IIS-> MyServer -> Sites -> MyWebSite -> MyApplication (right click) -> Manage Application -> Advanced Settings

Set “Application Pool” to “MyApplicationPool”
IIS -> MyServer -> Sites -> MyWebSite -> MyApplication -> .NET AuthorizationRules
authreq401error04

IIS -> MyServer -> Sites -> MyWebSite -> MyApplication -> Authentication -> (right click) Edit Permissions -> Security -> Edit -> Add
Type “IIS AppPool\MyApplicationPool” in the “Enter the object names to select” box and click “Check Names”

Locations -> MyServer -> OK -> OK -> OK

Set “Full control” to “Allow” and click “OK”

 

IIS -> MyServer -> Sites -> MyWebSite -> MyApplication -> Authentication -> (right click) Open Feature -> Anonymous Authentication (right click) -> Edit -> Application pool identity -> OK

IIS -> MyServer -> Sites -> MyWebSite -> MyApplication -> Authentication -> (right click) Open Feature -> Windows Authentication (right click) -> Advanced Settings

Set “Extended Protection” = “Accept” and “Enable Kernel-mode authentication” = “Checked”
IIS -> MyServer -> Sites -> MyWebSite -> MyApplication -> Authentication -> (right click) Open Feature -> Windows Authentication (right click) -> Providers -> NTLM -> Move Up -> OK
authreq401error05

This example demonstrates how to get the current logged in windows user by using an ASP.NET Web Form named “MyWebForm.aspx” and a code behind page named “MyWebForm.aspx.cs”. The Web Form has a single text box control named “TextBox1”. “this” represents System.Web.UI.Page, which has a public instance variable named “Request” of type System.Web.HttpRequest.
using System;

namespace MyNamespace
{
public partial class MyWebForm : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
TextBox1.Text = this.Request.LogonUserIdentity.Name;
}
}
}

First, develop a SSRS report, deploy it to Report Manager, and run the report to confirm that it works properly. Create/open an ASP.NET Web Application. Create/open a Web Form in Visual Studio:

Toolbox -> Reporting. Drag a ReportViewer control onto the Web Form.

Ensure that adding the ReportViewer to the page caused the following updates (you will have to reload web.config if it is already open in Visual Studio):

  1. A new reference for Microsoft.ReportViewer.WebForms was added in: Solution Explorer -> MySolution -> References
  2. A new register assembly directive was added to the .aspx form:
    1. <%@ Register Assembly=”Microsoft.ReportViewer.WebForms, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a” Namespace=”Microsoft.Reporting.WebForms” TagPrefix=”rsweb” %>
  3. The web.config file has the following XML elements (highlighed in bold text):

 

<?xml version=”1.0″?>

<configuration>
<connectionStrings>
<add name=”ApplicationServices”
connectionString=”data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnetdb.mdf;User Instance=true”
providerName=”System.Data.SqlClient” />
</connectionStrings>

<system.web>
<httpHandlers>
<add path=”Reserved.ReportViewerWebControl.axd” verb=”*” type=”Microsoft.Reporting.WebForms.HttpHandler, Microsoft.ReportViewer.WebForms, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a”
validate=”false” />
</httpHandlers>
<compilation debug=”true” targetFramework=”4.0″>
<assemblies>
<add assembly=”Microsoft.ReportViewer.WebForms, Version=10.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A” />
<add assembly=”Microsoft.ReportViewer.Common, Version=10.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A” />
<add assembly=”Microsoft.Build.Framework, Version=4.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A” />
<add assembly=”System.Management, Version=4.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A” />
</assemblies>
<buildProviders>
<add extension=”.rdlc” type=”Microsoft.Reporting.RdlBuildProvider, Microsoft.ReportViewer.WebForms, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a” />
</buildProviders>
</compilation>

<authentication mode=”Windows”>
<forms loginUrl=”~/Account/Login.aspx” timeout=”2880″ />
</authentication>

<membership>
<providers>
<clear />
<add name=”AspNetSqlMembershipProvider” type=”System.Web.Security.SqlMembershipProvider” connectionStringName=”ApplicationServices” enablePasswordRetrieval=”false” enablePasswordReset=”true” requiresQuestionAndAnswer=”false” requiresUniqueEmail=”false” maxInvalidPasswordAttempts=”5″ minRequiredPasswordLength=”6″ minRequiredNonalphanumericCharacters=”0″ passwordAttemptWindow=”10″ applicationName=”/” />
</providers>
</membership>

<profile>
<providers>
<clear />
<add name=”AspNetSqlProfileProvider” type=”System.Web.Profile.SqlProfileProvider” connectionStringName=”ApplicationServices” applicationName=”/” />
</providers>
</profile>

<roleManager enabled=”false”>
<providers>
<clear />
<add name=”AspNetSqlRoleProvider” type=”System.Web.Security.SqlRoleProvider” connectionStringName=”ApplicationServices” applicationName=”/” />
<add name=”AspNetWindowsTokenRoleProvider” type=”System.Web.Security.WindowsTokenRoleProvider” applicationName=”/” />
</providers>
</roleManager>

</system.web>

<system.webServer>
<modules runAllManagedModulesForAllRequests=”true” />
<validation validateIntegratedModeConfiguration=”false” />
<handlers>
<add name=”ReportViewerWebControlHandler” preCondition=”integratedMode” verb=”*” path=”Reserved.ReportViewerWebControl.axd” type=”Microsoft.Reporting.WebForms.HttpHandler, Microsoft.ReportViewer.WebForms, Version=10.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a” />
</handlers>
</system.webServer>
</configuration>

Get the value that we will use for the ReportViewer “ReportPath” property. Browse to an existing published report in Report Manager, copy the ItemPath value from the URL in the address bar. Replace all URL escape codes with the escaped characters. Example: “%2fMyFirstFolder%2fMySecondFolder%2fMy+Report+Name” becomes “/MyFirstFolder/MySecondFolder/My Report Name”.

Get the value that we will use for the ReportViewer “ReportServerUrl” property: Start -> (All Programs) -> Microsoft SQL Server -> Configuration Tools -> Reporting Services Configuration Manager. Enter the Server Name, click “Find”, select the Report Server Instance, and click “Connect”. Click “Web Service URL” in the left menu, then click the blue URL link on the page. The link should open in a browser and you should see the file system contents of your SSRS root folder. Copy the URL from the address bar in the browser. The link will probably be similar to: http://myReportServer/ReportServer.

Visual Studio -> MyWebForm.aspx -> Design mode -> ReportViewer -> Properties. Expand the “ServerReport” property. Set the following 2 properties:

  1. Set the “ReportServerUrl” property to the value that we determined in a previous step.
  2. Set the “ReportPath” property to the value that we determined in a previous step.
  3. Set the “ProcessingMode” property to “Remote” (this may not be necessary, based on your server architecture)

Visual Studio -> Toolbox -> AJAX Extensions: drag a ScriptManager control onto the web form. The ScriptManager does not require any configuration, it just needs to exist on the form.

Run the web application, browse to the web form containing the ReportViewer. You should see the report embedded in the Web Form.