none
Connect to SQL 2000 server with FIM SQL MA RRS feed

  • Question

  • I am not able to connect to a SQL server 2000 database from the FIM 2010 R2 SP1 MA. I installed SQL Server Management Studio on the FIM server and can connect to the database with no problem, so it's not a firewall or rights issue. I even tried altering the .Net Runtime Startup Configuration in the miiserver.exe.config file.

    Any ideas?

    Thanks,

    Mark


    Mark Creekmore - BlueVault Software http://www.bluevaultsoftware.com

    Monday, July 15, 2013 5:40 PM

All replies

  • Maybe it's just the port?  If you are connecting to a server running SQL that is using a non default port, you must create a SQL alias for Setup to be able to contact the SQL server.

    To create a SQL alias for Setup to be able to contact the SQL server

    1. Start the SQL Server Configuration Manager.
    2. Navigate to SQL Native Client 10.0 Configuration/Aliases.
    3. Create a new alias with your server information (including port).

    Once your alias is created, try connecting via this alias and see how you go.  You can test the alias by creating a new text file and changing the file extension from TXT to UDL, then double-clicking to open the Data Link dialog and specifying your alias name - then test to see if you can see your database in the drop-down list.


    Bob Bradley (FIMBob @ TheFIMTeam.com) ... now using Event Broker 3.0 for just-in-time delivery of FIM 2010 policy via the sync engine, and continuous compliance for FIM

    Tuesday, July 16, 2013 3:13 PM
  • Thanks for the reply Bob. The SQL 2000 server is listening on port 1433. I have no problem connecting to it from SQL Server 2012 Management Studio (installed on the FIM Sync Server). the issue seems to be that the SQL Native Client 11 (SQL 2012) dropped support for SQL 2000. So when FIM gained support for SQL 2012, it lost support for SQL 2000. I installed the 2008 R2 native client on the FIM SQL server and was able to create a System DSN (using the older client) and SQL linked server. This allowed me to create a view into the database that I can read with FIM, but I can't write to this view so it only fixes part of the issue.

    Thanks,

    Mark


    Mark Creekmore - BlueVault Software http://www.bluevaultsoftware.com

    Tuesday, July 16, 2013 3:30 PM
  • I was going to suggest the SQL linked server idea next :).  I wasn't aware of the dropped support - good to know but not very helpful at all for you.  I will ask the FIM PG if they were aware of this and what their advice would be (other than of course to upgrade the SQL 2000 database! - is this not an option - I guess there is some application which only supports 2000 right?).

    Since I despise the SQL MA with a passion, I would propose either writing an ECMA or using a PowerShell ECMA as an alternative in the meantime.  Incidentally I am wondering why a linked server has to be read-only (I thought they could be read/write - see this article)?


    Bob Bradley (FIMBob @ TheFIMTeam.com) ... now using Event Broker 3.0 for just-in-time delivery of FIM 2010 policy via the sync engine, and continuous compliance for FIM

    • Proposed as answer by UNIFYBobMVP Thursday, August 13, 2015 12:00 PM
    Tuesday, July 16, 2013 3:42 PM
  • I should explain - you could theoretically use the SQL MA to work with a local SQL database, and use SQL triggers to maintain the corresponding SQL2000 database tables in sync or "instead of" - totally over the top, but achievable in theory.

    Bob Bradley (FIMBob @ TheFIMTeam.com) ... now using Event Broker 3.0 for just-in-time delivery of FIM 2010 policy via the sync engine, and continuous compliance for FIM


    • Edited by UNIFYBobMVP Tuesday, July 16, 2013 3:45 PM instead of idea
    Tuesday, July 16, 2013 3:45 PM
  • The client has no desire to upgrade to a newer version of SQL. They would only need to go to SQL 2005. I've used SQL linked servers in a read / write capacity in the past. There just seems to be something different about this configuration (from 2012 to 2000) that errors when I try to write to the view. I'm sure an ECMA would work if I had the time / funding to write one. Maybe I just need to stand my ground better with the client. :-)

    The SQL trigger idea is something that I've been considering. I've setup SQL triggers in the past to do delta syncs with SQL DBs.

    I'll keep everyone posted if I come up with a solution.

    Thanks,

    Mark


    Mark Creekmore - BlueVault Software http://www.bluevaultsoftware.com

    Tuesday, July 16, 2013 3:58 PM
  • We have a working solution. I would rather have direct DB access, but this gets the job done. We have a stored procedure that makes a local copy of the DBs on the SQL 2012 server. FIM syncs with the local DBs. SQL triggers keep track of all of the changes. Then another stored procedure updates the SQL 2000 DBs.

    Luckily, they only need to sync with these DBs once per day.

    Thanks,

    Mark


    Mark Creekmore - BlueVault Software http://www.bluevaultsoftware.com

    Thursday, July 18, 2013 1:15 PM