Sql-server – Can an application connect to databases from on-premise SQL Server Instance and Azure SQL together

I shared the case scenario with the Azure SQL Database team at Microsoft via an Exchange Mailing list for Azure SQL Databases contributors/MVPs. The answer provided by one of the managers of the team is the following: "Yes, transactional replication will be the best way at this point if they want to keep the replication running after the migration to Managed Instances."

Hope this helps.

After much experimentation, I stumbled upon an answer on the Secure your Azure SQL Database.

Please correct me if I am wrong here!

It is strange that the documentation on securing SQL Azure does not mention the following in any detail. The clues were here (emphasis mine):

SQL Authentication, which uses username and password for logins and users that are valid only in the context of a specific database within a logical server.

-snip-

By default, the user can connect to the database, but has no permissions to read or write data.

In SQL Azure, it is possible to have database-contained users with a password (which is different from the on-premise approach of creating a user against an existing login account). This makes sense, because the all-in-one contained approach allows for a seamless failover.

The solution is to use SSMS and not the Azure portal:

-- After connecting to SQL Azure within SSMS (as dbo)
CREATE USER [UserNameForApplication] WITH PASSWORD=N'543rg.....ehgdGDF';
GO
ALTER ROLE [MyExistingDatabaseRole] ADD MEMBER UserNameForApplication;
GO

My application can now access the SQL Azure database with zero additional rights that would otherwise be granted via the Azure portal.