1. Within Windows Azure, navigate to ‘ENDPOINTS’ and Select ‘ADD’ at the bottom of the page.
2. You will need to open up a port in order to allow access to the instance of SQL Server. I’m going to stick with the default port of 1433 and create a new endpoint here. Make sure Both Check boxes (circled below) are NOT ticked
3. Next up we need to configure the firewall on the server itself. Open an RDP session and log into the Azure VM. Open Control Panel > Windows Firewall and configure a new inbound rule in the Windows firewall for port 1433:
4. Select a rule type of ‘Port’. The default port for SQL Server is 1433 but this can be different depending on how the SQL Server properties have been set up. Simply type ‘tcp setting for SQL Server’ in a search engine and this will tell you where to find or change the port number.
After clicking next, the options are as follows:
· Select TCP and specify port 1433
· Next, select ‘Allows This Connection’
· Then, select when you want the rule applied – typically ‘Public.’ This will enable external connection to the port.
· Lastly, assign this rule a name (e.g SQL Port)
5. The next step is to verify that SQL Server is enabled to use the TCP protocol. Ensure that ‘Named Pipes’ is set to DISABLED. You can do all these settings by using SQL configuration manager.
6. Check that SQL Server is configured to use mixed mode authentication. Right Click on the server (within SSMS) and toggle to the ‘Security’ page and verify that the radio button ‘SQL Server and Windows Authentication Mode’ is selected.
7. Create a test login
8. Restart the SQL Server. It is important the service is restarted, otherwise, any changes to the tcp or pipelines will n bot have been committed.
9. The final step is to login in from your local SSMS. Specify the full server name (taken from Azure) and the new user credentials created.