The (#FIM2010) service account cannot access SQL Server …

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:

  1. Create a UDL file on the FIM Sync server desktop
  2. Configure the UDL file to connect to the SQL instance you are targeting
  3. 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.

Advertisements

About bobbradley1967

Microsoft IAM MVP and Solutions Architect (MCTS, MCP) - FIM/ILM/MIIS Specialist, with 20 years SQL database ( OLAP) and MS.Net applications development/SI background, in particular on the SharePoint platform
This entry was posted in Uncategorized. Bookmark the permalink.

3 Responses to The (#FIM2010) service account cannot access SQL Server …

  1. Kamlesh says:

    Thanks it worked for me.

    “The error text can be quite misleading – because (as was the case with the linked thread) the problem can be the installer access itself. ”

    This is very useful

  2. Kamlesh says:

    Actually these lines

    “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. ”

    are useful

  3. cwapshere says:

    Ran into an unexpected one the other day – the customer had in fact dutifully removed sysadmin from the installer account in Prod (without having done that in Dev or Test) and all the SQL Agent jobs that run against the FIMService database stopped running. It turns out they are all installed to run as the installer account. So while it’s ok to remove sysadmin you still have to give the installer account sufficient access to the FIMService database to run these jobs – such as db_owner. I was surprised I hadn’t noticed this before – and it seems completely wrong – if anything is going to be hard-coded it should be the service account. (This was a fresh installed of FIM 2010 R2 patched to the latest hotfix.)

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