Microsoft Dynamics CRM (Archived)

Possible kerberos issues with Reporting services

(0) Share

Report

Posted on by VITALTH

Hello,

I have a CRM Server containing a CRM 2013 Server and Reporting Services server. (I will call it here srv-crm)

I have a SQL Server containing data but not from CRM database (another database). (I will call it here srv-sql).

I create a report I put on CRM where the data source coming from srv-sql).

I tried to create a Kerberos Authentification to have an integrated Windows authentification during all connections to databases.

For this, in IIS I gave an identity for CRM for a domain account (I will call it here svc-crm). In the same server for Reporting Services I execute the service with the same domain account.

In srv-sql I execute the SQL server with another domain account (I will cal it here svc-sql).

In the reportingserver.config file I check if the node AuthentificationType is <RSWindowsNegotiate>.

In IIS I check for my Site if the Windows Authentification and Anonymous Authentification are enabled and I choose Negotiate as providers.

I create SPN like this

SetSPN -S http/srv-crm dmn\svc-crm

SetSPN -S http/srv-crm.dmn.fr dmn\svc-crm

SetSPN -S MSSQLSvc/srv-sql:1433 dmn\svc-sql

SetSPN -S MSSQLSvc/srv-sql.dmn.fr dmn\svc-sql

Where dmn is the domain where servers and accounts are.

In Active Directory after checking my account are not sensitive and can be delegated, for the account svc-crm I selected trusted this account for all services.

In my client browser (IE) I check that Windows Authenfication is selected. I am in intranet network.

I tried to see my Report in CRM and I have this issues :

[ReportServerException : Failed to open user session svc-crm ]

[ReportServerException : Impossible to create connexion to datasource 'MyDataSource'. (rsErrorOpenningConnection)]

(My CRM is in French so the error message are in French, maybe the translation I gave is mine, maybe it is not exactly the same message in English).

It is look like a problem to connect in Kerberos authentification. Because I set up SetSPN et deleguation to have an integrated Windows authentification

*This post is locked for comments

I have the same question (0)

All responses (17)

Answers (0)

Nuno Costa 1,300 on at

Like (0)

Report

Hi Vitalth,

If it is a Kerberos issue you should be able to troubleshoot it via the windows kerberos tracing, or using a network capture, try looking into that first.

However could you let us know what are your settings for the data source on the report?

Was this reply helpful? Yes No
VITALTH on at

Like (0)

Report

In event management in Windows on CRM Server for safety Windows Journal, I have different lines for the events occurs by seeing my report in Client brower : it is always my client account with Keberos as session opening process.

So actually you seems to have right it seems to be not a kerberos issues but the message is strange because to connect to datasource I shouldn't user the user srv-crm (because it is delegated)

In reporting services in BI Development Studiofor my datasoure I used Windows Integrated Mode as authentification. But you have to know that Datasource is not CRM database : it is another database. The fact is I don"t know if it is possible to query other database as CRM in report of CRM but when I see the error message it seems to be possible unless to give a good configuration

Was this reply helpful? Yes No
Hosk on at

Like (0)

Report

I would check to see if you have any duplicate SPN's which can cause problems

social.technet.microsoft.com/.../searching-for-duplicated-spns-with-setspn

Was this reply helpful? Yes No
VITALTH on at

Like (0)

Report

I have already checked and haven't duplicate SPN (otherwise I guess I would have a Keberos issue in event Windows journal).

But I have already had problem with Kerberos with SSRS where databases is onot on the same server. At this time, the problem was I had the same domain account for SQL Server service in a server and Report Server in other server (not duplicate SPN : juste the same account for two différents services).

I fixed the problem by having différents domain account fore ach server for each service.

Here the problem is different because as I have the same server for Report Server and Dynamic CRM which used the same services class (http) : I can not have two service account for the services because in this case I woul dhave duplicate SPN. Maybe it can be fixed if I have Report Server in another server than my MSCRM_Config database but I don't know it is possible and if it is I don't know how to configure.

Was this reply helpful? Yes No
Nuno Costa 1,300 on at

Like (0)

Report
Vitalth,

if you connecting to a different database on your data_source, try the following:

change the datasource to authenticate using a specific account:

selecting Credentials stored in the report server, type domain\user and type the password

then choose Use as windows credentials when connecting to the database and click test

Test connection

If the above works it means you can connect to the other server and fetch data... the previous setting windows integrated authentication means you have to set up the CRM ReportingGroup AD group to have access to the database you connecting to (Read-Access) so that the windows integrated authentication actually works

For the windows authentication to work as well you need to have SPNs correctly configured and as you said you can't see any Kerberos errors this could be correctly configured. (have you enabled Kerberos Debugging? by changing a registry key in your windows server?)

Hope this helps, let us know how you get on.

Was this reply helpful? Yes No
VITALTH on at

Like (0)

Report

Hello Nuno Costa,

I understand what do u suggest and I didn"t do the test you suggest in dot 1 of your post. But to answer to your proposal I have to explain you something and explain what I have done to answer to your question.

First of all my database is not only a SQL Server database but also a OLAP Cube;

In fact in my report I have a filter choice and according to this filter the graphical of OLAP cube filtered.

If I do what do you suggest, because SSAS can not be openned with a generic account with SQL, I will not see my report despite your test.

But I will explain what I have done :

- If I do nothing, at the displaying of report I have error message.

- If I give rights to SQL Server database for the account which execute CRM and Report Server services, at the displaying of reports I can see the filter part of my report (but nothing in my chart because of rights of OLAP cube).

So for me it is possible to access to data source for another database but I loose Client authentification.

I haven't yet changed registry key for Kerberos debugging but I know how to do. I can make it but I have to say you if I use Wireshark on CRM Server and filter in Kerberos, I see no records so I suppose it will be the same even if I enable Keberos debugging. But I will do it and keep you inform

Was this reply helpful? Yes No
VITALTH on at

Like (0)

Report

Actually I was not sure but liglever is already set in Registry Key for Kerberos debugging but nothing appears in Windows journal

Was this reply helpful? Yes No
Nuno Costa 1,300 on at

Like (0)

Report
okay it starts to become more clear...

more questions:

how is the connection string formatted?

does the other database works with AD and windows authentication? I'm assuming yes because you referring to kerberos

if you give access to the CRM account you say the report filter works but not the chart, I did not understand this bit correctly could you elaborate more?

When you mean client authentication do you mean you want all users to authenticate against that data source?

Can you check what is the error you get in the CRM report server?

Report server error is located under: C:\Program Files\Microsoft SQL Server\[YOUR_INSTANCE]\Reporting Services\LogFiles

Regarding kerberos logging:

http://support.microsoft.com/kb/262177

you want to look at the event viewer tool > system log

Was this reply helpful? Yes No
VITALTH on at

Like (0)

Report

To answer to your question :

1 - In CRM the connection string is :

data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true (I didn't change it it was like that by install)

In My report the connection strings are :

- Data Source=<My_Other_Server>;Initial Catalog=<My_Other_Database> for SQL Server

- Data Source=<My_Other_Server>;Initial Catalog=<My_Analyses_Services> for SSAS

(It is the same server for the two datasources)

2 - Yes the other databases are SQL Server and Analyses Server of SQL Server and work with AD and Windows authentification

3 - Ok it's pretty hard to understand.

Suppose I have a date table in my other database (for instance with datetime as primary key (but not the case in reality) and I have a CUBE where I have opportunities with estimated value as Fact and date as dimension and I display in chart the sum of value in ordonnate and date in abcisses. But in SSRS in gave filter for years (actually my char isn't so simple but it is just an example).

I use a SQL query with datasource = my other database for filter and a MDX query with datasource = my OLAP cube for chart.

If I give right in SQL Server for SQL database I can see the filter (choice of year) but not I see nothing in my chart.

4 - When I say client authentification, I say Windows integrated authentification, that means if we are in intranet network, the client is known (by his connection) and I pass the authentification through the different service until to database and database check if the client has right to connect to database. Usually if I didn"t misunderstood, we use Kerberos authentification.

5 - This is the translation french to English by myself I am not sure it is exactly the same message in SSRS in English set up :

Fail to open the user session 'dmn\crm-svc'.

6 - Thanks for URL but I knew to activate it byt in System log in CRM server I have nothing when I try to display report from a client browser

Was this reply helpful? Yes No
VITALTH on at

Like (0)

Report

Just to be sure to be ubderstood :

dmn\crm-svc is the account which execute SSRS but also the application pool of CRM in IIS

Was this reply helpful? Yes No