User Information Outside the .INT File
The SQL Drivers determine the database server of a table by using the SERVER_NAME setting of the intermediate file of the table. Usually, this setting contains database server identification and user login information.
Intermediate files are plain ASCII files that can be viewed by anybody having access to the intermediate file. For this reason, it is preferred not to store the user information in the intermediate file.
Next to security reasons, there may be a need to open tables under different user accounts. If the user information is not stored in the intermediate file, the user account can change without the need for a different intermediate file.
SQL Server NT Authentication
The SERVER_NAME setting for a table using NT Authentication will be something like:
SERVER_NAME SERVER=MySQLServer;Trusted_Connection=yes;DATABASE=MyDb
When using NT authentication, you trust that if the user is able to log in to NT, there are sufficient rights to access the data. In this mode, there is no need to change the intermediate file since no user information is stored in the file.
"Normal" Authentication
When using "normal" authentication for the DataFlex SQL Server Driver, the SERVER_NAME setting for a table will contain user and password information as in the examples below:
SQL Server
SERVER_NAME SERVER=MySQLServer;UID=MyName;PWD=MyPassword;DATABASE=MyDb
DB2
SERVER_NAME DSN=MyDB2Database;UID=MyName;PWD=MyPassword
ODBC
SERVER_NAME DSN=MyDataSource;UID=MyName;PWD=MyPassword
In this case, there is sensitive information in the intermediate file. It would be desirable to remove the user information from the intermediate file for security reasons. The result would be an intermediate file containing a SERVER_NAME setting like:
SQL Server
SERVER_NAME SERVER=MySQLServer;DATABASE=MyDb
DB2
SERVER_NAME DSN=MyDB2Database
ODBC
SERVER_NAME DSN=MyDataSource
In order to open a table that does not contain any user information in the intermediate file, a connection should be established before the table is opened.
Establishing a connection is possible by using the Login command. In order to log in to the database MyDb on server MySQLServer as user MyName using password MyPassword, one would issue the following login command:
SQL Server
Login "SERVER=MySQLServer;DATABASE=MyDb" "MyName" "MyPassword" "MSSQLDRV"
DB2
Login "DSN=MyDB2Database" "MyName" "MyPassword" "DB2_DRV"
ODBC
Login "DSN=MyDataSource" "MyName" "MyPassword" "DB2_DRV"
After this command has been issued, tables containing the server name setting as described above can be opened without having to enter any additional information.
Using DataFlex Tools with "No User Info" Tables
Tables that do not contain any user information in the intermediate file can be used from the DataFlex tools: Database Builder, Database Explorer, and Studio.
In order to open a "no user info" table in any of the tools, you need to establish a connection before opening the table. To establish a connection, select Database | Login. Choose the appropriate driver. In the server form, enter the same information as is stored in the intermediate file for the SERVER_NAME setting. In case of the SQL Server example above, that would be SERVER=MySQLServer;DATABASE=MyDb. In the user name form, enter the user name, and in the password form, enter the password.
In order to convert tables in a way that results in intermediate files without user information, you need to establish a connection before converting your tables. To establish a connection, select Database | Login. See above for further details.
Then, in the conversion wizard, enter the server name in the Server form (without the SERVER= or DSN=) and the database name in the Database form (without the DATABASE=). Do not enter information in the User ID or Password forms. Also, do not check the use NT Authentication checkbox. Mark the other options as needed for the conversion and press OK.
SQL Server, Leaving out the Database Information
In some environments, an application needs to access data from different databases. The definition of the data (columns, indexes, and such) is the same in multiple databases, but the information in the tables is different. A set-up like this is quite common in environments where one company runs an administration service for other companies.
It is possible to create a SERVER_NAME setting without the database reference. In that case, the database reference needs to be specified when logging in. If we want to be able to open data on database server MySQLServer that has two different databases – MyDb and TheirDB – we would need to create an intermediate file with SERVER_NAME setting only:
SERVER_NAME SERVER=MySQLServer
When we want to open tables in database TheirDb, we need to establish the connection using the login command:
Login "SERVER=MySQLServer" "MyName;DATABASE=TheirDb" "MyPassword" "MSSQLDRV"
The database name is passed as part of the user name to the login command. After this login command has been issued, all tables that use the SERVER_NAME SERVER=MySQLServer as server name setting in their intermediate file will automatically be opened in database TheirDb.
If we want to connect to database MyDb, we would need to issue the login command in the following way:
Login "SERVER=MySQLServer" "MyName;DATABASE=MyDb" "MyPassword" "MSSQLDRV"
After this login command has been issued, all tables that use the SERVER_NAME SERVER=MySQLServer as server name setting in their intermediate file will automatically be opened in database MyDb.
Tables that do not have any database information in the intermediate file can also be opened in the DataFlex tools. Like the code sample above, you need to enter ;DATABASE=… after the user ID in the login screen when establishing a connection.