1. UNIFYBroker Forum
  2. Questions
0
Answered

Investigate the use of SQL Server alias use for database connection string.

Ross Currie 13 years ago • updated by anonymous 8 years ago • 7

This one seems very simple on the surface, but none of the usual configuration steps are working.

When I try to start the Identity Broker service, the service fails to start with the following error message:

Service cannot be started. Unify.Framework.ConnectorEngineConfigurationException: Connector Engine unable to start due to bad database connection.
at Unify.Framework.ConnectorEngine..ctor(IAdoNetDataControlFactory dataControlFactory, INotificationMessageService messageService, XElement xmlConnectorConfiguration, IConnectorEntityPartitionContextGenerator entityPartitionUpdateableContextFactory, IScheduleCollection scheduler, IAdoNetDataControlGenerator dataControlGenerator)
at Unify.Framework.ConnectorEnginePlugInFactory.CreateComponent(IUnifyEnginePlugInFactoryInformation factoryInformation)
at Unify.Framework.DependencyPlugInGenerator`4..ctor(ICollection`1 plugInGenerator, IPlugInFactory`2 factoryInformationFactory)
at Unify.Framework.UnifyEngine..ctor(IEnumerable`1 additionalPlugInFactories, DirectoryInfo executingAssemblyLocation)
at Unify.Service.IdentityBrokerServiceEngine..ctor(DirectoryInfo assemblyExcutionPath)
at Unify.Service.IdentityBrokerService.OnStart(String[] args)
at System.ServiceProcess....

Data Engine file:

<?xml version="1.0" encoding="utf-8" ?>
<DataEngine>
<dataRepository>
<dataConnection name="sql" repository="Unify.IdentityBroker" connectionString="Data Source=SQLSERVER;Initial Catalog=Unify.FIMIdentityBroker;Integrated Security=True" />
</dataRepository>
</DataEngine>

Connector Configuration:

<?xml version="1.0" encoding="utf-8" ?>
<ConnectorEngine>
<dataConnection name="repository" repository="Unify.IdentityBroker" />
<connectorconfigurations>
</connectorconfigurations>
</ConnectorEngine>

Steps taken:

  • Identity Broker service account is set to a domain account
  • Domain account is local administrator on IdB server
  • Confirmed domain account has owner access to Unify.FIMIdentityBroker
  • Distributed Transaction Coordinator is configured on the SQL Server and the IdB server
  • SQLServer is a valid alias (connects fine in SQL Server Management Studio), configured under SQL Server Configuration Manager
  • Have another 32-bit server in same environment running Identity Broker, connecting to a different database (Unify.IdentityBroker), which runs fine.
  • Have tried re-creating the database, re-downloading the IdB service, changing the database name, changing the IdB service account

Is there anything else worth trying here?


idb89.png
Ross Currie 13 years ago

After reviewing this issue: https://unifysolutions.jira.com/browse/ACGCEO-29, I tried a few things, specifically Shane's 3 suggestions:

1 - The SQL Server TCP/IP was not enabled for 4 and IPV6. Thus enabled them.
2 - SQL Server Browser was not running
3 - The hostname for the ConnectionString must be a fully qualify hostname. (ACG CEO has some kind of hostname resolution, although we can ping and nslookup using hostname only just fine.)

The first two didn't seem to affect it, and I assumed the 3rd wouldn't be an issue... the alias is confirmed to work.

However, on a whim I changed the connection string to:
<dataConnection name="sql" repository="Unify.IdentityBroker" connectionString="Data Source=dvdb500;Initial Catalog=Unify.FIMIdentityBroker;Integrated Security=True" />

in this case, dvdb500 is the name of the SQL server we're connecting to.

The service then starts.

So, it appears that for whatever reason, IdB on this platform is unable to resolve the SQLServer alias.

This should be investigated further as it will cause a fair bit of annoyance when doing dev->test->prod deployments - generally we can use the SQL Server alias to ensure that we can just move the IdB configs over without modification and the alias is picked up automatically.

Adam van Vliet 13 years ago

I have renamed this issue to be more appropriate, now that you have diagnosed the issue.

Ross,

Thanks for reporting this issue.

Have you successfully used aliases before in IdB? What version of SQL Server were you targeting for this environment?

Shane Day (Chief Technology Officer) 13 years ago

I will add to the issue that it's most likely it's a ADO.NET issue, as the connection string is just a normal ADO.NET issue. The only resolution may be to document the issue, but it's definitely worth looking into the possibility of a resolution in the product.

Ross Currie 13 years ago

Sorry, didn't realise I hadn't replied to this one.

Yes, I frequently use alises for the IdB database, as it allows me to migrate configurations between different environments (dev/test/prod) without having to make as many changes.

SQL Server version is "Microsoft SQL Server Standard Edition (64-bit)", 2008 R2

This particular issue is identified in the new FIM environment we've configured at BCE. We are connecting to the same database using an alias in the ILM environment, which is running on Windows 2003 SP1 (32bit). Could be an x64 issue?

Adam van Vliet 13 years ago

Hi Ross,

Did you create the alias on the client pc under both the 32 and 64 bit configuration?

The MSDN link is not too much help:
http://msdn.microsoft.com/en-us/library/ms190445.aspx

The following link explains it a lot better:
http://geekswithblogs.net/twickers/archive/2009/12/08/136830.aspx

Ross Currie 13 years ago

Wow, that is... random

Anyway, I think the picture sums it up nicely:

Ross Currie 13 years ago

Thanks Adam

Customer support service by UserEcho