This will be by far the longest speaking journey I have undertaken as New Zealand is on the antipode of Western Europe give or take a few swims.

I will be speaking about one of my favourite topics: ETL and SSIS. But with a twist since the workshop I am delivering is all about Biml.

Business Intelligence Markup Language is a wake-up call

If your one of the 3 persons who read this blog, you probably have seen Biml mentioned on previous posts (like this one: https://theblobfarm.wordpress.com/2015/11/19/biml-online-beta-biml-without-the-software/)

And apart from my eternal gratitude for being one of the 3 readers, you know that Biml basically removes all the tedious work out of building SSIS packages for keeping only the business part.

Whenever I present on the topic, it is always a grateful experience. The gain in productivity is so obvious that attendees who don’t know Biml always are in for a wake-up call.

There is a lot more to Biml than building SSIS packages of course and this is what I will show in my workshop.

In the workshop we will build a complete metadata driven data warehouse and have a greater and deeper look at these elements of building the solution:

• Metadata driven: decoupling the metadata from the solution, seamlessly using MDS, Excel, Tables or event XML files for describing the transformations and relations
• Implementing DW specific features and patterns like: Inferred members, degenerate dimensions, Type 2, 3 and 4 historic
• Profiling you source data in a seamless way
• Doing Unit test and Integration testing
• Protecting your metadata code and IP by using C# class libraries

What you will leave this session with is the foundation for a BIML Script framework, which will allow you to tear-down and recreate your ETL process in minutes, instead of having to recode each package by hand.

Register here

If you are at Difinity come and say hello!

Happy biml’ing !

While configuring Kerberos Constrained Delegation for Power BI Report Server is not very different from other setups, there are a few things that you need to be aware of before moving forward:

• You need protocol transition
• You might need to register a SPN for the MSOLAPDisco.3 service if running a named instance.

This blog post is an explanation on how to setup Kerberos Constrained Delegation for:

• Getting data from SSAS within a Power BI report
• Getting data from SQL with a Report Server report

Please keep in mind that at the date of writing, the preview of Power BI Report Server only supports SSAS live connection. More will be added a later point in time.

Installing Power BI Report Server is quite trivial and explained in detail in other blog posts, so I invite you to read those if you need info about that part:

https://powerbi.microsoft.com/en-us/documentation/reportserver-install-report-server/

and this great YouTube video by Dustin Ryan: https://www.youtube.com/watch?v=jfTrFEsoOBs

Why Constrained Delegation

In many demo cases, you will have an all-in-one server where you have installed Power BI Report Server, SSAS (tabular or multidimensional) and SQL Server. In those cases you don’t need any form for credentials delegation since the Report Server is on the same box than the data source.

But there are scenarios where you have a distributed environment like the one I have on my VMs demo domain and for jumping around servers and passing credentials around, you need to setup Kerberos Constrained Delegation. Furthermore you will need protocol transition for it to work in Power BI Report Server. Let me explain….

Without Kerberos Constrained Delegation, you will get that error when trying to view a Power BI report with a SSAS data source

And when connecting a report to a SQL Server it would look like that using integrated security

The setup

Virtual Environment Servers

Service Accounts

• Analysis Services: OFL\SSASSvc
• Power BI Report Server: OFL\SSRSSvc
• SQL Server: OFL\SQLSvc

Configuring Constrained Delegation

The most crucial step when configuring Kerberos Constrained Delegation is gathering the information necessary to the setup.

The service accounts are listed above and the first step is to make sure that we have the SPNs we need. For that we use

SetSPN

SetSPN
is available on every server but requires domain admin permissions to make change to the Active Directory.

In a previous post, I have explained how to give non Domain Admins permission to configure Constrained Delegation via Group Policies: https://theblobfarm.wordpress.com/2016/12/05/allowing-non-domain-admins-to-configure-kerberos-constrained-delegation/

1 Registering SPNs for Power BI Report Server

There are 2 cases:

1. Report Server running under a domain account using the server names and user accounts listed above:
   SetSPN -s HTTP/SQL1 OFL\SSRSSvc
   SetSPN -s HTTP/SQL1.OFL.Local OFL\SSRSSvc
2. Report Server running under Virtual Service Account or Network Service:
   The server name should already have a SPN listed for HTTP. Control that using
   SetSPN -L SQL1
   Yields that result
   

2 Registering SPNs for the SSAS data source

1. For the named instance:
   SetSPN -s MSOLAPSvc.3/DC:TAB OFL\SSASSvc
   SetSPN -s MSOLAPSvc.3/DC.OFL.Local:TAB OFL\SSASSvc

Since we are running a named instance, we need to register a SPN for the SQL browser service. If the SQL Browser service is running under Local System, Local Service or Network Service, you need to register a SPN using the computer name, else the domain account

1. For the SQL Browser – running as Network Service in our case:SetSPN -s MSOLAPDisco.3/DC DC
   SetSPN -s MSOLAPDisco.3/DC.OFL.Local DC

3 Registering SPNs for the SQL Server

Since I am also connecting to a SQL Server from the Report Server, I also need to register SPNs for it.

It is running under a default instance, so we only need to specify the port number.

Since my data source is on a database located on the server SHP.OFL.Local I need to add the SPNs below

SetSPN -s MSSQLSvc/SQL1.OFL.Local:1433 OFL\SQLSvc
SetSPN -s MSSQLSvc/SQL1.OFL.Local OFL\SQLSvc

4 Setting the Constrained Delegation

Now that we have all the SPNs in place, I need to find my Report Server Service account in Active Directory Users and Computers and click on the delegation tab. Be aware that this tab is only available if you have an SPN registered for that user.

Please, keep in mind that the internals of Power BI Report Server requires protocol transition so we’ll need to pick the right kind of delegation

Remember that the service account we are looking at is our “middle-man” for going from A to B. So this man in the middle need to be able to pass credentials forward from A to B. In that case B is our Analysis Services instance, more specifically it’s service account and the SQL Browser.

Call out :

1. Add Service for delegation
2. Click Users or Computers
3. Choose the SSAS service account
4. Add the MSOLAPSvc.3 Service
5. Click Users or Computers
6. Choose the DC computer account
7. Add the MSOLAPDisco.3 Service

The expanded result should look like that

Repeat the same steps but this time add the SQL Server Service account (SQLSvc). Once done it should look like the result below

5 Modify the report server config file

The file is located here: C:\Program Files\Microsoft Power BI Report Server\PBIRS\ReportServer

By default, Power BI Report Server (and reporting services) is using the NTLM protocol (RSWindowsNegotiate) as shown below

we need to modify the rsreportserver.config file to use RSWindowsNegotiate (trying Kerberos and falling back to NTLM) or RSWindowsKerberos (Kerberos only) so it looks like that

6 Stop and Start the Power BI Report Server via the configuration tool

7 Test the configuration

The result should look like the image below

And for SQL Server data source

If you still get the error it is due to the cache mechanism. The best way to get rid of it is to wait or to reboot the Power BI Report Server if you are allowed to (When in doubt, reboot).

I hope that it has given you some more information around how to configure Power BI Report Server in a distributed environment and an idea of why Kerberos isn’t that complicated, as long as you are very specific in what you are trying to achieve.

Happy Power BI’ing !!

From May 1^st to May 3^rd in Copenhagen.

For the second year in a row, I am heavily involved in the planning and organization of Scandinavian’s largest data platform conference in Copenhagen: SQL Nexus

For the second year in a row we have gathered speakers from every part of the world.

For the second year in a row we are hosted by Cinemaxx at Fisketorvet in Copenhagen. If you’ve never attended a conference in this venue I can tell you that every speaker I spoke to from last year was totally in awe by the AV capacity of those huge screens, making use of Zoomit or other magnifying software totally unnecessary.

Following the recent announcement of the SQL Server 2017 we have a content focused on the features that have been made available in that release of SQL Server, which is – according to many – the most important SQL Server release since SQL Server 2005.

The event is split up in two parts

• Full-day workshop
• Main conference with 6 tracks and 60+ sessions over two days. Check the agenda here

What is learning without sharing?

Since we believe that the community is very important for the development of the knowledge around the Data Platform we have arranged a Community Zone where the speakers will hang out after their sessions so you – the attendees – have a chance to chat with them and ask whatever questions weren’t answered during their session.

Tuesday evening we turn the ground floor of Cinemaxx into a Gin Bar and the outdoor area into a he BBQ and beer area. Let’s hope the capricious Danish weather Gods will be clement.

The community around SQL Nexus is astonishing.

On the volunteers side of the event, we have:

Kenneth M. Nielsen, my partner in crime in organizing SQL Saturdays, speaking engagements and Gin tasting.

Mark Broadbent who used to be the hardest drinking DBA on the planet and still a great chap. Mentioned SQL Nexus to me in the first place back in Edinburgh in June 2014.

SQL Server User Group members : Claus Munch, Kennie Pontopiddan, Jesper Johansen and countless other helping hands who will keep the Community Zone going during those days

From the event organization side, we have:

Kathrine Nørrelund: Keeping all things together and us volunteers under control

Lars Bo Granath: Making a heroic effort to push Microsoft message through to us community cone heads and using his clout to get amazing Microsoft Corporate speakers involved.

Martin Kronborg: In charge of all the practical details around the event, sponsor area, party night, catering and many other things.

Sanne Kreiner: Web wizard keeping the website under control with the speed of light.

So if you meet one of those guys during the event, give them a hug and thanks them for their awesome work!

Of course, this event is only possible thanks to the speakers, the attendees and the sponsors.

Bottom line, whether you are into Database Development, Business Intelligence, Database Administration, Advanced Analytics or IoT, don’t miss the chance to network with peers, learn from the best and join us for 3 days of total immersion into the Data Platform in fantastic surroundings.

Did I mention that you even can pay the conference fee with your Microsoft Premier Support agreement ? How awesome is that ?

Don’t miss the chance to attend SQL Nexus, go and check it out now on http://www.sqlnexus.com

On March 9^th and 10^th, there are 3 preconference workshops held by some of the very knowledgeable people in the industry, so if you are near you don’t want to miss

Friday 10^th:

• Advanced Data Management with MDS by Tillmann Eitelberg and Gabi Münster
• What the Hekaton?! How to Get 100X Faster Performance in SQL Server 2016 by Kalen Delaney

Thursday 9^th

• How to Tune SQL Server Indexes with Execution Plans by Kendra Little

This is the third time, I have the privilege to be invited as a speaker at this great event (which by the way was the first SQL Saturday outside the US, and the second I spoke at).

This year my session is all about Biml : Biml for advanced developers

This is the early morning session, my favorite hour, soI hope that people are awake at that time of the day because I won’t rest until we manage to achieve Continuous Deployment of SSIS projects in Biml.

If you are at the event or near Microsoft office in the Portuguese capital, don’t hesitate to come to me and say hello!

Looking forward to some great days of learning and networking in Lisbon and seeing all my SQL friends and colleagues.

But now it is officially available to download for an on-premises installation as a 180 days preview.

What is really interesting, besides the fact that we get Power BI reports on-premises, is that there is a whole new installer to perform the installation.

This is how it looks like :

After a few seconds downloading the software and a very short installation periodd, it is now installed!

This is so simple. Why didn’t you do that before Microsoft ?

Right after that you are presented with the well known Reporting Services Configuration Manager. From there it is a well known process to configure Reporting Services.

After that the process is exactly the same than when configuring SSRS for SQL Server 2016. You get the scale out option and of course the Power BI integration!!

Once it is installed you can navigate to the portal and create a new Power BI Report and get data from your favorite source.

Adding a Power BI report opens a dialog just like when using Report Builder and I was expecting Power BI Desktop to start automatically – since it is installed on my PC – but it didn’t (double checked that Report builder did start automatically, and it did)

Interestingly the Publish To Pyramid Analytics is now gone from the Publish menu in the January version of Power BI Desktop (Version: 2.42.4611.701 64-bit (Januar 2017))

I couldn’t find any way to save to my Reporting Services instance, the Web Portal URL and the Web Service URL did not work so I uploaded the report manually.

If you try to upload anything else than a report containing data from a SSAS instance (tabular or multidimensional), you’ll get the following error:

Using SSAS data on the other hand will give you the expected result !!

Update

After installing the PowerBI Desktop for Reporting Services that was made available for download together with Reporting Services I am able to edit reports directly from the Reporting Services portal and to save Power BI reports straight into Reporting Services

Saving in Reporting Services now work directly from Power BI as shown below:

Conclusion :

• We are getting this long awaited on-premises Power BI Desktop
• The installation process is really simple, it is a pleasure.
• No publishing to Pyramid
• I was not able to save the Power BI desktop file directly to my reporting services instance
• For now I can only connect to SSAS on-premises, but live query seems supported, although I haven’t tried
• I LOVE IT ALREADY !!

In a next post, I will try to pass Reporting Services parameters to the Power BI report and see if that works…

Happy Power BI’ing

Next week, Monday 23rd, marks the start of the first edition of the MSCloudSummit in Paris, France.

This will be my very first public speaking in my country of birth and although I am a native speaker I find that speaking about the technology I work with every day in French is very challenging.

So expect a mix of English terms, Danish sarcasm and French style for my session about Azure Data Factory and SSIS, where I will compare both technologies on a functional, technical and performance level. This session takes place on January 24th at 12PM.

There are also a lot of French and foreign speakers doing sessions about everything Cloud related during these 3 days. Check the agenda for a complete list.

I am also delighted to be part of a panel of speakers sharing experiences about Microsoft certifications at 1045AM on the 24th.

A bientôt à Paris !!

Power BI reports, that works together with SSRS 2016 and that has been available as a Technical Preview in Azure will be available in an on-prem preview version mid-januar 2017. A production ready version of the product that will give Power BI reports in SSRS will be – according to the plan – available mid-2017.

Furthermore support for custom visuals, data refresh, additional connectors and Power BI Mobile will be supported moving forward.

This is all part of an announcement made by the SSRS team today on their blog : https://blogs.msdn.microsoft.com/sqlrsteamblog/2016/12/16/power-bi-reports-in-sql-server-reporting-services-feedback-on-the-technical-preview/ where they also disclose that Power BI reports in SSRS will not be shipped with any SQL Server 2016 service packs or cumulative updates.

Happy Power BI’ing!

In many larger organizations where there is a clear separation of duties this is can be problem since the engineers and developers need to specify what type of delegation need to be configured, and domain admins need to execute on that, not knowing what this is for and in many cases not knowing how and why Kerberos Constrained Delegation (KCD) operates.

Anyone that has tried to work with KCD knows what I am referring to.

This post is not about how to configure KCD but rather how to give a group of non-domain admin persons the ability to setup KCD without giving them other unnecessary domain-wide permissions.

This can be accomplished in a few steps, using an Organizational Unit (OU), a group for membership and a Global Policy applied to the OU.

Below is a description of how to achieve that.

For the sake of this article let us suppose that we have a group of DBAs that need to enable KCD for their BI developers so they can use row level security on their data sources (SSAS or SQL Server databases).

Let’s start by creating an OU where the servers we need to delegate can be placed. I name this OU SQL Server Data Sources

In that OU, let’s place the servers for which we need to be able to configure Kerberos Constrained Delegation. In my example I am placing my two database servers, SQL1 and SHP in the newly created OU.

The next step is to create a Security Group in which the users that need to be able to configure KCD for those servers will become member. I call this group DBA and it has one member called regbac.

Now we need to find the exact security settings that will allow members of this DBA group to configure Kerberos Constrained Delegation. For that purpose we need to view the advanced security properties of the newly created OU.

Properties à Security à Advanced

Next step is to Add a principal by clicking on Add and in the “select a principal” link, choosing the DBA security group.

The first thing to do now is to give this DBA group permissions to modify some properties on all the computer objects that we will put in the OU (our data sources).

Now, switch the “Applies to” setting to “All descendant objects” (This means all objects within the OU) and give DBA permissions to Write all properties. Click OK.

Now I need to add some special permissions to computer objects, so I click Add again. Once again, I’ll select the DBA group, then I need to switch to Descendant Computer objects. I click Write and then scroll down until I see Validated write to service principal name. I’ll click the box to enable it, and then OK, OK, and OK.

The end result looks like below :

2 permissions for DBA group,

• All descendants objects : Write all properties
• Descendant computer objects : Validate write to Service Principal Name

Now that we have managed the permission part, we need to create a group policy to enable thrust for delegation. This requires that I modify the Group Policy for the OU.

In group policy management, create a new Group Policy called SQLDBAforKCD.

Choose Edit (right-click), this brings up the Group Policy Management editor. The policy I need to change is under the Computer Configuration. I’ll expand Policies, Window Settings, Security Settings, and Local Policies. I’m looking for User Rights Assignments. Now I see a list of all the policies. I’m looking for Enable computer and user accounts to be trusted for delegation.

Add the DBA group to this policy.

Now back in Group Policy Manager, let’s find the OU. I’ll locate the SQLDBAforKCD policy and drag it to the OU. Yes, I do want to link it there, and I’ll also enforce it. Now the members of the DBA group have rights to set up Kerberos delegation.

Let’s test it by logging onto the server, running Active Directory Users and Computers as regbac and validate that the delegation is active for that user,
by trying to set or alter an active delegation:

Trying to do the same with an object placed in other OU doesn’t work, thus proving that we have successfully enabled KCD on that OU only.

Next steps

In a next post I will suggest a OU design for Kerberos and BI.

If you missed my talk about Kerberos for BI please come to a SQL Saturday near you….

Happy KCD’ing!!

Special thanks to Niels for reading the article and suggesting edits before publishing it!

SQL Saturday Copenhagen #541
Besides co-organizing it for the fourth time I will also be speaking (selection process was may be a bit biaised) about one of my favorite topics. Automated deployment and integration : Continuous Integration with SSDT and Team Foundation Server

SQL Saturday Madrid #568
For the third time on Spanish ground for a SQL Saturday, I am really looking forward to this event where I will present about building custom Power BI visuals : A beginner’s guide to Power BI Custom Visuals

SQL Saturday Krakow #534
At the end of September, it’s to head towards Poland (my first visit ever to this country) and to present once more about this subject so dear to me. Continuous Integration with SSDT and Team Foundation Server. I never get tired of the topic and with the introduction of TFS 2015 update 2 (with Release Management) the possibilities are incredibly flexible and easy to implement.

SQL Saturday München #555
Back to München and this time at Microsoft’s brand new HQ for a brand new session which is built on many years of blood, sweat and tears: Kerberos for BI

I have also submitted sessions for the first SQL Saturday in Prague ever and for this year’s SQL Saturday in Slovenia and hoping to be selected.

So this is going to be quite a busy fall!!

If you come by one of those events please stop by and say hello !!

The error I got was :

Method not found: ‘Void Microsoft.SqlServer.Management.IntegrationServices.EnableSsisSupportAlwaysOnSqmHelper.Initialize()’. (Microsoft.SqlServer.IntegrationServices.UITasks)

Looking at the documentation for the namespace Microsoft.SqlServer.Management.IntegrationServices I quickly figured out that I would be able to create the SSIS Catalog manually using PowerShell.

But then I couldn’t locate the Microsoft.SqlServer.Management.IntegrationServices dll anywhere except from in the GAC so I had to load it a somewhat cumbersome way (with help from Remo). Below is the script I used for doing that.

First portion is for enabling CLR like it does in the SSMS dialog:

sp_configure 'show advanced options', 1; 
GO 
RECONFIGURE; 
GO 
sp_configure 'clr enabled', 1; 
GO 
RECONFIGURE; 
GO

The second part loads the assembly.

$assemblyPath = 'C:\Windows\assembly\GAC_MSIL\Microsoft.SqlServer.Management.IntegrationServices\13.0.0.0__89845dcd8080cc91\Microsoft.SqlServer.Management.IntegrationServices.dll'
$fullName = [System.Reflection.AssemblyName]::GetAssemblyName($assemblyPath).FullName
[System.Reflection.Assembly]::Load($fullName)

Then it’s only a matter of connecting to the server and creating the catalog manually using the Catalog method

#Because I am lazy and do not want to retype this long namespace everytime
$IntegrationServicesNamespace = "Microsoft.SqlServer.Management.IntegrationServices"

#Connect to the server
$sqlConnectionString = "Data Source=localhost;Initial Catalog=master;Integrated Security=SSPI;" 
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $sqlConnectionString

#Create the catalog
$integrationServices = New-Object $IntegrationServicesNamespace".IntegrationServices" $sqlConnection
$catalog = New-Object $IntegrationServicesNamespace".Catalog" ($integrationServices, "SSISDB", "P@ssw0rd") 
$catalog.Create()

After that I was able to use my newly created SSIDB Catalog.

Happy SSIS’ing