Question Re: SQL Data store creation in FIM in Additional Q&A

SQL Data store creation in FIM

Former Member — Wed, 25 Nov 2015 06:34:54 GMT

Hello All,

We are unable to create a SQL data store in FIM, even though all the required details are provided it is failing to test the connection.

Could you please let us know if we need to do any additional settings to create a SQL connection.

Please help us to create the sql connection.

Regards

Srikianth

Re: SQL Data store creation in FIM

sirine_belhadjrhouma — Wed, 25 Nov 2015 08:55:03 GMT

Hi Srikianth,

You can refer to the following article:

1797203 - Error when creating a FIM datastore of type "SQL Database"
: "Connection '****' is invalid"

Regards

Sirine.

Re: SQL Data store creation in FIM

Marc_Kuipers — Wed, 25 Nov 2015 15:31:01 GMT

Hello Srikanth

I noticed you had not ticked the "Use windows authentication", so the note 1797203 may not be applicable to your situation.

You are specifying the 1433 port in the connection. However, this is normally the default port, see below

If you connect to the server:port (even in MS SQL Management Studio), it throws an error

However, without the port specified it works fine

So, using the same entries for FIM, the data-source can be successfully created.

Hope this helps.

Marc

Re: SQL Data store creation in FIM

Former Member — Wed, 25 Nov 2015 16:49:52 GMT

Hi Marc,

Thanks for your reply.

We are creating a data store to connect to a source system which is SQL server

Our target is BPC 10.0 in MS.

Do we need to maintian any settings in the server \.

EX: For Oracle we used to maintain TNS names in server.

I have tried without the port but still getting the same error.Please let us know if any additional settings are to be done in server.

Regards

Srikanth

Re: SQL Data store creation in FIM

Marc_Kuipers — Wed, 25 Nov 2015 17:32:55 GMT

Hello Srikanth,

You need the SQL Server native client on the FIM server to make connection to the DB server

A simple test is this;

- on the FIM server, run odbcad32.exe

- define a system DNS

- select the SQL server native client

- provide all further details (server, user/pwd, database)

- test the connection - this should work. If it does not, then FIM can also not connect

Thanks

Marc

Re: SQL Data store creation in FIM

Former Member — Thu, 26 Nov 2015 07:07:53 GMT

Hi Marc,

Could you please confirm that SQL management studio should be installed in FIM server in order to create a SQL server data store connection to connect to Source system.

Please let us know if any settings that we need to do to connect to sql server.

we have sql server managemnet studio(64 bit) installed on BPC server but not in FIM server.

If we install SQL server management studio now in FIM server, does it have any impact on the existing jobs and data stores.

Regards

Srikanth

Re: SQL Data store creation in FIM

Marc_Kuipers — Thu, 26 Nov 2015 13:06:25 GMT

Hello Srikanth

You don't need the whole Management Studio, but you need the SQL client

Did you try running "odbcad32.exe" (this is a standard Windows program, should be available on any system) and check if you have SQL server as driver?

If not, install the SQL native client

Marc

Re: SQL Data store creation in FIM

Former Member — Thu, 26 Nov 2015 14:32:36 GMT

Dear Marc,

Thank you for the update.

I have ran "odbcad32.exe" and verified that SQL server is available, but SQL Native client is not available.

Please let me know is it mandatory to have sql native client.

We haven't changed any configuration from FIM server , Our IT Team have resolved the issue and we are able to create a datastore to connect to source system SQL server.