| title | description | author | ms.author | ms.reviewer | ms.date | ms.service | ms.subservice | ms.topic | ms.collection | helpviewer_keywords | |||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Connecting to Azure SQL Database (AccessToSQL) |
Learn how to connect to a target instance of Azure SQL Database to migrate Access databases. SSMA obtains metadata about databases in Azure SQL Database. |
cpichuka |
cpichuka |
randolphwest |
11/19/2024 |
sql |
ssma |
conceptual |
|
|
To migrate Access databases to [!INCLUDE ssazure-sqldb], you must connect to the target instance of [!INCLUDE ssazure-sqldb]. When you connect, SQL Server Migration Assistant (SSMA) obtains metadata about all the databases in the instance of [!INCLUDE ssazure-sqldb] and displays database metadata in the Azure SQL Database Metadata Explorer. SSMA stores information about which instance of [!INCLUDE ssazure-sqldb] you're connected to, but doesn't store passwords.
Your connection to [!INCLUDE ssazure-sqldb] stays active until you close the project. When you reopen the project, you must reconnect to [!INCLUDE ssazure-sqldb] if you want an active connection to the server. You can work offline until you load database objects into [!INCLUDE ssazure-sqldb] and migrate data.
Metadata about the instance of [!INCLUDE ssazure-sqldb] isn't automatically synchronized. Instead, to update the metadata in Azure SQL Database Metadata Explorer, you must manually update the [!INCLUDE ssazure-sqldb] metadata. For more information, see the Synchronize Azure SQL Database metadata section in this article.
The account that is used to connect to [!INCLUDE ssazure-sqldb] requires different permissions depending on the actions that the account performs:
-
To convert Access objects to [!INCLUDE tsql] syntax, to update metadata from [!INCLUDE ssazure-sqldb], or to save converted syntax to scripts, the account must have permission to sign in to the instance of [!INCLUDE ssazure-sqldb].
-
To load database objects into [!INCLUDE ssazure-sqldb], the account must be a member of the db_ddladmin database role.
-
To migrate data to [!INCLUDE ssazure-sqldb], the account must be a member of the db_owner database role.
Before you convert Access database objects to [!INCLUDE ssazure-sqldb] syntax, you must establish a connection to the instance of [!INCLUDE ssazure-sqldb] where you want to migrate the Access database or 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 schema level after you connect to [!INCLUDE ssazure-sqldb]. For more information, see Mapping Source and Target Databases (AccessToSQL).
Important
Before you try to connect to [!INCLUDE ssazure-sqldb], make sure that your IP address is allowed through the [!INCLUDE ssazure-sqldb] firewall.
To connect to [!INCLUDE ssazure-sqldb]:
-
On the File menu, select Connect to SQL Azure (this option is enabled after the creation of a project).
If you previously connected to [!INCLUDE ssazure-sqldb], the command name is Reconnect to SQL Azure.
-
In the connection dialog box, enter or select the server name of [!INCLUDE ssazure-sqldb].
-
Enter, select, or Browse the Database name.
-
Enter or select Username.
-
Enter the Password.
-
SSMA recommends encrypted connection to [!INCLUDE ssazure-sqldb].
-
Select Connect.
If there are no databases in the [!INCLUDE ssazure-sqldb], you can create the first database using Create Azure Database option that appears on the select of Browse button.
Metadata about databases in [!INCLUDE ssazure-sqldb] isn't automatically updated. The metadata in Azure SQL Database Metadata Explorer is a snapshot of the metadata when you first connected to [!INCLUDE ssazure-sqldb], or the last time that you manually updated metadata. You can manually update metadata for all databases, or for any single database or database object. To synchronize metadata:
-
Make sure that you're connected to [!INCLUDE ssazure-sqldb].
-
In Azure SQL Database Metadata Explorer, select the check box next to the database or database schema that you want to update. For example, to update the metadata for all databases, select the box next to Databases.
-
Right-click Databases, or the individual database or database schema, and then select Synchronize with Database.
If [!INCLUDE ssazure-sqldb] schemas change after you connect, you can refresh metadata from the server.
To refresh [!INCLUDE ssazure-sqldb] metadata:
- In Azure SQL Database Metadata Explorer, right-click Databases, and then select Refresh from Database.
Your connection to [!INCLUDE ssazure-sqldb] stays active until you close the project. When you reopen the project, you must reconnect to [!INCLUDE ssazure-sqldb] if you want an active connection to the server. You can work offline until you load database objects into [!INCLUDE ssazure-sqldb] and migrate data.
The procedure for reconnecting to [!INCLUDE ssazure-sqldb] is the same as the procedure for establishing a connection.