Correlation and causality – The bases of a good prediction

 

The Hidden Biases of Data :)

Enabling Kerberos Authentication for Reporting Services/SSAS

 

Nice Article from Rob_Carrol at http://blogs.technet.com/b/rob/archive/2011/11/23/enabling-kerberos-authentication-for-reporting-services.aspx:

By default, Reporting Services uses Windows Integrated Authentication, which includes the Kerberos and NTLM protocols for network authentication. Additionally, Windows Integrated Authentication includes the negotiate security header, which prompts the client to select Kerberos or NTLM for authentication. The client can access reports which have the appropriate permissions by using Kerberos for authentication. Servers that use Kerberos authentication can impersonate those clients and use their security context to access network resources.

You can configure Reporting Services to use both Kerberos and NTLM authentication; however this may lead to a failure to authenticate. With negotiate, if Kerberos cannot be used, the authentication method will default to NTLM. When negotiate is enabled, the Kerberos protocol is always used except when:

• Clients/servers that are involved in the authentication process cannot use Kerberos.
• The client does not provide the information necessary to use Kerberos.

An in-depth discussion of Kerberos authentication is beyond the scope of this post, however when users execute reports that are configured to use Windows Integrated Authentication, their logon credentials are passed from the report server to the server hosting the data source. Delegation needs to be set on the report server and Service Principle Names (SPNs) set for the relevant services. When a user processes a report, the request must go through a Web server on its way to a database server for processing. Kerberos authentication enables the Web server to request a service ticket from the domain controller; impersonate the client when passing the request to the database server; and then restrict the request based on the user’s permissions. Each time a server is required to pass the request to another server, the same process must be used.

Kerberos authentication is supported in both native and SharePoint integrated mode, but I’ll focus on native mode for the purpose of this post (I’ll explain configuring SharePoint integrated mode and Kerberos authentication in a future post). Configuring Kerberos avoids the authentication failures due to double-hop issues. These double-hop errors occur when a users windows domain credentials can’t be passed to another server to complete the user’s request. In the case of my customers, users were executing Reporting Services reports that were configured to query Analysis Services cubes on a separate machine using Windows Integrated security. The double-hop issue occurs as NTLM credentials are valid for only one network hop, subsequent hops result in anonymous authentication.

The client attempts to connect to the report server by making a request from a browser (or some other application), and the connection process begins with authentication. With NTLM authentication, client credentials are presented to Computer 2. However Computer 2 can’t use the same credentials to access Computer 3 (so we get the Anonymous login error). To access Computer 3 it is necessary to configure the connection string with stored credentials, which is what a number of customers I have worked with have done to workaround the double-hop authentication error.

However, to get the benefits of Windows Integrated security, a better solution is to enable Kerberos authentication. Again, the connection process begins with authentication. With Kerberos authentication, the client and the server must demonstrate to one another that they are genuine, at which point authentication is successful and a secure client/server session is established.

In the illustration above, the tiers represent the following:

• Client tier (computer 1): The client computer from which an application makes a request.
• Middle tier (computer 2): The Web server or farm where the client’s request is directed. Both the SharePoint and Reporting Services server(s) comprise the middle tier (but we’re only concentrating on native deployments just now).
• Back end tier (computer 3): The Database/Analysis Services server/Cluster where the requested data is stored.

In order to enable Kerberos authentication for Reporting Services it’s necessary to configure the relevant SPNs, configure trust for delegation for server accounts, configure Kerberos with full delegation and configure the authentication types for Reporting Services. These steps are outlined in greater detail in the “Manage Kerberos Authentication Issues in a Reporting Services Environment” whitepaper in the resources section at the end of this article.

Service Principle Names (SPNs) are unique identifiers for services and identify the account’s type of service. If an SPN is not configured for a service, a client account will be unable to authenticate to the servers using Kerberos. You need to be a domain administrator to add an SPN, which can be added using the SetSPN utility. For Reporting Services in native mode, the following SPNs need to be registered

–SQL Server Service SETSPN -S mssqlsvc/servername:1433 Domain\SQL

For named instances, or if the default instance is running under a different port, then the specific port number should be used.

–Reporting Services Service SETSPN -S http/servername Domain\SSRS SETSPN -S http/servername.domain.com Domain\SSRS

The SPN should be set for the NETBIOS name of the server and the FQDN. If you access the reports using a host header or DNS alias, then that should also be registered

SETSPN -S http/www.reports.com Domain\SSRS

–Analysis Services Service SETSPN -S msolapsvc.3/servername Domain\SSAS

Next, you need to configure trust for delegation, which refers to enabling a computer to impersonate an authenticated user to services on another computer:

Location	Description
Client	1. The requesting application must support the Kerberos authentication protocol. 2. The user account making the request must be configured on the domain controller. Confirm that the following option is not selected: Account is sensitive and cannot be delegated.
Servers	1. The service accounts must be trusted for delegation on the domain controller. 2. The service accounts must have SPNs registered on the domain controller. If the service account is a domain user account, the domain administrator must register the SPNs.

In Active Directory Users and Computers, verify that the domain user accounts used to access reports have been configured for delegation (the ‘Account is sensitive and cannot be delegated’ option should not be selected):

We then need to configure the Reporting Services service account and computer to use Kerberos with full delegation:

 

We also need to do the same for the SQL Server or Analysis Services service accounts and computers (depending on what type of data source you are connecting to in your reports).

Finally, and this is the part that sometimes gets over-looked, we need to configure the authentication type correctly for reporting services to use Kerberos authentication. This is configured in the Authentication section of the RSReportServer.config file on the report server.

<Authentication>

<AuthenticationTypes>

<RSWindowsNegotiate/>

</AuthenticationTypes>

<EnableAuthPersistence>true</EnableAuthPersistence>

</Authentication>

This will enable Kerberos authentication for Internet Explorer. For other browsers, see the link below. The report server instance must be restarted for these changes to take effect.

Once these changes have been made, all that’s left to do is test to make sure Kerberos authentication is working properly by running a report from report manager that is configured to use Windows Integrated authentication (either connecting to Analysis Services or SQL Server back-end).

Resources:

Manage Kerberos Authentication Issues in a Reporting Services Environment http://download.microsoft.com/download/B/E/1/BE1AABB3-6ED8-4C3C-AF91-448AB733B1AF/SSRSKerberos.docx

Configuring Kerberos Authentication for Microsoft SharePoint 2010 Products http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=23176

How to: Configure Windows Authentication in Reporting Services http://msdn.microsoft.com/en-us/library/cc281253.aspx

RSReportServer Configuration File http://msdn.microsoft.com/en-us/library/ms157273.aspx#Authentication

Planning for Browser Support http://msdn.microsoft.com/en-us/library/ms156511.aspx

• 4 Comments

2008, Reporting Services, Kerberos, Analysis Services

Using a Script Component Source to Consume a WebService

Thanks to Dennis and Jim for this original post at: http://dennysjymbo.blogspot.pt/2013/05/using-script-component-source-to.html

     When attempting to use XML as a data source in SSIS you really only have 2 solution (and this depends on whether or not this XML is coming from a web service). If your XML feed is coming from a web service, you can use the Web Service Task and set the output to either a file or a variable. If you set your file to a variable, or file, you can use the XML Task to perform various xpath queries, reformat using an XSLT, etc. The result of which can be set to other variables or a file. If set to a file, it can then be sourced using an XML  Source  and brought into your data flow. But what if you don’t want to use an XML file as your source? SSIS has no way of reading an XML feed from a web service directly into a data flow without custom coding. In this entry I’ll show an example of how this can be done using a Script Component source in a data flow.

     This solution is very similar to the JSON solution I wrote about, with a few changes to apply to XML.  The XML source we’re using is going to apply to metrics used in a call center. These attributes include employee login, date, calls offered, calls answered and calls missed. Our XML source will look something  like this:

<?xml version=”1.0″ encoding=”utf-8″?>

<DisplayStats>

  <DailyStat>

    <WebLogin>eli</WebLogin>

    <Date>2013-03-28T00:00:00</Date>

    <CallsOffered>4</CallsOffered>

    <CallsAnswered>4</CallsAnswered>

    <CallsMissed>0</CallsMissed>

  </DailyStat>

  <DailyStat>

    <WebLogin>rit</WebLogin>

    <Date>2013-03-28T00:00:00</Date>

    <CallsOffered>0</CallsOffered>

    <CallsAnswered>0</CallsAnswered>

    <CallsMissed>0</CallsMissed>

  </DailyStat>

  <DailyStat>

    <WebLogin>Dan</WebLogin>

    <Date>2013-03-28T00:00:00</Date>

    <CallsOffered>15</CallsOffered>

    <CallsAnswered>15</CallsAnswered>

    <CallsMissed>1</CallsMissed>

  </DailyStat>

  <DailyStat>

    <WebLogin>Pab</WebLogin>

    <Date>2013-03-28T00:00:00</Date>

    <CallsOffered>0</CallsOffered>

    <CallsAnswered>0</CallsAnswered>

    <CallsMissed>0</CallsMissed>

  </DailyStat>

  <DailyStat>

    <WebLogin>Rus</WebLogin>

    <Date>2013-03-28T00:00:00</Date>

    <CallsOffered>21</CallsOffered>

    <CallsAnswered>21</CallsAnswered>

    <CallsMissed>0</CallsMissed>

  </DailyStat>

</DisplayStats>

This comes from an ASP.NET web service I built using SOAP:

You can see from this screenshot that the web service takes one parameter, offerDate, with a date format. We're going to want to make this a variable that we can alter at run time every time this package is executed. This can be done through values returned from an Execute Sql Task from a database, set via a function, etc. Regardless of how its implemented, in this example were going to hard code the variable to 03/28/2013:

 

     Now that we have a variable for offerDate, let’s set up our script component. Drag a script component onto the design surface of a data flow. When prompted, select source as the script component type:

On the script screen, we want to make sure to add our SSIS variable in ReadOnlyVariables:

     Next, let’s configure the output buffer for this script component. On the input and output screen, click on the default output and add some columns. These will represent the XML elements were going to get back from our web service:

This screen helps us to define what our output is going to look like. The columns that will represent our XML elements are as follows: WEBLOGIN DT_STR 50,  DATEDT_DBTIMESTAMP, CALLSOFFERED DT_I4, CALLSANSWERED DT_I4 and CALLSMISSEDDT_I4. With this all set up, we can go back to the script screen, click on the Edit Script button and begin coding. In the main.cs file, paste the following code:

using System;

using System.Data;

using Microsoft.SqlServer.Dts.Pipeline.Wrapper;

using Microsoft.SqlServer.Dts.Runtime.Wrapper;

using System.Net;

using Microsoft.SqlServer.Dts.Runtime;

using System.Windows.Forms;

using System.IO;

using System.Xml.Serialization;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]

public class ScriptMain : UserComponent

{

    public override void CreateNewOutputRows()

    {

        //Get SSIS Variables

         string offerDate = Variables.OFFERDATE;

        //Set Webservice URL

        string wUrl = “http://YOUR WEB SERVICE URI/Respiratory/ResService.asmx/getAgentMetricsXml?offerDate=”+offerDate;

        try

        {

            //Call getWebServiceResult to return our DailyStat array

            DailyStats outPutStats = getWebServiceResult(wUrl);

            //For each group of metrics output records

            foreach (var stats in outPutStats.MDailyStat)

            {

                Output0Buffer.AddRow();

                Output0Buffer.WEBLOGIN = stats.WebLogin;

                Output0Buffer.DATE =stats.Date;

                Output0Buffer.CALLSANSWERED =stats.CallsAnswered;

                Output0Buffer.CALLSOFFERED = stats.CallsOffered;

                Output0Buffer.CALLSMISSED = stats.CallsMissed;

            }

        }

        catch (Exception e)

        {

            failComponent(e.ToString());

        }

    }

    //Method to return our DailyStat array

    private DailyStats getWebServiceResult(string wUrl)

    {

        HttpWebRequest httpWReq = (HttpWebRequest)WebRequest.Create(wUrl);

        HttpWebResponse httpWResp = (HttpWebResponse)httpWReq.GetResponse();

        DailyStats xmlResponse = null;

        try

        {

            //Test the connection

            if (httpWResp.StatusCode == HttpStatusCode.OK)

            {

                Stream responseStream = httpWResp.GetResponseStream();

                //Set xmlString using a stream reader

                using (StreamReader reader = new StreamReader(responseStream))

                {

                    //Deserialize our XML

                    XmlSerializer sr = new XmlSerializer(typeof(DailyStats));

                    xmlResponse = (DailyStats)sr.Deserialize(reader);

                    reader.Close();

                }

            }

            //Output connection error message

            else

            {

                failComponent(httpWResp.StatusCode.ToString());

            }

        }

        //Output xml parsing error

        catch (Exception e)

        {

            failComponent(e.ToString());

        }

        return xmlResponse;

    }

    //Outputs error message

    private void failComponent(string errorMsg)

    {

        bool fail = false;

        IDTSComponentMetaData100 compMetadata = this.ComponentMetaData;

        compMetadata.FireError(1, “Error Getting Data From Webservice!”, errorMsg, “”, 0, out fail);

    }

}

//Class to hold elements within <DailyStat>

public class DailyStat

{

    public string WebLogin { get; set; }

    public DateTime Date { get; set; }

    public int CallsOffered { get; set; }

    public int CallsAnswered { get; set; }

    public int CallsMissed { get; set; }

}

//Class to hold our array of <DailyStat>

[XmlRootAttribute(“DisplayStats”)]

public class DailyStats

{

    [XmlElement(“DailyStat”)]

    public DailyStat[] MDailyStat { get; set; }

}

After calling our web service and getting our stream of XML, we deserialize into a DisplayStats object:

  XmlSerializer sr = new XmlSerializer(typeof(DisplayStats));

  xmlResponse = (DisplayStats)sr.Deserialize(reader);

This object is returned to the calling method. We then loop through the DailyStat[]array, within the DisplayStats object, and output the attributes to the output buffer:

 //Call getWebServiceResult to return our DailyStat array

            DisplayStats outPutStats = getWebServiceResult(wUrl);

            //For each group of metrics output records

            foreach (var stats in outPutStats.MDailyStat)

            {

                Output0Buffer.AddRow();

                Output0Buffer.WEBLOGIN = stats.WebLogin;

                Output0Buffer.DATE =stats.Date;

                Output0Buffer.CALLSANSWERED =stats.CallsAnswered;

                Output0Buffer.CALLSOFFERED = stats.CallsOffered;

                Output0Buffer.CALLSMISSED = stats.CallsMissed;

            }

     Build, save and lets return to the data flow. We can create a union all that the script component can output to so we can add a data viewer to see the records. In a normal implementation this would go to a data destination:

Let’s now run the package and view the results:

The XML data is now in our data flow and can be sent to a data destination. No physical XML file was needed, which means we didn’t have to rely on an XML Source to get this.

How to query Active Directory with SQL Server

Good evening everyone, this is an intersting article from Brady Upton  posted at http://www.mssqltips.com/sqlservertip/2580/querying-active-directory-data-from-sql-server/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=20130618

How to stop or cancel your SSAS cube processing

It’s quite easy to do this operation.

First you need to find the following Id’s : SPID, ConnectionID and Session ID. You can get this by using the following DWV’s:

SELECT * FROM $SYSTEM.DISCOVER_CONNECTIONS

This will return all active connections to a particular SSAS Instance Database, along with useful information such as the ConnectionID as well as user, time and data based statistics

SELECT * FROM $SYSTEM.DISCOVER_SESSIONS

This DMV will return all active sessions on a particular SSAS database, this DMV can be linked to the connections DMV using the SESSION_CONNECTION_ID column.

SELECT * FROM $SYSTEM.DISCOVER_COMMANDS (Just to see the Currently executing commands, this can be linked to the Sessions DMV using the SPID column.)

After finding the XMLA Process Command you can get the “ID’s” and run the following cancel statement:

<Cancel xmlns=“http://schemas.microsoft.com/analysisservices/2003/engine“>
<ConnectionID>insert the connection id</ConnectionID>
<SessionID>insert the session id</SessionID>
<SPID>insert the spid</SPID>
<CancelAssociated>true</CancelAssociated>
</Cancel>

Thanks to http://thinknook.com/kill-a-session-spid-or-connection-to-analysis-service-cube-2012-09-21/

I’m a Microsoft Business Intelligence Technology Specialist :)

Yesterday i passed the exam to achieve the Microsoft Certification in Business Intelligence Development & Maintenance (MCTS 70-448), with a 906/1000 score 🙂

Connecting SSRS Report to Bing Maps

Hy there,

Today i read an interesting article at mssqltips.com talking about drill through actions in  order to connect and map your Report Data to external sites such as Bing or Google Maps.

Here is the link :http://www.mssqltips.com/sqlservertip/2926/ssrs-data-specific-report-drill-through-to-bing-maps/?utm_source=dailynewsletter&utm_medium=email&utm_content=headline&utm_campaign=20130410

Contain DW/BI Scope Creep and Avoid Scope Theft

Nice article at Kimball Group Site, exposing the need to keep your Business Intelligence Program on track – avoid losing your inicial Scope!!

http://www.kimballgroup.com/2013/04/01/design-tip-154-contain-dwbi-scope-creep-and-avoid-scope-theft/

List All SSAS Roles and Users Membership

This is a known issue of SSAS, there is no easy way to list all SSAS Roles and Users Membership in a Tabular and simple Format. There are some posts mentioning the use of Powershell in order to complete this task (

http://www.lucasnotes.com/2012/09/list-ssas-user-roles-using-powershell.html#comment-form). There is also a Roles Report available in Bids Helper but it hasn’t an easy and understandable format

However, with the help of a great project at codeplex called the Analysis Services Stored Procedure Project (http://asstoredprocedures.codeplex.com), we can install some stored procedures written in C# in our SSAS Server and then run this simple command in a Mdx query editor:

Command: call assp.discoverxmlmetadatafull(“\Database\Roles\Role|Name\Members\Member”)

The result: