Ran into this old chestnut just now and thought that it was worth re-visiting the outcome of an old forum post on the subject.
Before I get to the point, by way of background I always start out the installation process with a quick sanity check:
- Create a UDL file on the FIM Sync server desktop
- Configure the UDL file to connect to the SQL instance you are targeting
- Test for connectivity success
The above will ensure you can at least get to “first base” with SQL connectivity, negotiating firewall and network issues.
When installing the FIM Sync service any number of connectivity issues can prevent you progressing through the installer wizard. For instance, if you’ve got a remote SQL database and you’ve forgotten to install the appropriate SQL Native Client then you will be stuck on the page configuring the SQL connection.
Once you get past this problem it’s generally onto the next … the configuration of the FIM Sync service account. The full text of the error you might run into is this:
The service account cannot access SQL server. Ensure that the server is accesible, the service account is not a local account being used with a remote SQL server, and that the account doesn’t already have a SQL login.
The error text can be quite misleading – because (as was the case with the linked thread) the problem can be the installer access itself. The installer account (not the service account itself) MUST be a member of the SQL sysadmin role to have any hope of progressing beyond this point. Generally you will want to (or be asked to!) remove this access after a successful install.
Thanks to those who bother contributing answers to the TechNet forums – they are incredible time savers, often long after the threads are closed.