So as you would expect, I as a consultant do not have god-like access to things in production like I do in the dev and test environments. So occasionally I get tripped up on access rights, and when it comes to TFS, well, they could do a much better job of listing out all the places where you do and do not need Admin rights, sysadmin rights, farm admin rights… Well, it’s all out there between the Ranger Guidance, best practices documents, install docs and MSDN documentation but you have to do a LOT of cross referencing to get it all. And sure, ideally anyone who is a TFS admin would be able to just ask nice and smile and get all those rights, but this is the real world and many large companies are PARANOID about handing out access like that to production. I had to fight to get the minimal rights documented in the TFS guidance, let alone anything extra.
While upgrading TFS 2010 to 2012 at this current client, I am stopped dead in my tracks at least a few times a week, sometimes a few times a day, by “Access Denied”. My most recent one was extra tricky because it involved a Power Tool and as you know, those are often not documented very well. So, on to my story… I was setting up a Backup Plan on TFS 2010 using the nifty Power Tools feature (see screen below) from the Admin console. I login to the TFS application tier with my account, a TFS Admin user. I know that my account has sysadmin rights on SQL because I am a TFS Admin, and when it comes time to providing the account to run the backup plan under I provided the TFSService account which I know has Administrator and sysadmin rights on the data tier server:
So between those two accounts I would think everything was OK. I don’t know for sure, but if the Backup Plan is running as the TFSService account the way it is setup here, well that account is king of the world so everything should “just work”. And yet:
So to hopefully make this something that comes up when someone else does a search on this message, here is what I saw - “Error [ Backup Plan Verifications ] The current username failed to retrieve MSSQL Server service account. Please make sure you have permissions to retrieve this information.”
WTH?! And when I opened up the error log the first error I encountered was:
TFS upgrade xp_regread() returned error 5, 'Access is denied.' xp_regread() returned error 5, 'Access is denied.'
So the DBA goes off and starts researching what xp_regread() does, and tried to figure out why this isn’t an issue in our dev and test environments given that everything was setup the same, and I start digging through forums. Finally I find one sad and lonely little post on the MSDN forums related to the issue that recommends 1) logging in as a TFS Admin user (OK, I’m with you) and 2) “ensure that the user who perform this Backup Plan have required permission in SQL Server”. Wait, what? Be more specific please. What *ARE* the required permissions?? This happens all the time. Don’t tell me to “make sure you have appropriate permissions” without clarifying what those are. Otherwise, well, duh! I *think* I have the right permissions but clearly I am mistaken.
I dig through the Ranger Guidance which as far as I can tell is the only place this tool is documented. It doesn’t say the person CREATING the backup plan has to be an admin on SQL, and it IMPLIES the account specified to run the job has to be an ADMINISTRATOR but only because the example specified a Administrator account. Here, right from the guidance:
But even that doesn’t necessarily imply a SQL admin, and nowhere in the doc does it say what rights either account (logged in user or “Account”) should have. I just went back and read it AGAIN, does not say anything IRT rights of either of those users in the Guidance. I suppose if you knew what it was doing behind the scenes you could infer the rights needed from the MSDN docs (I found this later). I made an educated guess that because in dev and test I am a server Administrator on the DT, and the Backup worked just fine there, that me being a SQL Server Admin must be a requirement. So I logged back into my production TFS AT with another account that I knew was admin on every server in the TFS implementation (I know, I know), and the backup plan was created just fine. .
Our DBA does NOT like making TFS admin accounts SQL Administrators, but if I can show him explicit rules that say YOU CANNOT DO YOUR JOB AS A TFS ADMIN WITHOUT IT, he will do it. So please Microsoft, don’t make it so darn difficult to divine what rights all of the accounts need for the various tasks the user will do. Particularly the Power Tools which make people nervous anyway.