Tim Elhajj

Off the Microsoft stack!

To Install TFS, What SQL Server Permissions Do I Need?

7 Comments

G-d

The person installing TFS needs to be a member of sysadmin fixed server role on the SQL Server.

You may not realize it, but if you’re a member of sysadmin on SQL Server, there is precious little you cannot do on the SQL Server. You are, essentially, a god. How did you get these permissions? If you’re using SQL Server Express, TFS setup gave them to you during installation. If you installed SQL Server manually, you most likely added yourself to this role as you were clicking through the installation wizard. Of course, the SQL Server installation wizard doesn’t tell you any of this. It asks something completely innocuous like “Add Current User?” But once you click that Add button, you have real power—at least on that SQL Server.

This is why many easy going DBAs balk at the mere thought of hosting a TFS database. I recently read an email where one DBA joked that he had to get out the vinegar and wire brush to clean up his SQL server after a TFS install. DBAs are sensitive like that.

Why does TFS need so much permissions? Good question. You can go here for an explanation. The bottom line, though, is that the person installing TFS needs to be a member of the sysadmin fixed server role on the SQL Server. If you installed SQL Server yourself, you most likely have these permissions. If you have to ask a DBA in your organization for them, be prepared to convince that DBA that TFS means the SQL Server no harm.

It’s just the only way to make it work.

Author: Tim Elhajj

Tim is probably walking his dog.

7 thoughts on “To Install TFS, What SQL Server Permissions Do I Need?

  1. Pingback: May 4, 2011 – Visual Studio and TFS Daily | Learn TFS

  2. Hey Tim,
    As always you are too good!
    So my understanding after reading your post for my installation model is:
    1. Set up 3 service accounts say TFSService, TFSReports and TFSInstall. You find more than enough details about first two but very rare about the last one which is TFSInstall service account. The TFSInstall service account would be used for installing TFS on AT.
    2. TFSService would have SA role on DT and Farm Admin on my shared SharePoint installation (as per MSDN link)
    3. There are some requirements for TFSReports as well which are pretty straightforward.
    4. But the most important is security permissions for TFSInstall account. And here your post helped me. So this account should have local admin rights on AT, DT and SharePoint box. Also should have SA role on DT.

    Please confirm my understanding.

    Thanks,
    Sam

  3. Hi Sam, you’re understanding is good for the service accounts, but there is one small problem. I don’t consider TFSinstall to be a service account.

    TFSinstall is just YOU.

    TFSinstall is your account.

    TFSinstall is Domain\SAM (or whatever your domain account name is — mine is Redmond\TimEl).

    You (Sam) need to be in the local admin group to install TFS, SQL Server, or pretty much any piece of software. But you don’t have to create a special account to install any of these products. You just need to make sure your account (or the one you’re going to use) is in the local admin group. You (or any account you add to the local admin group) will then have the ability to install and configure software products.

    Good luck!

  4. I understand your point Tim. The TFSInstall need not be a service account and it could be mine but the kind of policies our organization follow are different and I’m bound to take service account route. Thanks for clarifying though.

    And for installing TFS with SQL on separate box, I need to give local admin rights to this TFSInstall account on both boxes. But, does this TFSInstall account need to have “SA” role on SQL box as well?

    Thanks for your help.
    Sam

  5. Sam, very interesting. I can’t be sure if we’re talking past one another or not, but I’m saying the account you use to install TFS isn’t one of the service accounts for running TFS, SQL Server or the SharePoint site.

    I’d like to understand more about your organization’s requirements that are driving the accounts you use to install TFS. Would you be willing to share the rules you have to follow with me? I have always just described the identity people should use to install TFS as “YOU,” thinking that was pretty clear. If it’s not clear, I may have to rethink my strategy. Of course, if you’d rather not post your orgs rules and requirements here, I’m happy to have a dialog via email: tim.elhajj @ Microsoft dot com.

    As for your question about permissions, it depends on whether you installed SQL Server with the same account your using to install TFS. If you used an account called TFSinstall, for example, to install SQL Server, and then used that same account to install TFS (even if it’s on a different machine), you should be fine. If you did not use the TFSinstall account to install SQL Server, then you’d have to make the TFSinstall account a member of sysadmin Server role on the SQL Server before you run the TFS advanced configuration wizard (as TFSinstall).

    Hopefully that makes sense. This is all documented here:

    http://msdn.microsoft.com/en-us/library/dd631919.aspx

  6. Cool! I tried the installation based on your suggestions and e-book steps and it succeeded.
    To conclude, TFSInstall should have local admin rights on AT and DT and also have SA role on DT. TFSService and TFSReports need not have SA role or local admin rights on either of the boxes i.e. AT and DT.

    Now, moving on to next steps of installing Build services:
    Tim, I need your expert guidance here as well.

    As you know my model is 1 AT server, 1 DT server, 1 shared SharePoint server and 1 build server.
    Also, I am sure I would end up creating multiple Team Project Collections based on various organizational needs.
    Now the concern which bothers me is per MSDN link http://msdn.microsoft.com/library/dd793166.aspx

    My understanding after reading this through is that of constraints/limitations of having multiple Build Controllers on single build server.

    Looks like I need to buy extra Build servers for each TFS project collection I create because of the constraints/limitations.

    Is my assumption and understanding correct?
    Also, can you please guide in best possible way which is also supported by MS.

    Thanks for your help.
    Sam

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s