Sometimes as a software developer you find that what crosses your desk is a workload distinctly of the black art variety, by black art I mean network and infrastructure related. Neither of these are remotely within my sphere of interest so it’s always ‘a real pleasure’, in no way whatsoever… Just such an example recently hit my desk.
The scenario is that we have a web app which uses integrated security, passed across from the browser, to log into SAP Business object on the back end. This log should be their windows domain login so that they only see the reports to which they have permissions. This has always worked fine and dandy, in fact it still does for the majority of users…. It was only the users who were moved across onto newer infrastructure who were experiencing issues. Now, I have to be honest.. the error handling wasn’t the greatest on this site. It seems that not being able to log onto the SAP Server wasn’t foremost in the developers mind when writing the code; It was therefore only after a lot of investigation that we discovered that this is actually what was the core of the problem.
The problem though, was “what was causing this issue”. Stepping through the code on the server I could see that the correct network user was indeed coming across.
The impersonation code seemingly worked with no errors upon invocation. However any attempts to logon to SAP BOBJ using integrated security failed with a totally useless message of ‘Internal Error’. For anybody familiar this is the message that BOBJ raises at just about any failed effort to logon to the BO Session.
So there were a couple of things to try here…
- Log on to SAP Enterprise using enterprise login – This would exonerate the issues being with the BO Server itself
- Run the web-app from the server logging on using windows AD – This would exonerate issues with Active Directory per se…
Of course, both of these particular tests worked… of a fashion. Test no 1 worked without issue meaning that we had no problem with the BOBJ Server itself. Reverting the code back to using window AD for Test 2 showed that depending upon how we logged into the site windows AD was not broken
- If we logged on using the NetBios name, that is “\\MachineName\WebsiteName” windows AD authentication worked without issue.
- If however we logged in using the IP address, that is “http:\\192.168.0.99\WebsiteName”… Windows authentication failed.
As a “by the by” when logging onto the server using either of the above methods from a remote machine the authentication failed to yield a usable logon at all for the SAP BOBJ login.
Lost in Translation
The problem here was that “internal error” was telling me absolutely nothing. I needed to understand EXACTLY what the error message was. I decided to add some simple code to log into a SQL Server database using windows authentication as well. Maybe that would assist with tracking this bug down….
Bingo Crepescule!
Bingo! We now get a decent error that actually means something
So we can see that our login is not being passed on to the SQL Server. This must also be the issue that SAP was trying to tell us about, in an obtuse way. It was at this point that words that I have heard, yet never uttered until this point, started forming on my tongue….
Double Hop
Simplistically speaking a “Double Hop” is when a client connecting to one server then, likely unbeknownst to them, needs to use their credentials to connect to a second server downstream. The authentication token provided to the initial server is sufficient to be used in the context of that initial server but it cannot be passed on for the second call to the downstream server.
Much, much, much reading later I discovered that despite the whole internet being rammed full of information, and full of “how to’s” on Delegation I discovered that it was all very piece meal and not explained in a language that I understood. This was further fuelled by the fact that I have never even seen Active Directory before… They just don’t give the keys for stuff like that, to reckless developers like me!
So I thought that I would try and capture the essence of the internet on delegation, without the complexity. In a nutshell the newer versions of operating systems/browsers can pass their credentials to one machine. The act of passing them does not, however, give the machine the right to use them to provision services as AD will not accept credentials that are not trusted. Machines therefore need to set up trust relationships so that certain machines *may* pass credentials on behalf of a user to a secondary set of services.
In my instance, although I trusted the web server with my credentials, the Active Directory domain did not trust that the IIS Web Server had the right to use my credentials to request other services (SAP BOBJ, SQL Server). The domain thus replaced them with an anonymous log in….
Delegation & SPNs (Server Principal Names)
What needs to happen is that the web server needs to establish its own credentials to be trusted in passing along other sets of Active Directory credentials. This is done through delegation. Active Directory is very granular with its delegation. Instead of trusting that ‘Server A’ can delegate to ‘Server B’ it tends to be that ‘Server A’ can delegate to ‘A service owned by a particular user that just happens to be located on Server B ‘. This is achieved through the use of Server Principal Names or SPNs. These are in effect a Unique Identifier across a network to a service instance.
Most services will try to automatically create these SPN’s during their start up phase. This of course relies upon the service account that invokes these services having the necessary privileges to this. You can see if the account controlling your SQL Server has the required elevated rights by examining the error log thus:-
exec sp_readerrorlog 0,1,null, nullWhat you should see if you look the through resultant log entries is an entry that looks similar to these:-
SQL Server is attempting to register a Service Principal Name (SPN) for the SQL Server service. Kerberos authentication will not be possible until a SPN is registered for the SQL Server service. This is an informational message. No user action is required.Further on in the log you should also see something like this… This shows you that an SPN has been registered against the AD user account that the service runs in.
The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/ServerName.domainName.local ] for the SQL Server service.Seek and Ye Shall Find
If you need to take a look at the SPN’s defined for any given user you can just type this simple command into a command window from any machine on the domain
setSPN -l usernameIf I run this for a particular account on our network you can see that there are 6 separate SQL Server SPN’s registered with this fairly high level account, each pointing to a different SQL Server instance somewhere in the network. These can therefore be trusted individually.
Trust is Earned, Not Given
Thus setting up the trust relationship between your web server and the SQL Server Service located on a particular box becomes as easy as:-
- Selecting the Web Server computer in Active Directory
- Scroll down to the delegation area and doing the following:-
- Select the ‘Trust This Computer for Delegation to Specified Services Only” option
- Underlying this select the ‘Use Any Authentication Protocol” option
- Press the ‘Add’ Button
- Select the domain name of the user that is responsible for running the service account that you wish to connect to
- You will then be prompted with a list of all of the “Server Principal Names” associated to this user. Select all of the ones that you are interested in. In the below example I have selected just the one SQL Service which has the full security name of MSSQLSvc/ServerName.domainName.local
- You may then press the update button
- For safety’s sake I would check that those settings have persisted when you come back to them. Some older versions of AD apparently only persist the changes 50% of the time
- When you are sure that they have persisted you should be able to invoke your web service as desired.
Needless to say once we added the SPN to our delegation list for the web server our service was now able to function as it had for the last 5 years, trusting users in a world deeply lacking trust.