Sharepoint or Sharepoint Central Admin Returns Error “The File Exists (Exception from HRESULT: 0x80070050)

One of my clients is a financial institution. As such, they have pretty stringent controls on user accounts for vendors that may need to work on their system. Several months ago, I was brought in to do an install of Business Portal, which requires SharePoint.

I was given a temporary admin level userid to use during the setup, which I used to install and configure SharePoint and Business Portal.

Today I was called back to resolve some issues with Business Portal. When I tried to log back into SharePoint, my access was denied. I tried to get to Central Admin and received the error – The file exists. (Exception from HRESULT: 0x80070050).

Basically, what has happened is that the user account I was given initially was deleted after the initial install was completed and then recreated when I was asked to come back and repair Business Portal. They also never created another Admin login for the Central Admin and SharePoint sites. When the new account was created, AD issued a new SID (Security ID) which is the string of characters that REALLY defines your account. Your username is just a descriptive field. SharePoint provides access based on the SID, not just the username field. So, my username is the same, but the SID is different, preventing me from getting to SharePoint. The problem is that my account is the ONLY one that has rights – not a good situation for the client to be in.

Fixing this problem….well, it’s not a simple procedure. I’ve spent nearly two hours now researching and testing ways of getting the SID updated in the SharePoint database.

Here’s the procedure to go through to get the SID changed for a user in SharePoint. You’ll definitely earn your Geek badge for this one.

WARNING: This is a low-level solution, requiring hackery and other risky behaviors. USE AT YOUR OWN RISK ONLY AFTER YOU HAVE A BACKUP.

Procedure:

• Get the SID listed in SharePoint
• Get your current SID from Active Directory
• Convert your current SID from binary SID to HEX
• Update the database with the new HEX value

Simple sounding, not so much in real life. After much searching and trial and error, here are the source files you’ll need to accomplish this.

1) Get the SID listed in Sharepoint

Connect to your SQL Server and run the following query on the AdminContent database for your SharePoint install:

SELECT Sites.Id, Webs.FullUrl FROM Sites inner join Webs on Sites.RootWebId = Webs.Id

This will give you a list of the sites and associates URL’s that SharePoint knows about. Copy the Site ID to your clipboard.

You’ll now run this script that will get you the other information you need – including the user’s SID which is in the tp_SystemID field. Paste your Site ID from above into the XXXX space below.

SELECT * FROM UserInfo WHERE tp_SiteID='XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX'

If you don’t mind filtering through a long list manually, you can just skip the 1st step and run this query -

SELECT * FROM UserInfo

This will get you a list of the usernames for that site.

2) Get your current SID from AD and convert to HEX

This is the part that took a long time to figure out. Fortunately, I came across a Visual Basic Script that will accomplish the task in one fell swoop.

You’ll want to copy the following text into NotePad and then save it as getSID.vbs. Note the portion in yellow below, you’ll need to replace it with the user’s AD context information. If you don’t know what the context is, use the dsquery command listed below the script to retrieve it.

GETSID.VBS

Option Explicit
Dim objUser, arrSid, strSidHex, objTrans, strUserDN, strSidDec
' Constants for the NameTranslate object.
Const ADS_NAME_INITTYPE_GC = 3
Const ADS_NAME_TYPE_1779 = 1
Const ADS_NAME_TYPE_SID_OR_SID_HISTORY_NAME = 12
' Bind to object. REPLACE WITH YOUR AD INFO 
Set objUser = GetObject("LDAP://cn=Test,ou=Sales,dc=MyDomain,dc=com")
' Retrieve SID and convert to hex string, then to decimal string.
arrSid = objUser.objectSid
strSidHex = OctetToHexStr(arrSid)
Wscript.Echo strSidHex
strSidDec = HexStrToDecStr(strSidHex)
Wscript.Echo strSidDec
' Use the NameTranslate object to convert objectSid to
' Distinguished Name.
Set objTrans = CreateObject("NameTranslate")
' Initialize NameTranslate by locating the Global Catalog.
objTrans.Init ADS_NAME_INITTYPE_GC, ""
' Use the Set method to specify the SID format of the object name.
objTrans.Set ADS_NAME_TYPE_SID_OR_SID_HISTORY_NAME, strSidDec
' Use the Get method to retrieve the Distinguished Name of the user object.
strUserDN = objTrans.Get(ADS_NAME_TYPE_1779)
Wscript.Echo strUserDN
Wscript.Quit
Function OctetToHexStr(arrbytOctet)
' Function to convert OctetString (byte array) to Hex string.
Dim k
OctetToHexStr = ""
For k = 1 To Lenb(arrbytOctet)
OctetToHexStr = OctetToHexStr _
& Right("0" & Hex(Ascb(Midb(arrbytOctet, k, 1))), 2)
Next
End Function
Function HexStrToDecStr(strSid)
' Function to convert hex Sid to decimal (SDDL) Sid.
Dim arrbytSid, lngTemp, j
ReDim arrbytSid(Len(strSid)/2 - 1)
For j = 0 To UBound(arrbytSid)
arrbytSid(j) = CInt("&H" & Mid(strSid, 2*j + 1, 2))
Next
HexStrToDecStr = "S-" & arrbytSid(0) & "-" _
& arrbytSid(1) & "-" & arrbytSid(8)
lngTemp = arrbytSid(15)
lngTemp = lngTemp * 256 + arrbytSid(14)
lngTemp = lngTemp * 256 + arrbytSid(13)
lngTemp = lngTemp * 256 + arrbytSid(12)
HexStrToDecStr = HexStrToDecStr & "-" & CStr(lngTemp)
lngTemp = arrbytSid(19)
lngTemp = lngTemp * 256 + arrbytSid(18)
lngTemp = lngTemp * 256 + arrbytSid(17)
lngTemp = lngTemp * 256 + arrbytSid(16)
HexStrToDecStr = HexStrToDecStr & "-" & CStr(lngTemp)
lngTemp = arrbytSid(23)
lngTemp = lngTemp * 256 + arrbytSid(22)
lngTemp = lngTemp * 256 + arrbytSid(21)
lngTemp = lngTemp * 256 + arrbytSid(20)
HexStrToDecStr = HexStrToDecStr & "-" & CStr(lngTemp)
lngTemp = arrbytSid(25)
lngTemp = lngTemp * 256 + arrbytSid(24)
HexStrToDecStr = HexStrToDecStr & "-" & CStr(lngTemp)
End Function

This script will give you pop-up messages with the HEX value of the userid you’ve searched for. Unfortunately, you can’t copy and paste. So, VERY CAREFULLY transcribe it into notepad and then copy to your clipboard.

Future upgrade: I’ll try and see if I can revise the script to export to a text file instead.

DSQuery:

This command line will have to be run from a server that is AD aware and has the DSQuery utility. Easiest option is to run it from your AD Controller. In this example, we return any userid that starts with the letters JE. So for me, where my userid is RYoder, I’d use RY*

dsquery * domainroot -filter "(&(objectCategory=Person)(objectClass=User)(sAMAccountName=je*))" -attr sAMAccountName distinguishedName ObjectSid -Limit 0

 

3) Update the database with the new HEX value

Run the following script against the Content database. NOTE: You’ll need to append the 0x to the front of the HEX string you found above.

Replace the X with the tp_ID of the user you are trying to update.

UPDATE UserInfo SET tp_SystemID = 0x010500000000000000000986BD9EA976E44036C3F5D3F04040000 WHERE tp_ID = 'X'

You should now have access to the content database. Note,  you’ll also want to run this update against any other content databases that are out there – usually one exists for each web application, otherwise you’ll get an Access Denied error when trying to hit the site.

 

That’s it! Enough for now, my brain hurts just typing this!

Troubleshooting SSRS With Dynamics GP

Installing and getting SQL Reporting Services to work with Dynamics GP is a fairly straightforward process, but there can be some tricks involved. The following link provides a good bit of information about configuring and troubleshooting the SQL Reporting Services Deployment Wizard.

SSRS with Dynamics GP FAQ

SQL Maintenance Plans – Rebuild or Reorganize?

Over time, customers may begin to notice a slowdown in the responsiveness of their Dynamics GP or CRM systems. One of the culprits can be SQL indexes that are no longer valid. It’s like telling your SQL Server to drive to California and then handing it a 20 year old road map – it’s going to take a little longer than it should!

This is where maintenance plans come in. In a perfect world, each of our customers would either hire a SQL DBA to constantly monitor the server, tweaking and optimizing or else pay us to do this. Yeah…right. This is where Maintenance Plans come in – ways of automating and scheduling those cleanup tasks so that the system doesn’t deteriorate over time.

Paul Thurrott of SuperSite for Windows posits that the brain is like a stack. Put a new memory in at the top and an old memory has to drop out the bottom. As I get older, I’m starting to believe this more and more! Memorize that esoteric SQL command and out drops that memory of Skippy, my pet turtle. (Did I have a turtle? I seem to remember something like that…)

That’s where this blog post comes in. I need to document those common tasks that I use every time I setup a new maintenance plan and there’s no reason you shouldn’t benefit as well.

Nightly sub plan:

• Full backup of all user databases
• Delete any existing backups older than 1 week (depending on disk space)
• Reorganize Indexes
• Update Statistics

Daily sub plan (every hour):

• Transaction log backup of all user databases
• Delete any existing backups older than 1 week (depending on disk space)

Weekly sub plan:

• DB Integrity Check
• Full backup of all databases (including system)
• Rebuild Indexes
• Update Statistics
• Delete history older than 8 weeks
• Shrink Databases

According to Pinalkumar Dave, indexes should be rebuilt when greater than 40% fragmented. Between 10% and 40%, use reorganize.

Setup Bing Wallpaper Desktop Slideshow

For those running Windows 7, you may have played around with the desktop slideshow. Did you know you can have those images piped in from an RSS feed. I’m thinking Flickr or maybe even those cool images Bing uses on it’s front page.

Mahendra Palsule at makeuseof.com has posted an article on how to do this. If you want to use another source, you could edit the file she provides and throw in your own source!

Find the article here.

Creating Report Subscriptions in Microsoft Dynamics CRM 4

Something that gets asked a lot is “Why can’t the data come to me? Why do I have to go somewhere and get it?”

There are ways to make that happen. CRM MVP Donna Edwards has posted an article on the Dynamics CRM Team Blog about exactly that topic. Find the article here - http://tinyurl.com/n5dsy7

Plus, it utilizes one of my new favorite tools – SQL Reporting Services.