AWS Schema Conversion Tool
User Guide (Version 1.0)

Using Microsoft SQL Server as a Source for AWS Schema Conversion Tool

You can use AWS SCT to convert schemas and application code from SQL Server to the following targets:

  • Amazon RDS for MySQL

  • Amazon Aurora (MySQL)

  • Amazon RDS for PostgreSQL

  • Amazon Aurora (PostgreSQL)

  • Amazon RDS for SQL Server

For more information, see the following sections:

Permissions Required When Using Microsoft SQL Server as a Source

The privileges required for Microsoft SQL Server as a source are listed following:

  • VIEW DEFINITION

  • VIEW DATABASE STATE

Repeat the grant for each database whose schema you are converting.

Using Windows Authentication When Using Microsoft SQL Server as a Source

If your application runs on a Windows-based intranet, you might be able to use Windows Authentication for database access. Windows Authentication uses the current Windows identity established on the operating system thread to access the SQL Server database. You can then map the Windows identity to a SQL Server database and permissions. To connect to SQL Server using Windows Authentication, you must specify the Windows identity that your application is using. You must also grant the Windows identity access to the SQL Server database.

SQL Server has two modes of access: Windows Authentication mode and Mixed Mode. Windows Authentication mode enables Windows Authentication and disables SQL Server Authentication. Mixed Mode enables both Windows Authentication and SQL Server Authentication. Windows Authentication is always available and cannot be disabled. For more information about Windows Authentication, see the Microsoft Windows documentation.

The possible example for creating a user in TEST_DB is shown below

USE [TEST_DB] CREATE USER [TestUser] FOR LOGIN [TestDomain\TestUser] GRANT VIEW DEFINITION TO [TestUser] GRANT VIEW DATABASE STATE TO [TestUser]

Using Windows Authentication with a JDBC Connection

The JDBC driver does not support Windows Authentication when the driver is used on non-Windows operating systems. Windows Authentication credentials, such as user name and password, when connecting to SQL Server from non-Windows operating systems. In such cases, the applications must use SQL Server Authentication instead

In JDBC connection string, the parameter integratedSecurity must be specified to connect using Windows Authentication. The JDBC driver supports Integrated Windows Authentication on Windows operating systems through the integratedSecurity connection string parameter.

To use integrated authentication

  1. Install the JDBC driver.

  2. Copy the sqljdbc_auth.dll file to a directory on the Windows system path on the computer where the JDBC driver is installed.

    The sqljdbc_auth.dll files are installed in the following location:

    <installation directory>\sqljdbc_<version>\<language>\auth\

When you try to establish a connection to SQL Server database using Windows Authentication, you might get the error: This driver is not configured for integrated authentication. This problem can be solved by performing the following actions:

  • need to declare two variables which point to the installed path of your JDBC:

    -variable name: SQLJDBC_HOME; variable value: D:\lib\JDBC4.1\enu (where your sqljdbc4.jar exists);

    -variable name: SQLJDBC_AUTH_HOME; variable value: D\lib\JDBC4.1\enu\auth\x86 (if you are running 32bit OS) or D\lib\JDBC4.1\enu\auth\x64 (if you are running 64bit OS). This is whe your sqljdbc_auth.dll is located.

  • copy sqljdbc_auth.dll to folder where your JDK/JRE is running. You may copy to lib folder, bin folder, etc. I copied to the following folder:

    [JDK_INSTALLED_PATH]\bin; [JDK_INSTALLED_PATH]\jre\bin; [JDK_INSTALLED_PATH]\jre\lib; [JDK_INSTALLED_PATH]\lib;
  • ensure that in your jdbc library folder, you only have SQLJDBC4.jar. Please remove other sqljdbc*.jar file from that folder (or copy to other folder). If you are adding the driver as part of your program, please ensure that you add only SQLJDBC4.jar as driver to use.

  • copy sqljdbc_auth.dll the file in the folder with your application.

Note

If you are running a 32-bit Java Virtual Machine (JVM), use the sqljdbc_auth.dll file in the x86 folder, even if the operating system is the x64 version. If you are running a 64-bit JVM on a x64 processor, use the sqljdbc_auth.dll file in the x64 folder.

When you connect to a SQL Server database, you can choose either the Windows Authentication or SQL Server Authentication for the Authentication option.

Connecting to SQL Server as a Source

Use the following procedure to connect to your Microsoft SQL Server source database with the AWS Schema Conversion Tool.

To connect to a Microsoft SQL Server source database

  1. In the AWS Schema Conversion Tool, choose Connect to Microsoft SQL Server.

    
                            Connect to source database

    The Connect to Microsoft SQL Server dialog box appears.

    
                            Microsoft SQL Server connection information
  2. Provide the Microsoft SQL Server source database connection information. Use the instructions in the following table.

    For This Parameter Do This
    Server name

    Type the Domain Name Service (DNS) name or IP address of your source database server.

    Server port

    Type the port used to connect to your source database server.

    Instance name

    Type the instance name for the SQL Server database. To find the instance name, run the query SELECT @@servername; on your SQL Server database.

    User name and Password

    Type the user name and password to connect to your source database server.

    Note

    AWS SCT uses the password to connect to your source database only when you create your project or choose the Connect to source option in a project, where source is your source database. To guard against exposing the password for your source database, AWS SCT doesn't store the password. If you close your AWS SCT project and reopen it, you are prompted for the password to connect to your source database as needed.

    Use SSL

    Select this option if you want to use Secure Sockets Layer (SSL) to connect to your database. Provide the following additional information, as appropriate, on the SSL tab:

    • Trust Server Certificate: Select this option to trust the server certificate.

    • Trust Store: The location of a trust store containing certificates.

    • Trust Store Password: The password for the trust store.

    Store Password

    AWS SCT creates a secure vault to store SSL certificates and database passwords. Enabling this option lets you store the database password and to connect quickly to the database without having to enter the password.

    Sql Server Driver Path

    Type the path to the driver to use to connect to the source database. For more information, see Installing the Required Database Drivers.

    If you store the driver path in the global project settings, the driver path doesn't appear on the connection dialog box. For more information, see Storing Driver Paths in the Global Settings.

  3. Choose Test Connection to verify that you can successfully connect to your source database.

  4. Choose OK to connect to your source database.