Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

We've recently migrated our virtualised SQL databases to a bare metal solution in an active/active cluster. Everything is working great except SQL Reports.

Ok, so a rundown of our setup here as it relates to SQL and Reports:

  • XXYYSQ01 is our old virtual SQL server, now running Reporting Services.
  • XXYYSQ1 is a new SQL server, running on bare metal. It does not house the database that we are attempting to access.
  • XXYYSQ2 is the other new bare metal SQL server. It runs the database we are looking to access.
  • SQLCLUSTERDB\DATABASE is the name of the clustered instance hosting the database we wish to access.

Now, when someone access the reporting services website on their desktop and tries to run a report, they get the dreaded Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON' error. So I add the required SPNs against the service user for Reporting Services:

H:\>setspn -L DOMAIN\ssrs-user
Registered ServicePrincipalNames for CN=ssrs-user,OU=Systems,DC=domain,DC=local:
        MSSQLSvc/SQLCLUSTERDB.domain.local:DATABASE
        MSSQLSvc/SQLCLUSTERDB:DATABASE
        http/xxyysq01

Refresh and.... still not working. My Google searches led me to add the service user to the Act as part of the operating system and Impersonate a client after authentication user rights. Still nothing.

I head for the error logs. XXYYSQ01 offers the following:

An account was successfully logged on.

Subject:
    Security ID:        NULL SID
    Account Name:       -
    Account Domain:     -
    Logon ID:       0x0

Logon Type:         3

New Logon:
    Security ID:        DOMAIN\amason
    Account Name:       amason
    Account Domain:     DOMAIN
    Logon ID:       0x2d70c77c4
    Logon GUID:     {955a0a82-c5cf-ca60-d063-cc4a32a363be}

Process Information:
    Process ID:     0x0
    Process Name:       -

Network Information:
    Workstation Name:   
    Source Network Address: -
    Source Port:        -

Detailed Authentication Information:
    Logon Process:      Kerberos
    Authentication Package: Kerberos
    Transited Services: -
    Package Name (NTLM only):   -
    Key Length:     0

This event is generated when a logon session is created. It is generated on the computer that was accessed.

The subject fields indicate the account on the local system which requested the logon. This is most commonly a service such as the Server service, or a local process such as Winlogon.exe or Services.exe.

The logon type field indicates the kind of logon that occurred. The most common types are 2 (interactive) and 3 (network).

The New Logon fields indicate the account for whom the new logon was created, i.e. the account that was logged on.

The network fields indicate where a remote logon request originated. Workstation name is not always available and may be left blank in some cases.

The authentication information fields provide detailed information about this specific logon request.
    - Logon GUID is a unique identifier that can be used to correlate this event with a KDC event.
    - Transited services indicate which intermediate services have participated in this logon request.
    - Package name indicates which sub-protocol was used among the NTLM protocols.
    - Key length indicates the length of the generated session key. This will be 0 if no session key was requested.

XXYYSQ2 gives me:

An account was successfully logged on.

Subject:
    Security ID:        NULL SID
    Account Name:       -
    Account Domain:     -
    Logon ID:       0x0

Logon Type:         3

New Logon:
    Security ID:        ANONYMOUS LOGON
    Account Name:       ANONYMOUS LOGON
    Account Domain:     NT AUTHORITY
    Logon ID:       0xe684d0c85
    Logon GUID:     {00000000-0000-0000-0000-000000000000}

Process Information:
    Process ID:     0x0
    Process Name:       -

Network Information:
    Workstation Name:   XXYYSQ01
    Source Network Address: -
    Source Port:        -

Detailed Authentication Information:
    Logon Process:      NtLmSsp 
    Authentication Package: NTLM
    Transited Services: -
    Package Name (NTLM only):   NTLM V1
    Key Length:     128

This event is generated when a logon session is created. It is generated on the computer that was accessed.

The subject fields indicate the account on the local system which requested the logon. This is most commonly a service such as the Server service, or a local process such as Winlogon.exe or Services.exe.

The logon type field indicates the kind of logon that occurred. The most common types are 2 (interactive) and 3 (network).

The New Logon fields indicate the account for whom the new logon was created, i.e. the account that was logged on.

The network fields indicate where a remote logon request originated. Workstation name is not always available and may be left blank in some cases.

The authentication information fields provide detailed information about this specific logon request.
    - Logon GUID is a unique identifier that can be used to correlate this event with a KDC event.
    - Transited services indicate which intermediate services have participated in this logon request.
    - Package name indicates which sub-protocol was used among the NTLM protocols.
    - Key length indicates the length of the generated session key. This will be 0 if no session key was requested.

And:

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. Reason: Token-based server access validation failed with an infrastructure error. Check for previous errors. [CLIENT: RE.DA.CT.ED]

Argh.

Is there anything I'm missing here? I can run the reports just fine from XXYYSQ01 itself, so I know for sure this is a double hop problem. However, from what I've read, what I've done so far should solve this problem. There are no duplicate SPNs, I checked.

I have also changed authentication to Negotiate/Kerberos in the rsreportserver.config file. The connection string is Server=SQLCLUSTERDB\DATABASE;Initial Catalog=DataBase

Any assistance at all will be greatly appreciated.

share|improve this question

1 Answer 1

up vote 0 down vote accepted

Found it - the solution is to give the service account access to create its own SPNs. To do that, simply grant SELF to read and write the servicePrincipalName field:

Permission window for SELF with r/w SPN field allowed

Once you do that, you'll need to restart the SQL service. DO NOT USE THE SERVICES SNAP-IN WITH CLUSTERED SQL SERVERS! The Failover Cluster service will take this as a failure and cut it over.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.