SQL Server: Set up FogBugz database on a separate non-trusted server

In situations where the access permissions in your environment are such that Setup will not be able to log in to your SQL Server and/or grant the FogBugz user account permissions in your database:

  1. Run Setup on the web server, choosing Access as the database. (You can just delete the FogBugz/Accessories/fogbugz.mdb file later.)
  2. Install just one of your license orders and log in as Administrator. Don't bother with any other settings right now.
  3. Find the FogBugz/Accessories/all.sql file. This is a SQL script that creates a skeletal FogBugz database with just one table named Version, the other tables will be created later.
  4. Open SQL Server Enterprise Manager. Expand down to Databases, right click on Databases and select New Database. Name it fogbugz. The defaults are all fine.
  5. Left click on your new fogbugz database to select it, go to Tools at the very top, and choose Query Analyzer. Go to File > Open and open the all.sql file. Run it (you can hit F5 to run it).
  6. Back in Ent. Mgr. go to Security > Logins and create a login for FogBugz to use. You must set it to use SQL Server Authentication, not Windows Auth.  In SQL Server 2005, make sure to uncheck the "Enforce password expiration" and "User must change password at next login" options.  Make this login db_owner for the FogBugz database.
  7. Back in FogBugz in the browser, click on Site, top right. Scroll down about half way, and click the ODBC tab for Database. Delete whatever is in that field and paste the connection string below, but replace the parts in capital letters with your own information:   

    Provider=sqloledb;Data Source=YOURSQLSERVER;Initial Catalog=FogBugz;User Id=YOURLOGIN;Password=YOURPASSWORD;

    "Data Source" is the name of the server that is running SQL Server, and "Initial Catalog" is the name of the FogBugz database within that SQL Server.
  8. Click OK on the Site page and the next page you see should give you link that lets you upgrade the database, you need to click that link to proceed. FogBugz will create the rest of the database when you click that link.
  9. Lastly, run regedit on the web server and go to:
    HKLM > Software > Fog Creek Software > FogBugz > C:\\path\to\your\FogBugz
    Set fFullTextSearchOK to 1. This turns full text search on in FogBugz. (Full text search is disabled when you use Access as your database.)