SQL Server

SQL Server is the most versatile database in MigratorXpress -- it can serve as a source, a target, and the migration tracking database.

Connection Parameters

Parameter	Required	Description
ds_type	Yes	Must be "mssql"
auth_mode	Yes	"classic" (SQL Server authentication, the parameters below), "connection_string", "trusted" or "odbc" (see Authentication modes)
username	Yes	SQL Server login
password	Yes	SQL Server password
server	Yes	Hostname or IP address
port	Yes	Port number, as a JSON integer (usually 1433)
database	Yes	Database name

MigratorXpress connects to SQL Server with the pymssql driver: no ODBC driver is required on the MigratorXpress host, except for odbc entries. Besides SQL Server authentication (classic), SQL Server entries can use an ADO.NET connection string (connection_string), integrated authentication -- SSPI on Windows, Kerberos on Linux (trusted), or an ODBC DSN (odbc, for a source or the tracking database only). See Authentication modes.

note

SQL Server can fill all three roles in a MigratorXpress migration. You can even use the same SQL Server instance for source, target, and migration tracking by creating separate database entries in your auth file.

Required Permissions

As Source

-- Grant read access for data transfer and schema discovery
GRANT SELECT ON SCHEMA::dbo TO migratorxpress_user;
GRANT VIEW DEFINITION ON SCHEMA::dbo TO migratorxpress_user;

As Target

-- Grant permissions for schema creation and data loading
GRANT CREATE TABLE TO migratorxpress_user;
GRANT ALTER ON SCHEMA::dbo TO migratorxpress_user;
GRANT INSERT ON SCHEMA::dbo TO migratorxpress_user;
GRANT UPDATE ON SCHEMA::dbo TO migratorxpress_user;
GRANT DELETE ON SCHEMA::dbo TO migratorxpress_user;

As a source, SQL Server has no intra-table parallel transfer method: use --n_jobs to transfer several tables at once. Since 0.7.3, a SQL Server source provides its primary, unique and foreign keys (from sys.key_constraints and sys.foreign_keys) to the copy_pk, copy_ak and copy_fk tasks.

As Migration Tracking

-- Full access to the migration tracking database
ALTER ROLE db_owner ADD MEMBER migratorxpress_user;

The migration tracking user needs full access to create and manage the internal tables that track migration runs, task statuses, and resume state.

Example: Classic Authentication

{
  "mssql_target": {
    "ds_type": "mssql",
    "auth_mode": "classic",
    "info": {
      "username": "migrator",
      "password": "********",
      "server": "sql-prod.company.com",
      "port": 1433,
      "database": "TargetDB"
    }
  }
}

Example: Integrated Authentication

{
  "mssql_trusted": {
    "ds_type": "mssql",
    "auth_mode": "trusted",
    "info": {
      "server": "sql-prod.corp.company.com",
      "port": 1433,
      "database": "TargetDB"
    }
  }
}

On Linux, integrated authentication uses Kerberos: see the prerequisites in Authentication modes.

Troubleshooting

Login failed for user: With classic entries, confirm that SQL Server authentication mode is set to "SQL Server and Windows Authentication" (mixed mode), not Windows-only. Verify the user exists with the correct permissions and check for IP restrictions.

Cannot open database requested by the login: Verify the database name, check that the user has been granted access, and confirm the database is online:

SELECT name, state_desc FROM sys.databases WHERE name = 'YourDB';

See Also

• Database Connections Overview -- Auth file format and security best practices