Seeing better RESULTS in your FUTURE
Home     BI Products     MOSS Products     Administration Products     Community     News     Site Search      
Blog     Twitter     Delicious      

FUTURESULTS, LLC Blog - by Robert Lambrecht

 

 

FUTURESULTS, LLC Blog is a Technology Blog for Microsoft Links, Reference Information, and How-To's.  Anything and everything that helps people understand Microsoft products.  Review additional material that addresses a specific issue, topic or describes some information that isn't easily found anywhere else. 

 

See a blog post summary of blog posts or look at some recent entries below.

 

Recent FUTURESULTS, LLC Blog Entries - by Robert Lambrecht

 

 

August 04

ProClarity Analytics Server Migration to a New Database Server

What would you do if you needed to change your ProClarity Analytics Server database instance name?  Let’s take a step back and start with some background.  Microsoft BI product installations have a separate data tier option.  In other words, the database is on a different machine than the actual product (like ProClarity).  Occasionally, you may have a need to change your database server or move your data to a new database instance on the same machine.  Perhaps you have a leased server and the lease expired.  Maybe you have outgrown your current hardware and need a larger SQL Server machine.  In all of these cases, you are going to have to backup and restore the database, ensure that you have the correct logins and permissions, and change any application configurations to use the new database instance.  Let’s look into these items and see how to make the changes for ProClarity Analytics Server.

Preparing the Database Machine

There is only one database for ProClarity Analytics Server. 

I posted previously about Database Migrations – Configure Logins and Permissions.  This post gives you information about:

Backup and Restoration of the SQL Database (Data contained in each database).

After you complete the work above, your new database machine is ready and the data and access to the configuration data is ready. 

 

Configuring ProClarity Analytics Server 

 

Open the ProClarity Analytics Server Administration Tool

clip_image001[1]

 

Right-click the name of the PAS server & select Properties.

clip_image002[1]

In the "SQL server" box enter the name of the new SQL Server.  For example, change the SQL server name from "BI-VPC" to "NewDB".  Better yet, create a SQL Alias so you will have an easier way to make this change in the future.  ProClarity Analytics Server will restart after selecting OK. 

While not always required, I have found it beneficial to Reboot the ProClarity Analytics Server.  I suggest doing this as part of your process.   

Navigate to the ProClarity Analytics Server web site to insure that the applications still renders all of your Briefing Books.

 

FUTURESULTS, LLC Blog and FUTURESULTS, LLC Web Site are both created by Robert Lambrecht.

 



10:10 AM GMT  |  Read comments(0)

May 03

Kerberos SPN Generation Setup Tool - Delegation Tab

The last post describes the Delegation Process.  While it is important to understand the Delegation Process, the idea was to provide a background for this post on how to use the Kerberos SPN Generation Setup Tool Beta to help you identify which delegations are needed or missing.  This is a continuation of the series of blog posts “Kerberos SPN Generation Setup Tool” that describe how to use the Kerberos SPN Generation Setup Tool Beta for Kerberos Constrained Delegation with Integrated Windows Authentication for Microsoft BI tools.  This post will discuss the “Delegation Tab”.  You can download the Kerberos SPN Generation Setup Tool Beta at . 

It is assumed that you have completed the Generation SPNs process and the Main Menu / Navigation screen should look something like this the image below.  Select “Delegation” to get to the Delegation tab.

 
Main Menu / Navigation - Delegation

PreDelegation

 

For review purposes, we will use the ProClarity Analytics Server example and it’s corresponding ProClarity Delegation example.

 

“PAS Tab” entries are:

PASEntry

 

If we look at the “Delegation” tab, notice that the delegation needed does not exist (cell D8 below). 

Delegation Tab Example (Prior to SPNs being Delegated).

SPS2AddB4DelegationDelTab

 

You can follow the Delegation Process – PAS Example for the details of how to do Delegation.  After the delegation is complete, the “Delegation” Tab should look like this.

 

Delegation Exists: (After SPNs were added and Delegation Process was completed)

SPNs2AddAfterDel-DelTab

 

Since there is a lot of detail on this page, I will break it up into three sections and discuss each separately.

DelegationTab

 

Section 1: Proposed Application Server / Database Server Delegations

Based on the entries in the “Input Tabs” sections, Section 1 is completed for you.  In this case, we have only entered information in for the PAS application tab.  You can see that the “Delegation” tab knows about the service accounts that were entered between the database / SSAS account and the PAS application server account.  In this case, the “Delegation” is also complete.  Note that there is a “*” in the Delegation Exists column.  This is to denote that if the delegation does exist, you will want to make sure all of the attributes are correct in Active Directory.  The process for checking this has been detailed in a previous post (Delegation Process - PAS Example).  You can add any notes you wish in the “Notes” column.  Section 1 is basically calculated and completed for you.  You may elect to do some checking on Delegations that exist.

 

Section 1: Expanded View

DelegationZone1

 

Section 2: Delegations Currently in the Domain (for listed accounts)

This section really documents the Kerberos Constrained Delegation.  In other words, it shows you the SPNs that are constrained between the two service accounts that exist on two different machines.  You can use the individual input tabs to find out the details for each BI product used.  In this example, it shows the service accounts from Section 1 along with the SPNs that were generated by the tool for the Database / SSAS service account (sql_analysis).  This is really the heart of what I was trying to accomplish with the tool.

If there are any service accounts listed in this section as “UNKNOWN”, it means that there is an existing delegation set up for the service account but you do not have the service account listed.  You can solve this by putting the missing service account in the “Other Accounts” tab.  When you rerun the “Generate SPNs” process, it will update the “UNKNOWN” account with the proper account.  This may take several iterations if you have to guess at the missing account.  It is important to have all of the accounts properly identified so that the tool can ensure that there are no “Duplicate SPNs”.  It only does this check for accounts that are listed in the tool.

The Front/Middle vs. Middle/Back account is reference nomenclature based on the proximity of the account to the user (Front) or database (Back).  In this case, you could say that the paswebapp account (accessed directly from the user’s browser) is the “Front” account.  This account interacts with sql_analysis which is the service account for the database (the “Back” account).  This nomenclature is typically used when thinking of a 3 tiered architecture where the “Front” tier is the web server, the “Middle” tier is the application server, and the “Back” tier is the database server.   In some cases there may be only two tiers.  The point here is that this nomenclature gives you some direction as to the delegation starting at the “Front” and working your way toward the “Back”.  The actual designation of Front, Middle, Back is not that important.

 

Section 2: Expanded View

DelegationZone2

 

Section 3: Other Delegations Needed – Application Server / Application Server (http)

In a complex delegation, you may need to have a delegation between two applications that is not yet specified.  In this case, you would need to specify the delegation between the applications.  In our simple example, this section is blank.  If you wanted to use this section, you would have to have the applications specified in the “Input Tabs”.  You can only selection delegations like this if the applications are defined in the tool.  The tool can then check for these delegations, etc.  There is also a “Notes” section to denote any additional delegations.  Note that you can only specify “http” delegations in this section.  If you need to define a delegation to a database, this should be done in one of the application input sections. 

 

Section 3: Expanded View

DelegationZone3

  

What additional features would you like to see in a Kerberos SPN setup tool?  Leave your suggestions below.

 

FUTURESULTS, LLC Blog and FUTURESULTS, LLC Web Site are both created by Robert Lambrecht.

 



2:32 PM GMT  |  Read comments(0)

Kerberos SPN Generation Setup Tool - Delegation Process

 

Delegation Process – PAS Example

This post takes a short departure from directly discussing the “Kerberos SPN Generation Setup Tool” as there needs to be a process discussion on how to do “Kerberos Constrained Delegation”.  This is a manual step that needs to be accomplished after completing the Generate SPNs process.  This post shows you how to do the “Kerberos Constrained Delegation” process.  It also shows you how to review SPN setup and Delegation information in Active Directory.  If you need to, you can download the Kerberos SPN Generation Setup Tool Beta at . 

For more details about this example, we will reference a previous post describing how to set up the Kerberos portion of the ProClarity Analytics Server (PAS) 6.3.  The setup for that post will consist of only the “PAS Tab” information.  In other words, imagine that we have only entered information into the “Common Tab” and the “PAS Tab”.  While this example is for a ProClarity setup, it works the same with other Microsoft BI technologies.

 

As always, we draw a picture to help us understand the definition of the setup. 
 

PAS

 

ADSI Edit and SPNs

This example assumes that you have completed the Generate SPNs process for PAS.  After you have completed the Generate SPNs process, the rest of the information in this section is optional (it is listed so you know the details of how to check what the tool did).  The Generate SPNs process essentially creates the SPNs and puts them into the appropriate servicePrincipalName (SPN) for you.  This section shows you how to manually check SPNs.  

Start by logging into your Active Directory Domain Controller.  ADSI Edit is a snap-in that can manage objects in Active Directory.  You can use ADSI Edit to check out if the Generate SPNs batch file added the SPNs correctly.  Start ADSI Edit and go to each of the domain user accounts to check the SPN setup.  In our example, you would go to “paswebapp” and “sql_analysis”.  We will start by looking at “paswebapp”.   

Go to the user account properties –> Attribute Editor tab –> and scroll down to the “servicePrincipalName” or SPN. 

Review the SPN values. 

In this example, the SPNs can be seen below for the “paswebapp” account.  The “paswebapp” account was the application pool account used for the PAS application.

  • http/analytics
  • http/analytics.newdn.com

Repeat this for all of the accounts that you would like to review the SPN entries for (the other account is “sql_analysis” in our example).

 

Analytics is a Host Header or Host (A) Name Record in DNS for the PAS application instance.

DelPAS1

 
“sql_analysis” is the domain user account used for the Analysis Services instance on the ReportMachine server.

DelPAS2

 

Delegation Process

First determine if delegation is needed (this will be discussed more in a future post).  In our example (after completing the Generate SPNs process), you can go to the “Delegation” tab and review the output.  In this case, you can see that Delegation does not exist (cell D8 below) and we must manually do the Delegation process.

 

Delegation Tab Example –> "Kerberos SPN Generation Setup Tool – Generate SPNs"

SPS2AddB4DelegationDelTab

 

Let’s start the delegation process by going into Active Directory and finding the user account “paswebapp”.  Following the arrows in our diagram above and working from front (the user) to back (data source), we find the application to application communication that takes place.  In our example, the “paswebapp” user account delegates to the “sql_analysis” user account (front to back following the arrows).  This application to application security is what we are interested in “Constraining”.  Find the user “paswebapp” and complete the following process in Active Directory.

Go to Properties –> Delegation Tab.  Select “Trust this user for delegation to specified services only” and then select “Use Kerberos only”. 

Select Add, Users & Computers, and then add the user “sql_analysis”, Select All.  Then select OK, OK, … until you get back to the “paswebapp” Properties window.

Select the Expanded checkbox and then OK.  Notice that the SPNs that you added for the “sql_analysis” account should now show up in the “paswebapp” properties services section of the dialog box shown below.

Select OK to complete.

We have now completed the “Kerberos Constrained Delegation” process for our example.

 

Completed Kerberos Constrained Delegation Process for “paswebapp”.

DelPAS5

 

Checking Delegation with ADSI

Just like we can check SPNs with ADSI Edit, we can check our Kerberos Constrained Delegation as well.  The Kerberos Constrained Delegation attribute is called “msDS-AllowedToDelegateTo” attribute. 

Start ADSI Edit and go to each of the domain user accounts to check the delegation attribute.  In our example, you would go to “paswebapp”. 

Go to the user account properties –> Attribute Editor tab –> and scroll down to the “msDS-AllowedToDelegateTo”. 

Review the values.  In this example, the SPNs that were “allowed to be delegated to” can be seen below for the “paswebapp” account.  The “paswebapp” account was the application pool account used for the PAS application.

  • MSOLAPSvc.3/ReportMachine 
  • MSOLAPSvc.3/ReportMachine.newdn.com

Basically we can see that the PAS application is Constrained, via Kerberos, to the Analysis Service instance on the “ReportMachine”.  Remember that Delegation is directional.  In other words there is a difference between the “paswebapp” user account delegating to the “sql_analysis” user account, and the “sql_analysis” user account delegating to the “paswebapp” user account. 

 

Checking the msDS-AllowedToDelegateTo attribute on “paswebapp”.

DelPAS4

 
For completeness, “sql_analysis” is shown even though the attribute is empty.

DelPAS3

 

Checking your work with the ”Kerberos SPN Generation Setup Tool”.

After you have completed your constrained delegation, you can rerun the Generate SPNs process and then check out the output on the “Delegation” tab.  You will now notice that the PAS application constrained delegation has been completed (see cell D8).  Notice that the “Delegation Exists?*” (column D) has an “*”.  The “*” basically tells you to check your delegation in Active Directory to make sure all of the attributes are correct.  The above process walks you through how to do this.

 

Delegation Tab after the manual delegation is complete.

SPNs2AddAfterDel-DelTab

 

While this example is for the PAS application, it is a valid approach for other BI applications.  Repeat this process for all of the necessary delegations until you complete your setup.

 

What if you add more configuration later and you have to delegate again?

Do the delegation again and check to see if the attributes are correct (the general process is shown above).  If you add an additional SPN at a later time, you must re-delegate the affected user accounts so that the existing attributes get updated.  You can also accomplish this by adding the new SPNs into the appropriate msDS-AllowedToDelegateTo attribute if needed or go through the delegation process again.

 

What additional features would you like to see in a Kerberos SPN setup tool?  Leave your suggestions below.

 

FUTURESULTS, LLC Blog and FUTURESULTS, LLC Web Site are both created by Robert Lambrecht.

 



2:16 PM GMT  |  Read comments(0)

Kerberos SPN Generation Setup Tool

This is the first installment for a series of blog posts that describe how to use the Kerberos SPN Generation Setup Tool Beta in terms of documenting and helping you plan and creates SPNs for Kerberos Constrained Delegation with Microsoft BI tools.  The following is the outline of this series.  I will update this post as I complete the individual segments.  You can download the Kerberos SPN Generation Setup Tool Beta at .   

Rather than just complain about Microsoft not having an adequate Kerberos Setup Tool, I created one.  You can read my general series on Kerberos Constrained Delegation to get an idea of some of the setup items needed to successfully setup Kerberos Constrained Delegation.  The Kerberos SPN Generation Setup Tool Beta helps you plan and document your BI security setup. 

The following shows a screen snapshot of the BI product SPNs that can be setup using the tool.  The goal of the tool is to help you generate unique SPNs needed for your setup.  It also helps you plan your delegations.  In general, the product allows you to export a batch file of unique SPNs that can be imported and ran on your Domain Controller.  The tool can be used to check for duplicate SPNs for select accounts and provide documentation for the future.  There is also an “Undo” file that can be used to remove the suggested SPNs should there be an issue.  There will be much more description throughout the remainder of this series.

Kerberos Setup Tool

Topics to explore are focused on how to set up SPNs and Kerberos Constrained Delegation in context of using the Kerberos SPN Generation Setup Tool Beta. 

Overview

 

Input Tabs

 

SPN Generation - Kerberos SPN Generation Setup Tool - Generate SPNs (Updated 1/19/2010)

Generate SPNs
Export SPNs to Add
Export SPNs to Remove
Review SPN Information
Process of adding / removing SPNs from Domain Controller

 

Delegation

Delegation Tab - Kerberos SPN Generation Setup Tool - Delegation Tab (Updated 2/10/2010)
Active Directory Delegation Process  - Kerberos SPN Generation Setup Tool - Delegation Process  (Updated 1/28/2010)

 

 

What additional features would you like to see in a Kerberos SPN setup tool?  Leave your suggestions below.

 

FUTURESULTS, LLC Blog and FUTURESULTS, LLC Web Site are both created by Robert Lambrecht.

 



11:39 AM GMT  |  Read comments(0)

July 27

Kerberos SPN Generation Setup Tool – Generate SPNs

To date, I’ve written a series of blog posts that describe how to use the “Input” section of the “Kerberos SPN Generation / Setup Tool”.  This is a continuation of the series of blog posts “Kerberos SPN Generation / Setup Tool” that describe how to use the Kerberos SPN Generation Setup Tool Beta for Kerberos Constrained Delegation with Integrated Windows Authentication for Microsoft BI tools.  The next short series of posts will discuss the “Generate SPNs” process.  You can download the Kerberos SPN Generation Setup Tool Beta at FUTURESULTS, LLC. 

 
Main Menu / Navigation - SPN Generation Section

GenSPNs

 

Generate SPNs

You made it through all of the details on how to enter data into each of the input tabs.  This section’s example will consist of only the “PAS Tab” example given previously.  In other words, pretend that we have only entered information into the “Common Tab” and the “PAS Tab”.  While this example is for a ProClarity setup, it works the same with all input tabs that are complete.

 
For review purposes, the “PAS Tab” entries look like this:

PASEntry

 

Now that we have completed the input section, go back to the “Main Menu / Navigation” tab and select the “Generate SPNs” link.  You must be connected to the domain prior to selecting the “Generate SPNs” link.  If there are any errors when we select “Generate SPNs”, you will be notified either by pop up messages, messages in the “Messages” section, or non-green “traffic light” symbols by the appropriate input sections.  You must clear all errors prior to generating SPNs. 

When the “Generate SPNs” traffic light is green, you have successfully generated SPNs.  The tool interrogates your domain (that is why you must be connected to your domain) and creates the proper SPNs.  It is that easy!

The next step is to review the SPN Output via the “SPNOutput” tab.  Notice in this case that there are SPNs Suggested to Add (column E).  Your implementation may have more or less SPNs to add based on information that is already in your domain.  In this case, the SPNs associated with “sql_analysis” already resided in the domain so there was no need to add SPNs for this domain account.

 

“SPNOutput” Tab Example.

SPNs2AddB4DelegationSPNTab

 

If we look at the “Delegation” tab, notice that the delegation needed does not exist (cell D8).  More on Delegation in the future.

 
Delegation Tab Example.

SPS2AddB4DelegationDelTab

 

Export SPNs to Add

Since there were SPNs to add listed in the “SPNOutput” tab, we need to Export SPNs.  Go back to the “Main Menu / Navigation” tab and select the “Export SPNs to Add” link.  This link creates a file with the commands needed to add the appropriate SPNs.  You must be a Domain Administrator to run the batch file on the Domain Controller. 

The file was purposely created with a .txt extension.  Many times this file must be emailed to another person with Domain Administrator rights on the domain controller.  Email systems normally block files with .bat extensions (that’s why the file is saved as a .txt).  Once you copy the file to the domain controller, change it to a .bat extension.  In this example, we would rename the file to “SPNs2ADDInput.bat”. 

 
Export SPNs to Add Example (-L means List, -A means Add).

SPNs2AddInput

 

When the file is on the domain controller you can run it and redirect the output to a file if you like.  For example you would run this file and redirect it’s output as follows:

SPNs2AddInput.bat > SPNs2AddOutput.txt

The output file contains listings of what the service accounts looked like before adding the new SPNs as well as after the SPNs are added.  In addition, ensure that each SPN was successfully added by searching the output file for “Updated object” after each add SPN command.  If there was any kind of error or you mistakenly typed in the wrong service account, etc. use the “Export SPNS to Remove (Undo)” commands in the next section.

 
Example SPNs to Add Batch File Results.

SPNs2AddOutput

 

Export SPNs to Remove (Undo)

Hopefully, this section should be self explanatory.  Basically it works exactly like the “Export SPNs to Add” section above except it removes SPNs instead of adding SPNs.  The process is similar and should always be done in conjunction with the “Export SPNs to Add” process.  In other words, you should always select this link immediately after saving the “Export SPNs to Add” link.  In this way, you can assure that the Remove file contains the same information as the Add file.  If anything goes wrong with the “Export SPNs to Add” process, you can remove whatever was done in the “Add” batch file. 

Use this process to clean up mistakes (if an error exists).  The general steps are:

  1. Go to the Main Menu / Navigation Tab
  2. Select the “Export SPNs to Remove (Undo)” link (immediately after selecting the “Export SPNs to Add” link)
  3. Copy the file to the Domain Controller
  4. Rename the file to “SPNs2RemoveInput.bat”
  5. Run command (ONLY IF NEEDED) SPNs2RemoveInput.bat > SPNs2RemoveOutput.txt
DO NOT RUN THIS FILE AFTER SUBSEQUENT CHANGES TO YOUR DOMAIN CONTROLLER HAVE BEEN MADE.  In other words, this command will remove the SPNs that were added only if subsequent changes have not been made.  If you made additional SPN changes, it could remove a SPN that is now used for another purpose.  The “Remove” process is no longer relevant once other SPN changes are made to the domain.  If you have any question about other changes, do not use this batch file and seek help from a knowledgeable source to remove SPNs manually.

 

 

Export SPNs to Add Example (-L means List, -D means Delete).

SPNs2RemoveInput 

 

Example SPNs to Remove (Undo) Batch File Results.

SPNS2RemoveOutput 

 

Review SPN Information

Now that we have completed adding SPNs to your domain, go back to the “Main Menu / Navigation” tab and again select the “Generate SPNs” link.  The tool interrogates your domain and creates additional SPN suggestions if needed.  In this case, it should find that you have added the appropriate SPNs and nothing additionally needs to be created.  You can validate this by reviewing the “SPNs to Add” section (column E) via the “SPNOutput” tab.

You can review the SPNs in your domain for each account that is entered into the spreadsheet (columns A & B).  Just as an FYI, some of the SPNs were generated automatically (in this case HOST and TERMSRV).  Other SPNs were entered manually via the SPN tool. 

 

SPNOutput Tab - Review output information.  Notice there are no SPNs to Add.

SPNs2AddAfterDel-SPNTab

 

 

Other SPN Generation Tips and Tricks

Domain Controller Replication

Many domains use replication between domain controllers.  This replication may take several minutes to occur.  If you add a new SPN, you may need to wait several minutes to rerun the spreadsheet process in order to do the SPN review process.

The point of this tool is to help you generate SPNs correctly based on parameters that can be gathered by administrators.  The process outlined in these blog posts allows you to have good documentation, reduce issues (like duplicate SPNs), and have a tool to check and troubleshoot your configuration later in case additional changes were made to your domain (other product setups).

Export SPNs to Remove (Undo)

Do not try to generate the “Undo” file at a later date if any domain changes were made.  In other words, do not use the tool to add SPNs and then later select the “Generate SPNs” link in the tool and then try to create the “Undo” file.  The only way that the “Undo” file works is if it is generated at the same time as the “Add” file and no subsequent changes are made to SPNs in the domain.

 

What additional features would you like to see in a Kerberos SPN setup tool?  Leave your suggestions below.

 

FUTURESULTS, LLC Blog and FUTURESULTS, LLC Web Site are both created by Robert Lambrecht.

 



9:30 AM GMT  |  Read comments(0)