Login Credentials and Authentication¶

Windows Trusted Connection (MS SQL Only)¶

The simplest approach for in-house MS SQL Server deployments is to use Windows Authentication (also called a trusted connection). With this method, the driver authenticates to SQL Server using the Windows identity of the user running the application — no username or password needs to be stored in any configuration file.

To enable this, set UseTrustedConnection=yes in the mds.ini file:

[SQL_BTR]
Server=myserver\sql2022
Database=appdb
UseTrustedConnection=yes

When trusted connections are used, the User and Password settings in mds.ini are ignored.

Requirements:

• Each Windows user who runs the application must have a corresponding SQL Server login with appropriate read/write permissions on the target database.

• This is typically managed through Active Directory groups mapped to SQL Server roles, so that permissions are granted automatically when a user is added to the appropriate AD group.

Advantages:

• No passwords to store, encrypt, or rotate.

• Each user’s database activity is logged under their own identity, providing a clear audit trail.

• Integrates naturally with existing Active Directory infrastructure.

Considerations:

• Requires that your SQL Server and Active Directory are configured to support Windows Authentication for every application user. This setup is intentional and requires coordination with your database and network administrators.

• Not available for Oracle or PostgreSQL backends.

Shared Application Credentials¶

The most common deployment strategy — and the one used by the majority of software vendors — is to configure a single database username and encrypted password that all application users share. The software vendor sets up the credentials during deployment; end users never see or need to know the actual database password.

The username and encrypted password are stored in the mds.ini file:

[SQL_BTR]
Server=myserver\sql2022
Database=appdb
User=app_dbuser
Password=01c3f05c0ca0d7868adf16b133d8fe5ee820922805920dbeec8717cb07bc1b1c4264836c860105e717468f6191f2a814ac

The password must be encrypted using the MdsEncryptPassword utility. A plain-text password will not work.

Advantages:

• Works across all backends (MS SQL, Oracle, PostgreSQL).

• Simple to deploy — the software vendor configures the credentials once and ships them as part of the installation.

• The software vendor retains control over database access. Only the vendor knows the unencrypted password, which limits what end users can do with the database outside of the application.

• The shared database user can be given restricted permissions — only the rights needed by the application — limiting the blast radius if credentials are compromised.

Considerations:

• Since all application users connect with the same database identity, it is not possible to distinguish individual users at the database level. All activity appears to come from the shared account.

• Password rotation requires updating the mds.ini file on every deployed machine.

Per-User Database Credentials¶

BTR2SQL also supports configurations where each user authenticates to the database with their own username and password. There are several ways to accomplish this:

Mertech Login Dialog

If the driver cannot determine credentials at startup — because no User/Password is configured in mds.ini or the INT files, and UseTrustedConnection is not set — it displays a built-in login dialog box prompting the user for server, username, and password. This is the same dialog shown during migration (see Login to the target database).

This approach is uncommon in production deployments, but it can be useful during development or in environments where each user must authenticate individually.

Programmatic Login (B_SQL_LOGIN)

Applications can suppress the built-in dialog and perform the login programmatically using the B_SQL_LOGIN extended API call. This allows the application to present its own login UI and pass the credentials to the driver at runtime. See the SDK documentation for details on the B_SQL_LOGIN operation.

Some customers use this approach to integrate database authentication into their application’s existing login workflow, so the user experiences a single sign-on rather than a separate database login prompt.

Advantages:

• Each user’s database activity is logged under their own identity.

• Credentials are not stored in configuration files on disk.

Considerations:

• Requires each user to have a database login, which adds administrative overhead.

• The application (or user) must supply credentials at startup, which may not be desirable in all environments.

Windows Credential Manager Storage¶

For deployments where you want each user to control their own password without the software vendor managing a shared secret, BTR2SQL supports storing the database password in Windows Credential Manager. Only a hex reference to the stored credential is placed in the mds.ini or INT file — the actual password is retrieved from Credential Manager at runtime.

[SQL_BTR]
Server=myserver\sql2022
Database=appdb
User=app_dbuser
Password=02636F6D2E6D6572746563682E67686768

The 02 prefix indicates a Credential Manager reference. See MdsEncryptPassword for details on generating the reference value and the StoreCredential.ps1 script.

Advantages:

• The password is protected by Windows security mechanisms and is tied to the current user’s Windows account.

• No encrypted password is stored in the configuration file — only a reference.

• Individual users can update their own password through Windows Credential Manager without modifying application configuration files.

Considerations:

• Each machine (and user profile) must have the credential stored in Credential Manager before the application can connect.

• Deployment requires an additional step to populate the credential store, typically via a setup script.