How to access the Azure SQL databases via SQL Server Management Studio

NOTE: This article relates to Sage 200 Professional software which has been deployed via the Sage Provisioning Portal along with an Azure Subscription.

TIP: As default, the logins provided by the connection string do not have the permission to Update tables, you can however use the Administrator login for this which are detailed lowed in this article.

How to generate a temporary connection string for your Azure SQL database

CAUTION: The connection string you will obtain here is designed to be used on a temporary basis. These credentials are set to expire after 1 day, and have read only access.

1. Install SQL Server Management Studio from the Microsoft website onto the client machine you wish to use, the latest version can be found here.
2. Log into the Sage Provisioning Portal via this link.
3. Navigate to the site details page which lists the databases, users, modules etc, and select the connection string button highlighted below.
   TIP: If you don't have access to the Connection String button below, you can access this via Azure, instructions can be found here.
   
   TIP: You can check which database you need to request the connection string through the Companies menu in Sage 200 System Administration.
4. Click Yes to the message displayed.
   
5. An email will be sent to the user who requested the connection string with a link to select, similar to the below. Click the link in this email to access the connection string details.
   
6. Once the link has been clicked and you have logged in, you will see a page with details similar to the below.
   
7. Open SQL Server Management Studio. On the login screen, click the Options button bottom right to expand all the tabs. Set the options as below:
   
   Server Type set to Database Engine.
   Server Name - This is the Data Source section of the connection string ending with '...windows.net'.
   TIP: Ignore the ; symbol when copying this information from the connection string.
   Authentication- Set this to SQL Server Authentication in the dropdown.
   Login - This is the ID section of the Connection string, copy and paste into SSMS.
   Password - Copy the Password section of the string and paste this in here.
   
8. Click the Connection Properties tab. Here you can leave almost all of the fields as default, but you will need to change the 'Connect to database' field by typing in the database name you wish to connect to. The database name will be on the email sent in the earlier step, the connection string screen and in the Sage Provisioning Portal.
9. Click Connect.
   NOTE: It may take a minute or so for SQL Server Management Studio to connect to the database, but once it has, you should see similar to the below.
   

This will allow you to run Select statements against the connected database. If you wish to run update procedures see below.

Running Update statements against the Azure SQL database

CAUTION: This process is only to be carried out by a user experienced in SQL Server and should only be updated when tested in an offline copy first to be sure the results are what is intended.

TIP: This section describes the administration access possible for the Azure SQL instance. This level of access allows the Buiness Partner to perform SQL updates or create user logins that are for applications or users which are permanent, for example a third party application which works alongside Sage 200. This adminstration user should be configured and secured by the Business Partner.

To update the database, you will need to access the Administrator user for the site, for which the password can be configured via the Sage Provisioning Portal.

1. Log into the Sage Provisioning Portal.
2. Navigate to the Site Details page, as detailed above in step 3.
3. In the Database servers section, click the Server Name 'iq9zospm9g' in this example.
   
4. You will be presented with the SQL Server details page as below.
   
5. The Server admin is the logon name you will need to use in SQL Server Management Studio.
6. If you haven't already set a server admin password, click the button on the right hand side to enter then confirm.
7. Once you have the admin logon name and password, open SSMS following the above steps but entering the Administrator Logon name and Password you have set.
   

You should now be able to run update scripts against the SQL database in SQL Management Studio and generate credentials for other applications to use, much in the same as as in an on-premise environment.