Metrics advisor connection to azure sql server

Question

Hi Guys,

I have started up a basic azure sql server and database. i can connect to it via the query editor and create tables, and push dummy datasets in.

When i want to connect to the sql database and source via azure metrics advisor, it does not allow me to connect with the basic connection string settings when i select Azure SQL Database | SQL server.

My MetricsAdvor Service stats up and works fine, I can connect to my SQL server and database and insert data. but i get a connection Failure. Please review paramaters error when i try to connect to the sql server via metricsadvisor.

My Connection String:
Server=servername.database.windows.net;Database=dbname;User Id=xxxxxxxxxxxxxxxxxxx;Password=xxxxxxxxxxxxxxxxxxxxx;

Question:
I see on top there is a prompt to please grant access the IP Address (list of 3 addresses ) in your data source. Is this needed for the connection to work ?

Answer 1

Please set the encryption=yes on the connection string.

When you create a new server in Azure SQL Database a server-level firewall blocks all access to the public endpoint for the server (which is accessible at mysqlserver.database.windows.net). You can create a firewall rule using the Azure portal to allow specific IP addresses to access the database server or databases as explained here. Make sure to set "On" the button named "Allow Azure services and resources to access this server" as you can see in the same documentation.