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"`, `"odbc"`, or `"connection_string"`
`username`	Yes*	SQL Server username (*not required for Windows auth via ODBC)
`password`	Yes*	SQL Server password (*not required for Windows auth via ODBC)
`server`	Yes	Hostname or IP address
`port`	Yes	Port number (default: 1433)
`database`	Yes	Database name

note

SQL Server is the only database that 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 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": "$env{MSSQL_USER}",
      "password": "$env{MSSQL_PASSWORD}",
      "server": "sql-prod.company.com",
      "port": 1433,
      "database": "TargetDB"
    }
  }
}

Example: Windows Trusted Authentication (ODBC)

Use ODBC mode with Trusted_Connection=yes for Windows integrated authentication:

{
  "mssql_trusted": {
    "ds_type": "mssql",
    "auth_mode": "odbc",
    "info": {
      "connection_string": "Driver={ODBC Driver 17 for SQL Server};Server=sql-prod.company.com;Database=TargetDB;Trusted_Connection=yes;"
    }
  }
}

Example: Connection String Mode

For full control over the connection string:

{
  "mssql_connstr": {
    "ds_type": "mssql",
    "auth_mode": "connection_string",
    "info": {
      "connection_string": "Driver={ODBC Driver 17 for SQL Server};Server=sql-prod.company.com,1433;Database=TargetDB;Uid=$env{MSSQL_USER};Pwd=$env{MSSQL_PASSWORD};Encrypt=yes;TrustServerCertificate=no;"
    }
  }
}

Troubleshooting

Login failed for user: 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';

Connection Parameters​

Required Permissions​

As Source​

As Target​

As Migration Tracking​

Example: Classic Authentication​

Example: Windows Trusted Authentication (ODBC)​

Example: Connection String Mode​

Troubleshooting​

See Also​