Skip to content

Latest commit

 

History

History
98 lines (66 loc) · 8.79 KB

connecting-to-sql-server-accesstosql.md

File metadata and controls

98 lines (66 loc) · 8.79 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic ms.collection helpviewer_keywords
Connecting to SQL Server (AccessToSQL)
Learn how to connect to a target instance of SQL Database to migrate Access databases. SSMA obtains metadata about databases in SQL Database.
cpichuka
cpichuka
11/16/2020
sql
ssma
conceptual
sql-migration-content
authentication
instance of SQL Server
metadata, refreshing
ports
refreshing metadata
spaces in database names
special characters
SQL Server
SQL Server, connecting
SQL Server, connecting to
SQL Server, reconnecting

Connecting to SQL Server (AccessToSQL)

To migrate Access databases to [!INCLUDEssNoVersion], you must connect to the target instance of the [!INCLUDEssNoVersion]. When you connect, SSMA obtains metadata about the databases in the instance of [!INCLUDEssNoVersion] and displays database metadata in SQL Server Metadata Explorer. SSMA stores information about which instance of [!INCLUDEssNoVersion] that you are connected to, but does not store passwords.

Your connection to [!INCLUDEssNoVersion] stays active until you close the project. When you reopen the project, you must reconnect to [!INCLUDEssNoVersion] if you want an active connection to the server. You can work offline until you load database objects into [!INCLUDEssNoVersion] and migrate data.

Metadata about the instance of [!INCLUDEssNoVersion] is not automatically synchronized. Instead, to update the metadata in SQL Server Metadata Explorer, you must manually update the [!INCLUDEssNoVersion] metadata. For more information, see the "Synchronizing SQL Server Metadata" section later in this topic.

Required SQL Server Permissions

The account that is used to connect to [!INCLUDEssNoVersion] requires different permissions depending on the actions that the account performs:

  • To convert Access objects to [!INCLUDEtsql] syntax, to update metadata from [!INCLUDEssNoVersion], or to save converted syntax to scripts, the account must have permission to log on to the instance of [!INCLUDEssNoVersion].

  • To load database objects into [!INCLUDEssNoVersion], the account must be a member of the db_ddladmin database role.

  • To migrate data to [!INCLUDEssNoVersion], the account must be a member of the db_owner database role.

Establishing a SQL Server Connection

Before you convert Access database objects to [!INCLUDEssNoVersion] syntax, you must establish a connection to the instance of [!INCLUDEssNoVersion] where you want to migrate the Access databases.

When you define the connection properties, you also specify the database where objects and data will be migrated. You can customize this mapping at the Access database level after you connect to [!INCLUDEssNoVersion]. For more information, see Mapping Source and Target Databases.

Important

Before you connect to [!INCLUDEssNoVersion], make sure that the instance of [!INCLUDEssNoVersion] is running and can accept connections.

To connect to [!INCLUDEssNoVersion]:

  1. On the File menu, select Connect to SQL Server. If you previously connected to [!INCLUDEssNoVersion], the command name will be Reconnect to SQL Server.

  2. In the Server name box, enter or select the name of the instance of [!INCLUDEssNoVersion].

    • If you are connecting to the default instance on the local computer, you can enter localhost or a dot (.).
    • If you are connecting to the default instance on another computer, enter the name of the computer.
    • If you are connecting to a named instance, enter the computer name, a backslash, and the instance name. For example: MyServer\MyInstance.
    • To connect to an active user instance of [!INCLUDEssExpress], connect by using named pipes protocol and specifying the pipe name, such as \\.\pipe\sql\query. For more information, see the [!INCLUDEssExpress] documentation.

  3. If your instance of [!INCLUDEssNoVersion] is configured to accept connections on a non-default port, enter the port number that is used for [!INCLUDEssNoVersion] connections in the Server port box. For the default instance of [!INCLUDEssNoVersion], the default port number is 1433. For named instances, SSMA will try to obtain the port number from the [!INCLUDEssNoVersion] Browser Service.

  4. In the Database box, enter the name of the target database for object and data migration. This option is not available when reconnecting to [!INCLUDEssNoVersion]. The target database name cannot contain spaces or special characters. For example, you can migrate Access databases to a [!INCLUDEssNoVersion] database named abc. But you cannot migrate Access databases to a [!INCLUDEssNoVersion] database named a b-c. You can customize this mapping per database after you connect. For more information, see Mapping Source and Target Databases

  5. In the Authentication drop-down menu, select the authentication type to use for the connection. To use the current Windows account, select Windows Authentication. To use a [!INCLUDEssNoVersion] login, select SQL Server Authentication, and then provide a user name and password.

  6. For Secure connection, two controls are added, Encrypt Connection Checkbox and TrustServerCertificate Checkbox. Only when Encrypt Connection Checkbox is checked TrustServerCertificate Checkbox is visible. When Encrypt Connection is checked(true) and TrustServerCertificate is unchecked(false), will validate the [!INCLUDEssNoVersion] SSL certificate. Validating the server certificate is a part of the SSL handshake and ensures that the server is the correct server to connect to. To ensure this, a certificate must be installed on the client side as well as on the server side.

  7. Click Connect.

Important

While you may connect to a higher version of [!INCLUDEssNoVersion], compared to the version chosen when the migration project was created, conversion of the database objects is determined by the target version of the project and not the version of the [!INCLUDEssNoVersion] you are connected to.

Synchronizing SQL Server Metadata

If [!INCLUDEssNoVersion] schemas change after you connect, you can synchronize the metadata with the server.

To synchronize SQL Server metadata, SQL Server Metadata Explorer, right-click Databases, and then select Synchronize with Database.

Reconnecting to SQL Server

Your connection to [!INCLUDEssNoVersion] stays active until you close the project. When you reopen the project, you must reconnect to [!INCLUDEssNoVersion] if you want an active connection to the server. You can work offline until you load database objects into [!INCLUDEssNoVersion] and migrate data.

The procedure for reconnecting to [!INCLUDEssNoVersion] is the same as the procedure for establishing a connection.

Next Steps

If you want to customize the mapping between source and target databases, see Mapping Source and Target Databases Otherwise, the next step is to convert database objects to [!INCLUDEssNoVersion] syntax using convert database objects.

See Also

Migrating Access Databases to SQL Server