Connecting to Azure SQL Database (AccessToSQL) - SQL Server (2023)

  • Article
  • 4 minutes to read

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

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

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

Required Azure SQL Database Permissions

The account that is used to connect to Azure SQL Database requires different permissions depending on the actions that the account performs:

(Video) How to access azure sql database from local machine

  • To convert Access objects to Transact-SQL syntax, to update metadata from Azure SQL Database, or to save converted syntax to scripts, the account must have permission to log on to the instance of Azure SQL Database.

  • To load database objects into Azure SQL Database, the account must be a member of the db_ddladmin database role.

  • To migrate data to Azure SQL Database, the account must be a member of the db_owner database role.

Establishing an Azure SQL Database Connection

Before you convert Access database objects to Azure SQL Database syntax, you must establish a connection to the instance of Azure SQL Database 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 Azure SQL Database. For more information, see Mapping Access Databases to SQL Server Schemas.

Important

Before you try to connect to Azure SQL Database, make sure that your IP address is allowed through the Azure SQL Database firewall.

(Video) How to connect Azure SQL database by SSMS

To connect to Azure SQL Database:

  1. On the File menu, select Connect to SQL Azure (this option is enabled after the creation of a project).If you previously connected to Azure SQL Database, the command name will be Reconnect to SQL Azure.

  2. In the connection dialog box, enter or select the server name of Azure SQL Database.

  3. Enter, select, or Browse the Database name.

    (Video) Access on Azure SQL - How to create an online Access application using Azure SQL and ODBC

  4. Enter or select Username.

  5. Enter the Password.

  6. SSMA recommends encrypted connection to Azure SQL Database.

  7. Click Connect.

If there are no databases in the Azure SQL Database, you can create the first database using Create Azure Database option that appears on the click of Browse button.

Metadata about databases in Azure SQL Database is not automatically updated. The metadata in Azure SQL Database Metadata Explorer is a snapshot of the metadata when you first connected to Azure SQL Database, 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:

(Video) 6. Access Azure SQL Database From SSMS

  1. Make sure that you are connected to Azure SQL Database.

  2. 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.

  3. Right-click Databases, or the individual database or database schema, and then select Synchronize with Database.

Refreshing Azure SQL Database Metadata

If Azure SQL Database schemas change after you connect, you can refresh metadata from the server.

To refresh Azure SQL Database metadata:

  • In Azure SQL Database Metadata Explorer, right-click Databases, and then select Refresh from Database.

Reconnecting to Azure SQL Database

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

(Video) Set Up Microsoft Azure SQL Server and SQL Database (Step-By-Step Tutorial)

The procedure for reconnecting to Azure SQL Database is the same as the procedure for establishing a connection.

Next steps

The next step in the migration depends on your project needs:

See Also

Migrating Access Databases to SQL Server

Videos

1. Integrate Azure SQL with Azure Active Directory
(fastpacedtutorials)
2. Microsoft SQL Server, Error: 18456 (connecting to Azure)
(SQL Server 101)
3. Azure SQL Database Create and Connect with SSMS
(TechTalks - Wriju)
4. Connecting to Azure SQL Server by Adding Client IP Address Firewall Rule
(Deep Dive Dotnet)
5. How to create SQL authentication user in Azure SQL database
(TSInfo Technologies)
6. Azure Active Directory Authentication For SQL Database | Azure
(Rohit Sharma)
Top Articles
Latest Posts
Article information

Author: Jamar Nader

Last Updated: 04/26/2023

Views: 6691

Rating: 4.4 / 5 (75 voted)

Reviews: 82% of readers found this page helpful

Author information

Name: Jamar Nader

Birthday: 1995-02-28

Address: Apt. 536 6162 Reichel Greens, Port Zackaryside, CT 22682-9804

Phone: +9958384818317

Job: IT Representative

Hobby: Scrapbooking, Hiking, Hunting, Kite flying, Blacksmithing, Video gaming, Foraging

Introduction: My name is Jamar Nader, I am a fine, shiny, colorful, bright, nice, perfect, curious person who loves writing and wants to share my knowledge and understanding with you.