Troubleshooting
Problem
Usually after a database (DB) crash and a restore, when trying yo log on to an i2 iBase database, the following error message is displayed : "The security connection file is not valid with the security database you are attempting to logon to. If you restored a security datbase from an earlier version of iBase, then you also need to restore the connection file." And connecting to i2 iBase is not possible.
Cause
The Security in i2 iBase is based on an IDS file, which is an Access file.
This file stores several information, like the list of users and their rights, but also some internal data such as, the iBase version, the Unicode level and more.
When this security file is "upsized" (or migrated) to SQL Server, most of the data in the IDS file is transferred to the SQL server DB.
When trying to connect to an IDS file that has been upsized to SQL, i2 iBase checks that the internal parameters that are still in the IDS file are matching the sma internal parameters that are in the SQL Server DB.
If they don't match, then i2 iBase displays the error message above.
It is a "security message", as it prevents the internal parameters in the IDS and in the SQL DB to be out of sync.
The same applies with the IDB file and the corresponding SQL DB. The internal parameters must be in sync, otherwise i2 iBase will prevent the DB to open.
This is most probably happening after a crash of some sort, and a restore of a previous SQL backup.
Environment
IBM i2 iBase.
IDS security file has been upsized to SQL Server.
IDB file has also been upsized to SQL.
Diagnosing The Problem
The steps are to be able to compare the values of the table "_Configuration_Text" in both the local Access DBS (IDB and IDS files).
1) In SQL Server Management Studio (SSMS), open the Security Database (the name should end with "_SEC")
2) Run the following SQL Script :
Select * from _Configuration_Text
It should look like this (the values, of course, might be different) :
3) Copy all the result, and save them in a Text file. Let's call it Security_SQL_Configuration.txt
4) You must look more specifically at the following values :
- SYS:CodePage
- SYS:SQLServer:Unicode
- SYS:Version
5) Still in SSMS, open the main iBase DB (the name does not end with _LOG or _SEC).
6) Perform the same SQL query :
Select * from _Configuration_Text
It should look like this (the values, of course, might be different) :
Again, you must look more specifically at the following values :
- SYS:CodePage
- SYS:SQLServer:Unicode
- SYS:Version
7) Now, you must open the IDS and IDB files in Access.
WARNING : These 2 files are protected by an access password that is normally accessible from iBase Designer, in the menu "Tools - Function Availability - Options", and then in the Advanced tab.
If you DON'T have this password, send us (the IBM i2 Support team) the ids file by mail, and we'll send you the Password. Note that the IDS file does not hold any sensible information like the user names.
Once you have this password, then open the IDS and IDB files in Access, and look at the _Configuration_Text table.
Save the result into a text file. Let's call it Security_IDS_Configuration.txt
8) You must look more specifically at the following values :
- SYS:CodePage
- SYS:SQLServer:Unicode
- SYS:Version
9) Compare these values with the same values from the SQL database.
10) There will most probably be differences.
For example, you might have :
In the IDS file :
SYS:CodePage = 65001
SYS:SQLServer:Unicode = 1
In the SQL DB :
SYS:CodePage = 1252
SYS:SQLServer:Unicode = 0
This means that the SQL Server is in NON-UNICODE version, while the IDS file is in UNICODE.
This discrepency is causing the problem.
If there are no differences in the IDS file, then check in the IDB file
Resolving The Problem
Once you have found the discrepancies, you must correct them in the IDS or IDB file, NOT in the SQL DB !
In the example above :
In the IDS file :
SYS:CodePage = 65001
SYS:SQLServer:Unicode = 1
In the SQL DB :
SYS:CodePage = 1252
SYS:SQLServer:Unicode = 0
You must manually change the values of the 2 fields in the IDS file, using ACCESS.
You must change the values to match the ones in SQL :
SYS:CodePage = 1252
SYS:SQLServer:Unicode = 0
Once this is done, save the changes in the IDS file, and try to reconnect to the security file in iBase Designer.
If you can connect to the Security file, then, try to open the iBase database.
You will then have to convert the Database to Unicode.
Problem should now be fixed.
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21976512