in

SQL Server Blogs

Voices from the UK SQL Server Community

Rob's SQL Server Blog

... SQL Server thoughts from the field
  • Enabling Kerberos Authentication for Reporting Services

    Recently, I’ve helped several customers with Kerberos authentication problems with Reporting Services and Analysis Services, so I’ve decided to write this blog post and pull together some useful resources in one place (there are 2 whitepapers in particular that I found invaluable configuring Kerberos authentication, and these can be found in the references section at the bottom of this post). In most of these cases, the problem has manifested itself with the Login failed for User ‘NT Authority\Anonymous’ (“double-hop”) error.

    By default, Reporting Services uses Windows Integrated Authentication, which includes the Kerberos and NTLM protocols for network authentication. Additionally, Windows Integrated Authentication includes the negotiate security header, which prompts the client to select Kerberos or NTLM for authentication. The client can access reports which have the appropriate permissions by using Kerberos for authentication. Servers that use Kerberos authentication can impersonate those clients and use their security context to access network resources.

    You can configure Reporting Services to use both Kerberos and NTLM authentication; however this may lead to a failure to authenticate. With negotiate, if Kerberos cannot be used, the authentication method will default to NTLM. When negotiate is enabled, the Kerberos protocol is always used except when:

    • Clients/servers that are involved in the authentication process cannot use Kerberos.
    • The client does not provide the information necessary to use Kerberos.

    An in-depth discussion of Kerberos authentication is beyond the scope of this post, however when users execute reports that are configured to use Windows Integrated Authentication, their logon credentials are passed from the report server to the server hosting the data source. Delegation needs to be set on the report server and Service Principle Names (SPNs) set for the relevant services. When a user processes a report, the request must go through a Web server on its way to a database server for processing. Kerberos authentication enables the Web server to request a service ticket from the domain controller; impersonate the client when passing the request to the database server; and then restrict the request based on the user’s permissions. Each time a server is required to pass the request to another server, the same process must be used.

    Kerberos authentication is supported in both native and SharePoint integrated mode, but I’ll focus on native mode for the purpose of this post (I’ll explain configuring SharePoint integrated mode and Kerberos authentication in a future post). Configuring Kerberos avoids the authentication failures due to double-hop issues. These double-hop errors occur when a users windows domain credentials can’t be passed to another server to complete the user’s request. In the case of my customers, users were executing Reporting Services reports that were configured to query Analysis Services cubes on a separate machine using Windows Integrated security. The double-hop issue occurs as NTLM credentials are valid for only one network hop, subsequent hops result in anonymous authentication.

    image

    The client attempts to connect to the report server by making a request from a browser (or some other application), and the connection process begins with authentication. With NTLM authentication, client credentials are presented to Computer 2. However Computer 2 can’t use the same credentials to access Computer 3 (so we get the Anonymous login error). To access Computer 3 it is necessary to configure the connection string with stored credentials, which is what a number of customers I have worked with have done to workaround the double-hop authentication error.

    However, to get the benefits of Windows Integrated security, a better solution is to enable Kerberos authentication. Again, the connection process begins with authentication. With Kerberos authentication, the client and the server must demonstrate to one another that they are genuine, at which point authentication is successful and a secure client/server session is established.

    image

    In the illustration above, the tiers represent the following:

    • Client tier (computer 1): The client computer from which an application makes a request.
    • Middle tier (computer 2): The Web server or farm where the client’s request is directed. Both the SharePoint and Reporting Services server(s) comprise the middle tier (but we’re only concentrating on native deployments just now).
    • Back end tier (computer 3): The Database/Analysis Services server/Cluster where the requested data is stored.

    In order to enable Kerberos authentication for Reporting Services it’s necessary to configure the relevant SPNs, configure trust for delegation for server accounts, configure Kerberos with full delegation and configure the authentication types for Reporting Services. These steps are outlined in greater detail in the "Manage Kerberos Authentication Issues in a Reporting Services Environment" whitepaper in the resources section at the end of this article.

    Service Principle Names (SPNs) are unique identifiers for services and identify the account’s type of service. If an SPN is not configured for a service, a client account will be unable to authenticate to the servers using Kerberos. You need to be a domain administrator to add an SPN, which can be added using the SetSPN utility. For Reporting Services in native mode, the following SPNs need to be registered

    --SQL Server Service
    SETSPN -S mssqlsvc/servername:1433 Domain\SQL

    For named instances, or if the default instance is running under a different port, then the specific port number should be used.

    --Reporting Services Service
    SETSPN -S http/servername Domain\SSRS
    SETSPN -S http/servername.domain.com Domain\SSRS

    The SPN should be set for the NETBIOS name of the server and the FQDN. If you access the reports using a host header or DNS alias, then that should also be registered

    SETSPN -S http/www.reports.com Domain\SSRS

    --Analysis Services Service
    SETSPN -S msolapsvc.3/servername Domain\SSAS

    Next, you need to configure trust for delegation, which refers to enabling a computer to impersonate an authenticated user to services on another computer:

    Location

    Description

    Client

    1. The requesting application must support the Kerberos authentication protocol.

    2. The user account making the request must be configured on the domain controller. Confirm that the following option is not selected: Account is sensitive and cannot be delegated.

    Servers

    1. The service accounts must be trusted for delegation on the domain controller.

    2. The service accounts must have SPNs registered on the domain controller. If the service account is a domain user account, the domain administrator must register the SPNs.

    In Active Directory Users and Computers, verify that the domain user accounts used to access reports have been configured for delegation (the ‘Account is sensitive and cannot be delegated’ option should not be selected):

    image

    We then need to configure the Reporting Services service account and computer to use Kerberos with full delegation:

     image

    image

    We also need to do the same for the SQL Server or Analysis Services service accounts and computers (depending on what type of data source you are connecting to in your reports).

    Finally, and this is the part that sometimes gets over-looked, we need to configure the authentication type correctly for reporting services to use Kerberos authentication. This is configured in the Authentication section of the RSReportServer.config file on the report server.

    <Authentication>

    <AuthenticationTypes>

              <RSWindowsNegotiate/>

    </AuthenticationTypes>

    <EnableAuthPersistence>true</EnableAuthPersistence>

    </Authentication>

    This will enable Kerberos authentication for Internet Explorer. For other browsers, see the link below. The report server instance must be restarted for these changes to take effect.

    Once these changes have been made, all that’s left to do is test to make sure Kerberos authentication is working properly by running a report from report manager that is configured to use Windows Integrated authentication (either connecting to Analysis Services or SQL Server back-end).

    Resources:

    Manage Kerberos Authentication Issues in a Reporting Services Environment
    http://download.microsoft.com/download/B/E/1/BE1AABB3-6ED8-4C3C-AF91-448AB733B1AF/SSRSKerberos.docx

    Configuring Kerberos Authentication for Microsoft SharePoint 2010 Products
    http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=23176

    How to: Configure Windows Authentication in Reporting Services
    http://msdn.microsoft.com/en-us/library/cc281253.aspx

    RSReportServer Configuration File
    http://msdn.microsoft.com/en-us/library/ms157273.aspx#Authentication

    Planning for Browser Support
    http://msdn.microsoft.com/en-us/library/ms156511.aspx

  • SQL Server Waits, Locks and Blocking

    The master.dbo.sysprocesses table in Microsoft SQL Server 2000 and SQL Server 2005 is a system table that contains information about the active server process IDs (SPIDs) that are running on SQL Server.

    If you are using SQL Server 2005, you can also access this table by using the sys.sysprocesses compatibility view.

    The waittype column, the lastwaittype column, the waittime column, and the waitresource column in the master.dbo.sysprocesses system table provide information about the resources that the processes are waiting on.

     

     

    References:

    How to monitor blocking in SQL Server 2005 and in SQL Server 2000
    Blocked Process Report Event Class
    Description of the waittype and lastwaittype columns in the master.dbo.sysprocesses table in SQL Server 2000 and SQL Server 2005

    Cross Posted from blogs.technet.com/rob

  • SQL Server and Windows Memory

    SQL Server 2000 Enterprise Edition introduced support for the use of Windows 2000 Address Windowing Extensions (AWE) to address 8GB of memory on Windows 2000 Advanced Server and 32GB of memory on Windows 2000 Datacentre. With AWE, SQL Server can reserve memory not in use by other applications and the OS. Each instance must statically allocate the memory it needs. AWE memory can only be used for the data cache and not for executables, drivers, DLL's, etc.

    Cross Posted from blogs.technet.com/rob

  • Windows Memory (PAE/AWE/3GB)

    SQL Server 2000 Enterrise Edition introduced support for the use of Windows  

    Cross Posted from blogs.technet.com/rob

  • Oracle Unbreakable... Not a Chance !

    Good article here comparing the Critical Updates fixes between SQL Server and Oracle and some of the mis-information peddled in the RDBMS world. Highlights include zero SQL Server critical update vulnerabilities since September 2004, which I think is pretty impressive!

    http://blogs.technet.com/dataplatforminsider/archive/2008/04/14/unbreakable.aspx 

  • SQL Server 2000 Full-Text Issues After Failover

    I can't take credit for finding the solution to this as that goes to my colleague, but I thought I'd share a problem we experienced after failing over one of our production clusters last night. This is a SQL Server 2000 SP4 Enterprise Edition active/passive cluster running on Windows 2000 Advanced Server SP4. After failover the full-text functionality failed and we were unable to rebuild the catalogs or indexes as SQL Server was returning various errors. The path to the full text catalogs was OK as were all the permissions. The full-text service was reporting as online in the Cluster Administrator and the MS Search service was running on the server. The SQL Agent jobs set up to populate the full-text catalogs were failing with the following errors:

    Execution of a full-text operation failed. The Gatherer project has not been initialized. The catalog needs to be remounted. If the project is still not initialized, remove it. [SQLSTATE 42000] (Error 7619).  The step failed.

    After some head scratching and googling, which didn't bring up anything conclusive, my colleague found the fix in this article:

    http://support.microsoft.com/default.aspx/kb/889708/en-us?spid=2852&sid=669

    The registry keys that contained the location of the word noise files were available on one node of the cluster and not the other, as these keys are only created on the primary node from which the SQL Server setup program was run. The problem was fixed by importing the key HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\ContentIndexCommon\LanguageResources\Override from the other cluster node, setting the Full-Text  Service offline in the cluster Administrator, restarting the MS Search service and then setting the Full-Text service back online.

  • SQL2K8 Launch Thoughts

    Been reading Dan Jones blog post regarding the delay to the launch of SQL Server 2008, and I think he makes some great points. There is obviously dissapointment from some people about the release date slipping back, but as Dan points out, a bad product will be remembered for a long time. SQL Server has built up a reputation for reliability, scalability and security over the last couple of releases. I'd rather Microsoft protected that reputation than rush something out to meet a marketing deadline. From what I have seen so far with the CTP's, SQL Server 2008 is going to be well worth the wait !

  • Windows Server 2008 Clustering

    I've been having a look at Windows Server 2008 clustering and the first thing I can say is... wow ! Setting up clusters, adding additional nodes and setting up clustered resources is far simpler than before, and the cluster validation tool means there is no more need for the HCL. Clusters can also be configured so that the quorum is not a single point of failure, which increases resilience. The GUI is much cleaner and intuitive than previous versions of the Cluster Administrator, which makes configuration and administration a lot easier. There is more information on Windows Server 2008 clustering improvements here.

    If you want a closer look, there is a Windows Server 2008 Failover Clustering virtual lab available which goes over some basic clustering scenarios (but not SQL Server clustering I'm afraid !!)

  • SQL Server 2005 Bug Report

    Saw this link to a SQL Server 2005 bug report on the Microsoft Connect site. I guess someone was having one of those days Big Smile

    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=253524&wa=wsignin1.0

  • SQL Server 2008 Certifications

    As I am currently preparing for the MCDBA to MCITP 2005 upgrade exam (70-447) it may seem a bit premature to talk about SQL Server 2008 certifications, but there is a webcast available at Trika's blog (from the Microsoft Learning team) below.

    http://blogs.msdn.com/trika/archive/2008/02/08/sql-live-meeting-recording.aspx

    Something I found surprising from the presentation is that there are just over 4,000 MCITP Database Administrators in the world compared to 150,000 MCDBA's. This may be down to the take-up figures of SQL Server 2005 compared to SQL Server 2000 or it may be as a result of changing the exam format. I found the SQL Server 2005 MCTS exam (70-431) an improvement on the SQL Server 2000 exams. The exam is split into 2 sections: a multiple choice section and a simulation section. You need to pass both the sections, and the lowest mark is taken as your overall mark. This requirement to have at least some degree of hands-on experience is a major improvement and should help eliminate the braindumpers who ultimately devalue the certifications for everyone else !

    There is a useful search facility on certguard.com that enables you to search sites to see if they are braindump sites or if they are legitimate. It's worth checking as you could risk being decertified if you continue to use braindumps.

More Posts Next page »
Powered by Community Server (Commercial Edition), by Telligent Systems