Finding the Elusive Lookup Field with SQL Query against Sharepoint's Backend Database

Because Sharepoint data is housed in SQL, it's possible to query it using T-SQL (example here ).  Of course, M$ says you shouldn't query the database directly, as it could impact performance, schema could suddenly change with a sharepoint update, blah blah blah.  There ARE supported ways in which to query Sharepoint data in SQL, such as Sharepoint List Association Manager on codeplex.

Finding the data isn't too hard.  But the other day I encountered a list with fields of type "lookup" in sharpeoint, and realized that lookup fields are handled differently in the database.  You can't just look at the UserData table fields like you can with other types of data.

Lookup data requires a JOIN between UserData and AllUserData tables on the UserData.intX and AllUserData.tp_ID fields for the corresponding list in AllUserData.  So, I used the concept of querying Sharepoint list data in SQL and added the following steps to the process:
Find the proper tp_ListId in AllUserData by looking for the List Name in tp_DirName.  This value is not going to be the same as the tp_ListId value in UserData because "lookup" means it's two different lists ;)
Review the UserData table int fields and find the one that corresponds to the AllUserData.tp_ID, and create your table join here.
In my case, the value I needed was stored in UserData.int7:

SELECT     WSS_Content.dbo.AllUserData.nvarchar3 AS EmployeeName
FROM     WSS_Content.dbo.UserData INNER JOIN WSS_Content.dbo.AllUserData ON WSS_Content.dbo.UserData.int7 = WSS_Content.dbo.AllUserData.tp_ID
WHERE     (WSS_Content.dbo.UserData.tp_ListId = 'value of UserData.tp_ListId') AND (WSS_Content_TServer.dbo.AllUserData.tp_ListId = 'value of AllUserData.tp_ListId')

multiple login prompts to sharepoint / wss

this seems to be a fairly common problem in sharepoint, and it can be caused by different things.

first, the core of the issue is not sharepoint failing to authenticate; it's an authentication failure at the Windows OS level.  by default, Windows re-prompts for authentication three times when there's a problem with authentication, and then it denies access.  in sharepoint, the three prompts are seen, but there usually is no message stating that authentication has failed after the third prompt.  (however, you can verify the failure by reviewing Event Viewer->Security logs on the web front end server)

here are the issues that can lead to multiple prompts for authentication

• IE is configured to always prompt for credentials:  in this scenario, the solution is usually to change IE settings so that it passes the credentials of the currently logged in user.  see here for more information.
  

• permissions problem with a file or directory:  this one can be as simple as the company logo being on a file share / directory that users don't have at least read permissions.  if it's a problem with an image on the page, you'll see a red x instead of the logo, for instance.  you can either add read permissions to the file share in which the image file resides, or add the image to a picture library in sharepoint to which all users have at least read access. if it's not an image on the page, it could be a more difficult problem to track down, such as a file in your virtual directory with improper permissions (see here for an example).

• sharepoint needs to be extended to a new zone:  you can tell if you need to extend your site to a new zone by watching the url in the address bar as you see each login prompt.  for testing, try the following:  at the first login prompt, click cancel without inputting your credentials, and make note of the url.  then close your browser, launch a new session and provide credentials to the first prompt, but click cancel on the second prompt, and make note of the url before closing your browser.  continue this process for the third prompt.  if at any point the url changed, it's an indicator that you need to extend your site to a new zone and configure alternate access mappings (AAM) in central administration.  see here for a great article on AAM.