Connect to Azure database using SQL Server Management Studio (SSMS)

The location of the Azure database is shown on the main SQL Server page in Azure in the form <server name>.database.windows.net.  The default port used by Azure is 1433.  This port is checked when setting up the shared drive earlier.

Open SSMS

In Object Explorer, click 'Connect'.

In the 'Connect to Server' dialog, enter the Server name.   This will be in the form of <client name>.database.windows.net,1433 (note the comma before the port number).

Use SQL Server authentication.

Login and Password are the 'Server admin login' and associated password set up when creating the server in Azure (not the Insol2Users credentials).

Click 'Connect'

The database can now be accessed via SSMS and the following actions taken:

• Update the database to current version (if the .bacpac is not a current version)
• Create the Insol2Users security login

NOTE:

The Insol2Users 'link' may exist in both the server and database but it appears the method to renew the link in Azure is different to that used on premise.   This method requires access to the database in SSMS.  Azure Data Studio does not appear to have the same functionality (unless it's hiding somewhere).

• Delete both references to Insol2Users (server and database) as usual.
• Execute the following code against the 'master' database:

CREATE LOGIN Insol2Users WITH password='<Insol6 master password>';

GO

CREATE USER [Insol2Users] FOR LOGIN [Insol2Users] WITH DEFAULT_SCHEMA=[dbo];

GO

• Execute the following code against the target (Insol6) database:

CREATE USER [Insol2Users] FOR LOGIN [Insol2Users] WITH DEFAULT_SCHEMA=[dbo];

GO

EXEC sp_addrolemember 'db_owner', 'Insol2Users';

GO

• Confirm that the Insol2Users credentials allow access to the database using either SSMS or Azure Data Studio.