We are no longer able to login to ODI as a result of changing the SQL Server that housed the database for our Planning application. Working with our dba we changed the connection data in the snp_connect table to reflect the new name of the SQL Server but it still will not allow us to connect via ODI. What more do we need to do to get back the connection?
Why did your connection stop working? you said it is because you changed your sql server, changed location? In theory all you need to do is edit your connection details when you go to log into the topology manager.
The database was moved to a new SQL Server with a whole new name.
I did notice though that in one of the tables(snp_connect) in the db_snmp database that there is a list of rows that show the connection names, server name, user_name and encryted password. I'm guessing that these are used to connect to the planning sql database. In the server name field it still showed the old server name. So I asked our dba to correct that to the new sql server name thinking that would fix the ODI connection problem. It did not fix the ODI issue. Is there another place where that might be a reference to the old sql server name that is causing the ODI problem? Everthing else has been corrected to point to the new sql server. Does ODI have to be reconfigured somehow?
Is there no way to connect to your original master repository using the topology manager because the best way to move between sql servers is to export the repository from within the topology manager. What is the error when you connect to your original master repository.
You shouldn't really have to start hacking the tables.
I think our dba moved all the db's to a new SQL Server with a new name and did not consider all the ramifications. We have been fighting that ever since. We have all the other functionality of the Planning application working now except for the ODI piece.
I'm really not that familiar with how the databases work together. I assumed the ODI sql database, db_snmp, was just a storage db for ODI properties, connection strings etc for the actual Planning databases that you see and work with once you are logged into ODI because there is really not much in the db_snmp except for those things I mentioned...properties etc.
The error I get is:
com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user 'snpw'.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(Unknown Source)
at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onEOF(Unknown Source)
at com.microsoft.sqlserver.jdbc.TDSParser.parse(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.access$000(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(Unknown Source)
at com.microsoft.sqlserver.jdbc.TDSCommand.execute(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.loginWithoutFailover(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(Unknown Source)
at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(Unknown Source)
I am assuming you are trying to log into the designer there because that looks like an account to connect to the work repository, what happens when you try and connect to the topology manager, login failed for user snpm ?
The error you get is the same as you get when you enter the wrong password for the account.
Yes, I am trying to login to the designer. I have not tried to login to the topology manager. I don't ever login there. I'm not sure we ever use that. There is no connection setup there from my machine so I don't know how to get in. Would those connection properties be the same as logging into the designer?
Well the first table you pointed out was from the master repository which the topology manager uses.
What I suggest is getting the snpm and snpw passwords for sql server reset.
Then you should be able to edit the connection and update the password before you log into the topology manager and designer (click the edit button when you the pop up window appears when you first open the topology manager or designer.
So the repository refers to the db_snpm database and within that, the snp_connect table holds the pw and login properties for getting to the other databases that contain the Planning metadata tables...correct? I don't think anything changed within any of the data in SQL Server. It was just backed up, moved to the new server and restored. But you think the passwords were somehow corrupted? We will try that and see if it works.
You will usually have one master repository and a work repository (you can have different combinations but this is just an example)
The master repository holds most of the information you see when you log into the topology manager.
The work repository relates more to what you see in the designer.
Now by looking at the information you have given it looks your master repository is db_snpm with a user account of snpm and your work repository is uing db_spnw with a user account of snpw.
It looks like you need to go into sql server and change the password for accounts snpm and snpw and then edit the connections for logging into the topology manager and designer using the new passwords. You don't need to change any tables to edit the passwords, just click the edit button when you start up the topology manager and the popup window appears.
I am assuming you can still access your original SQL server because the best method would be to login to the topology manager export master repository, login to the designer then export the work repository, then import the outputs into your other sql server environment.
If you can't log into the topology manager and need to update the password you just edit the connection details.
If you definitely know what the password needs to be you don't have to change anything in SQL server.
If you can get into the topology manager, you can change the passwords for physical connections to technologies.
OK, I was able to login to the topology mgr and saw the WORKREP1 repository. I even tested the connection in there after changing the old sql server name to the new one. But when I try to connect to it with the Designer it gives me an error -> com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name 'SNP_LOC_REPW'. And when I go back into the Topology Mgr it won't let me edit the WORKREP1. Do you know what this error means?