CRM Reporting Extensions – SSRS not listed

Just a quick entry in case you are trying to solve a problem when attempting to install CRM Reporting Extensions.  You might encounter a situation where you specify the SQL Server machine or instance name, but proceeding to select the SSRS instance produces a blank list. 

So you need to install Reporting Extensions AFTER you’ve successfully installed Dynamics CRM.  Partially this is so that the Organisation and associated reports (if any) are correctly published to SSRS.  Aside from this, also verify the following…

First thing to check is that you’re installing the Reporting Extensions on the SSRS/SQL location and NOT the CRM Application server(s).

If so far everything is correct, check the log file for the installer.  You can specify a location other than the default, invoke the installer from a command prompt like this:

image

SetupSrsDataConnector /Q /CONFIG c:\temp\install-config.xml /L c:\temp\log.txt

If you find the following in the installer log:

  • “Could not find a local RS instance corresponding to the reporting url http:///<reportserver> for organization <org name>”
  • “Organization <org name> does not have reports published”

It could be that you’re trying to install against a 32 bit version of SQL Server, or an unsupported edition (see supported editions and how to check your edition of SQL Server in the References, below).

You can also use the command line to specify a configuration file, here’s an example:

<crmsetup>
  <srsdataconnector>
    <configdbserver></configdbserver>
    <autoupdateconfigdb>1</autoupdateconfigdb>
    <reportserverurl>
http://servername/Reportserver</reportserverurl>
    <autogroupmanagementoff>0</autogroupmanagementoff>
    <instancename>Titan</instancename>
    <configsku>OnPremise</configsku>
    <!– Set enabled = true for DB webstore integration.  Set configdb=”true” for config db webstore integration–>
    <webstore enabled=”false” configdb=”false” />
    <monitoring>
<!– Monitoring service account name and password. It can not be local system or network service account –>
      <serviceaccountname></serviceaccountname>
      <serviceaccountpassword></serviceaccountpassword>
    </monitoring>
  </srsdataconnector>
</crmsetup>

References

Microsoft Dynamics CRM reporting requirements – https://technet.microsoft.com/en-us/library/hh699754.aspx

How to determine the version, edition and update level of SQL Server and its components – https://support.microsoft.com/en-au/kb/321185

Install Microsoft Dynamics CRM Reporting Extensions using a command prompt – https://technet.microsoft.com/en-us/library/hh699725.aspx


Installing K2 Blackpearl Smartforms Runtime in a separate Active Directory domain

Introduction

Recently I created an architecture which saw K2 Smartform Runtime components deployed and configured in a separate Active Directory forest from the relevant K2 Blackpearl server.  This architecture aligns with the security and enterprise architecture principals for one of my Government clients.

All of the client’s environments are all designed to implement a network/domain equivalence of air gapping, by implementing separate Active Directory forests for the security perimeter (a DMZ Domain) and for the corporate (“internal”) systems.  External connectivity must route through the DMZ domain for all external functionality – i.e. no exposure of services or applications within the corporate domain directly.

Naturally, K2 Blackpearl resides within the corporate domain, but external users can only authenticate to the DMZ domain – their credentials stored within Active Directory within the DMZ domain.  Both the DMZ and corporate directories implement AD FS 3 (Server 2012 R2), so WS-Federation and claims based authentication (SSO) is possible, and implemented already.

Luckily for us, the K2 Smartforms Runtime supports this authentication scenario.

The following diagram illustrates the configuration and domain design:

image

Pre-install Requirements

Note: This article assumes you already have installed and configured the K2 Blackpearl server.

Permissions required to execute the install

· Domain Admin in DMZ

· Domain Admin in Internal

· Admin role in K2 Server

· RDP access to K2 server, SQL server (K2 database), DMZ webserver, DMZ Domain Controller and DMZ ADFS

· A bespoke T-SQL script to configure the K2 database (details below)

· Access to K2 install media

Ports

The DMZ Web Server(s) needs port 1433 TCP access (one way) to SQL Server to connect to the K2 database – only for installation!

This access can be removed, post-installation.  The Runtime needs the TCP 5555 port to contact the K2 Blackpearl server.  The K2 Blackpearl server may require TCP 389/626 (LDAP) port access to the DMZ AD DS server to validate authenticated identities (being confirmed).

Certificates

You will require a digital certificate to secure the website for AD FS authentication.  Self-signed certificates would suffice for non-Production environments, although I have personally abandoned that practice many years ago, and insist on the installation of an Enterprise CA now for the purpose of issuing valid certificates for internal testing.  For pre-Production and Production environments, you’d be unprofessional not to use a properly issued certificate from a major issuer, e.g. Verisign etc.

Server Roles/Features

Roles

Web Server -> Application Development -> Application Initialization

Web Server -> Performance -> Dynamic Content Compression

Web Server -> Security -> URL Authorization

Features

.NET Framework 3.5 Features -> HTTP Activation

Windows Process Activation Service -> .NET Environment 3.5

1. K2 Core Install

1. Copy installer package to the server (e.g. D:\Installs\)

2. Extract package

3. Authenticate to SQL Server (Internal)

4. Open SSMS and expand Security\Logins

5. Create a SQL Server Authentication Login:

    a. Name: svc_k2_dmz

    b. Password: <Generate>

    c. User Mappings:

    d. K2 Database – db_datareader & db_datawriter

image

6. Make a record of the server & account details (needed for K2 install)

7. Return to DMZ webserver(s) and run the K2 blackpearl installer (first option)

clip_image004

8. Click through the installer until this screen, and select ‘custom’

clip_image006

9. UNCHECK everything except for the Required Components (at the very bottom)

clip_image008

It should look like this when installing:

clip_image010

10. Once finished, reboot and then proceed to the second installer:

clip_image012

2. K2 Smartforms Install

1. Run the installer

2. Continue until you encounter the following screen:

3. Uncheck everything except K2 smartforms runtime:

clip_image014

4. Enter the correct connection properties to the K2 database on the APPS cluster and test

image

5. Enter license information (see appendix)

image

While you are waiting for a license key, check the web site configuration.

Create the new website which will host the actual K2 Smartforms runtime site itself.  It should have a custom App Pool with a local account as the identity, e.g. svc_k2forms  This identity (local account) should also exist on the K2 server, with the same password (not set to expire).  I prefer to create the site manually rather than creating it through the installer.

image

Resuming the install, paste the license key into the UI and click next:

6. Select the forms website:

clip_image021

7. And the existing app pool

image

8. Confirm:

image

Once the install is underway, it’s worth configuring ADFS

clip_image027

Health error seems to be irrelevant…

Site now needs Controls – see K2 Smartforms Control Pack

2a. ADFS Configuration

Navigate to the DMZ ADFS server and open the AD FS Administration console.

If no trust exists, you can easily create one.  Use the endpoint URI of the site, and create a claim rule as so:

image

Add claim rules – Provider

clip_image032 clip_image034

=> issue(Type = “http://schemas.microsoft.com/identity/claims/identityprovider”, Value = “ADFS”);

Add claim rules – Identity

clip_image036  clip_image038

3. Install the K2 Smartforms Control Pack

1. Return to the Web Splash page and click install of the Control Pack:

clip_image040

2. Click through the installer until this page:

clip_image042

3. Reconfirm the DB connection settings using environment specific values:

image

Continue to run through the installer.

clip_image046

Next, edit the web.config for the Runtime application. Go to the following location:

C:\Program Files (x86)\K2 blackpearl\K2 smartforms Runtime
and take a backup copy of the Web.config.

Now edit the Web.config and append the following line:

<!– The DefaultSecurityLabel is used to when none is specified. Leave blank or missing to to use the URM default security label –>


<!–<add key=”DefaultSecurityLabel” value=”K2″/>–>


<!– SecurityLabels that are available (Semi-colon separated list). Leave blank or missing to use the all URM security labels–>


<add key=”SecurityLabels” value=”K2FORMS”/>

Perform an iisreset & restart the K2 blackpearl service on the K2 server.

You are finished in the DMZ. I’d suggest a reboot to ensure everything is nice and clean.

Time to configure the internal SQL & K2 server.

4. SQL Server Configuration

A specific T-SQL script needs to be run against the K2 database to insert a provider for the DMZ ADFS STS.  You’ll need to ensure all the <DMZDOMAIN> parts are valid before executing the script.  This should just be references to your LDAP names for the DMZ domain.  We’re using an Organisation Unit (OU) called “External Users” as the location for the user accounts.  Change as required.

   1: -- K2 LDAP User Manager (Forms - Setup).sql

   2: -- sample script for creating a K2 LDAP user manager that uses the SourceCode.Security.Providers.LdapProvider.Forms.Ldap provider

   3:  

   4: -- DECLARATIONS

   5: DECLARE @SecurityLabelName NVARCHAR(20) = 'K2FORMS'; --Update as needed

   6: DECLARE @XmlConfig XML = 

   7: '<AuthInit>

   8:   <LdapConnection

   9:     LdapServer="dmz.<DMZDOMAIN>"

  10:     LdapServerPort="389"

  11:     LdapSsl="false"

  12:     LdapAuthTypeConnect="Negotiate"

  13:     LdapAuthTypeAuthenticateUser="Negotiate"

  14:     LdapResolveAuthenticationUserToDistinguishedName="false"

  15:     LdapAutoBind="false"

  16:     LdapScope="Subtree"

  17:     LdapConnectIntegrated="true"

  18:     LdapConnectUserName=""

  19:     LdapConnectUserPassword=""

  20:     LdapTimeout="0"

  21:     LdapProtocolVersion="3"

  22:     LdapServerCertificatePath="" />

  23:   <LdapUserBaseObject>ou=external users,<DMZDOMAIN></LdapUserBaseObject>

  24:   <LdapUserSearchFormatString>(&amp;(objectClass=Person)(objectCategory=User)(samAccountName={0}))</LdapUserSearchFormatString>

  25:   <LdapUserGroupSearchFormatString>(memberOf:1.2.840.113556.1.4.1941:={0})</LdapUserGroupSearchFormatString>

  26:   <LdapUserAttributes>

  27:     <K2LdapMapping K2Name="ID" LdapName="samAccountName" ObjectType="System.String" />

  28:     <K2LdapMapping K2Name="Name" LdapName="samAccountName" ObjectType="System.String" />

  29:     <K2LdapMapping K2Name="Description" Multiline="true" LdapName="description" ObjectType="System.String" />

  30:     <K2LdapMapping K2Name="Email" LdapName="mail" ObjectType="System.String" />

  31:     <K2LdapMapping K2Name="DistinguishedName" LdapName="distinguishedName" ObjectType="System.String" />

  32:     <K2LdapMapping K2Name="ObjectSID" FullOnly="true" LdapName="objectSID" ObjectType="System.String" />

  33:     <K2LdapMapping K2Name="CommonName" LdapName="cn" ObjectType="System.String" />

  34:     <K2LdapMapping K2Name="UserPrincipalName" LdapName="userPrincipalName" ObjectType="System.String" />

  35:     <K2LdapMapping K2Name="Manager" FullOnly="true" LdapName="manager" ObjectType="System.String" SearchQuery="(&amp;(objectClass=Person)(objectCategory=User))" SearchResultProperty="samAccountName" />

  36:     <K2LdapMapping K2Name="SipAccount" LdapName="msRTCSIP-PrimaryUserAddress" ObjectType="System.String" />

  37:     <K2LdapMapping K2Name="DisplayName" LdapName="displayName" ObjectType="System.String" />

  38:     <K2LdapMapping K2Name="TelephoneNumber" LdapName="telephoneNumber" ObjectType="System.String" />

  39:     <K2LdapMapping K2Name="Mobile" LdapName="mobile" ObjectType="System.String" />

  40:     <K2LdapMapping K2Name="HomePage" LdapName="wWWHomePage" ObjectType="System.String" />

  41:     <K2LdapMapping K2Name="FaxNumber" LdapName="facsimileTelephoneNumber" ObjectType="System.String" />

  42:     <K2LdapMapping K2Name="HomePhone" LdapName="homePhone" ObjectType="System.String" />

  43:     <K2LdapMapping K2Name="IPPhone" LdapName="ipPhone" ObjectType="System.String" />

  44:     <K2LdapMapping K2Name="StreetAddress" LdapName="streetAddress" ObjectType="System.String" />

  45:     <K2LdapMapping K2Name="City" LdapName="l" ObjectType="System.String" />

  46:     <K2LdapMapping K2Name="Country" LdapName="c" ObjectType="System.String" />

  47:     <K2LdapMapping K2Name="State" LdapName="st" ObjectType="System.String" />

  48:     <K2LdapMapping K2Name="Title" LdapName="title" ObjectType="System.String" />

  49:     <K2LdapMapping K2Name="Department" LdapName="department" ObjectType="System.String" />

  50:     <K2LdapMapping K2Name="Company" LdapName="company" ObjectType="System.String" />

  51:     <K2LdapMapping K2Name="Office" LdapName="physicalDeliveryOfficeName" ObjectType="System.String" />

  52:     <K2LdapMapping K2Name="ManagedUsers" FullOnly="true" LdapName="managedUsers" SearchQuery="(&amp;(objectClass=Person)(objectCategory=User))" SearchResultProperty="samAccountName" ObjectType="System.Collections.ArrayList" />

  53:     <K2LdapMapping K2Name="Groups" FullOnly="true" LdapName="memberOf" SearchQuery="(objectCategory=Group)" SearchResultProperty="samAccountName" ObjectType="System.Collections.ArrayList" />

  54:   </LdapUserAttributes>

  55:   <LdapGroupBaseObject>ou=external users,dc=dmz,<DMZDOMAIN></LdapGroupBaseObject>

  56:   <LdapGroupSearchFormatString>(&amp;(objectCategory=Group)(samAccountName={0}))</LdapGroupSearchFormatString>

  57:   <LdapGroupMemberSearchFormatString>(member:1.2.840.113556.1.4.1941:={0})</LdapGroupMemberSearchFormatString>

  58:   <LdapGroupAttributes>

  59:     <K2LdapMapping K2Name="ID" LdapName="samAccountName" ObjectType="System.String" />

  60:     <K2LdapMapping K2Name="Name" LdapName="cn" ObjectType="System.String" />

  61:     <K2LdapMapping K2Name="Description" Multiline="true" LdapName="description" ObjectType="System.String" />

  62:     <K2LdapMapping K2Name="Email" LdapName="mail" ObjectType="System.String" />

  63:     <K2LdapMapping K2Name="DistinguishedName" LdapName="distinguishedName" FullOnly="true" ObjectType="System.String" />

  64:     <K2LdapMapping K2Name="ObjectSID" LdapName="objectSID" FullOnly="true" ObjectType="System.String" />

  65:     <K2LdapMapping K2Name="Member" LdapName="member" FullOnly="true" SearchQuery="(&amp;(objectClass=Person)(objectCategory=User))" SearchResultProperty="samAccountName" ObjectType="System.Collections.ArrayList" />

  66:   </LdapGroupAttributes>

  67: </AuthInit>' -- XML configuration for the LDAP provider, see K2 Help for more information on configuration values

  68: DECLARE @SecurityLabelID UNIQUEIDENTIFIER = NEWID();

  69: DECLARE @AuthSecurityProviderID UNIQUEIDENTIFIER = NEWID(); --Assigning new GUID

  70: --c84080d3-7673-4a87-9c79-42b99d39ce0a

  71: DECLARE @AuthInit XML = @XmlConfig;

  72: DECLARE @RoleSecurityProviderID UNIQUEIDENTIFIER = @AuthSecurityProviderID;

  73: DECLARE @RoleInit XML = @XmlConfig;

  74: DECLARE @DefaultLabel BIT = NULL; --1 = true, NULL and 0 = false

  75: DECLARE @ProviderClassName NVARCHAR(200) = 'SourceCode.Security.Providers.LdapProvider.Forms.Ldap';

  76: DECLARE @SPProviderID UNIQUEIDENTIFIER;

  77:  

  78: -- UPDATE TABLES

  79: DELETE FROM [HostServer].[SecurityProvider] WHERE ProviderClassName = @ProviderClassName;

  80: DELETE FROM [HostServer].[SecurityLabel] WHERE SecurityLabelName = @SecurityLabelName;

  81: INSERT INTO [HostServer].[SecurityProvider] VALUES (@AuthSecurityProviderID, @ProviderClassName);

  82: INSERT INTO [HostServer].[SecurityLabel] VALUES (@SecurityLabelID, @SecurityLabelName, @AuthSecurityProviderID, @AuthInit, @RoleSecurityProviderID, @RoleInit, @DefaultLabel);

  83:  

  84: SELECT @SPProviderID = [SecurityProviderId] FROM [HostServer].[SecurityProvider] WHERE [ProviderClassName] = N'SourceCode.Security.Providers.SharePoint.SharePointProvider';

  85: IF NOT EXISTS (SELECT 1 FROM [HostServer].[GroupProvider] WHERE [SecurityLabelID] = @SecurityLabelID)

  86: BEGIN

  87:        INSERT INTO [HostServer].[GroupProvider]

  88:        (

  89:               [GroupProviderID]

  90:               ,[SecurityLabelID]

  91:               ,[SecurityProviderID]

  92:               ,[Name]

  93:               ,[Init]

  94:        )

  95:        VALUES

  96:        (

  97:               NEWID()

  98:               ,@SecurityLabelID

  99:               ,@SPProviderID

 100:               ,'*'

 101:               ,'<init><label name="SP" /></init>'

 102:        )

 103: END

 

Open it within SSMS on the SQL cluster (or someplace handy). You need to have access to the K2 database in the APPS instance.

Alter the following as appropriate for the environment you are configuring (replace <DMZDOMAIN> as appropriate):

<LdapConnection

LdapServer=”dmz.<DMZDOMAIN>”

<LdapUserBaseObject>ou=external users,dc=<DMZDOMAIN></LdapUserBaseObject>

<LdapGroupBaseObject>ou=external users,dc=<DMZDOMAIN></LdapGroupBaseObject>

Then execute on the K2 database when ready.

You need to restart the K2 blackpearl service on the K2 server once completed.

5. K2 Server Configuration

Ensure the K2 Blackpearl service has been restarted. Navigate to the K2 Studio in Internet Explorer.
Open K2 Designer for the K2 server instance in the environment you are configuring.

image

Expand the Tree to:

All Items -> System -> Management -> Security -> Forms -> Manage Issuers

clip_image050   clip_image052

And Click “Run” and then click “New”. Add values appropriate to the environment you are configuring.

image

Where to get the Thumbprint? Authenticate to ADFS in the DMZ. Open a PowerShell console and type Get-ADFSCertificate:

image

When you’ve entered the thumbprint, save. Click on “Manage Claims” and click run.

clip_image057

Select “K2FORMS” from the drop list (if it is not here, you either missed the SQL part, or didn’t restart the K2 service).

Complete the rest of the fields as below with the correct domain names.

image

Now navigate to Manage Site Realms and click Run

clip_image061

Enter a site realm for the DMZ form site:

image

That should be it.

Now load up K2 Workspace by browsing to https://<K2Server>/workspace

Click on Management, then expand Machine Name -> User Managers -> K2 -> Domains

image

If the K2 server is missing, add it:

image

For some reason, the DMZ domain does not need to be added here J

Appendix 1 – License

1. You need to have an account in the K2 portal:

2. Authenticate to https://portal.k2.com/

clip_image069

3. Click on Support->License Key Request->License Key

clip_image071

Complete the license request form using the correct server names for the environment you are installing in.
Note that Anything other than production can use Non-Production server SKU.

image

A temporary license will be emailed to you, the proper license should arrive later.