Named SQL Instance stops User Profile Service

Aaaarrrrggghhh! I really did think I’d experienced them all but today I’ve witnessed yet another error which was causing the FIM Synchronization Service to fail.

Having re-provisioned the UPS Service Application to solve another issue, when I clicked on ‘Start’ the status would remain on ‘Starting’ for around 10 minutes before eventually bombing out and displaying ‘stopped’.

Fortunately, the ULS provided me with a starting point – this is what I was witnessing:

 

ILMPostSetupConfiguration: ILM Configuration: Validating installation of SQL Service.

ILM Configuration: Error ‘ERR_SERVICE_NOT_INSTALLED’.

ERROR  ILMPostSetupConfiguration: ILM Configuration: Validating installation of SQL Service FAILED

 

After referencing a number of blogs, it became apparent that FIM was failing to recognise that the User Profile SQL databases resided on a Named SQL instance (i.e SQLServer\development), and was trying to refer to the default instance. The workaround was to implement a SQL alias.

Firstly, open DNS and create a new CNAME resolving to the FQDN of your SQL Server:

‘Alias: ‘SharePointSQL‘ (this is your SQL alias which will define the call to SQL)

FQDN: ‘SharePointSQL.mydomain

IP address: ‘SQLServer.mydomain (the DNS A record of your SQL Server)

 

Then using the CName, create a SQL Alias.

From the Run Command, type ‘Cliconfg’

Choose ‘TCP/IP’

Server alias: ‘SharePointSQL‘ (CName you created earlier)

Server name: SQLServer\development (name of your SQL Server and instance)

With this is place, you should now be able to successfully run an import.

This SharePoint 2010 Farm is patched to Feb 2012 CU

Advertisements

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