In my last post, I talked about how to create an Azure SQL database. In this post, I am going to talk about how to connect to the same. You have multiple options to connect to the database:
1. Through the Management Portal using the link: https://<Azure SQL Database Server Name>.database.windows.net/
2. SQL Server Management tools like Management Studio, SQL Server Data Tools
3. Through programmatic means using .NET or other languages
But before you start connecting to your database, you will first need to setup the list of allowed IP Addresses. This post will talk about how to configure the firewall for your Azure SQL Database.
If you do not setup the firewall correctly, then you will be presented with the following error when you try to connect to the Azure SQL Database from any of the tools.
Cannot connect to tcp: <sevrer name>.database.windows.net,1433.
Cannot open server ‘<sever name>’ requested by the login. Client with IP address <IP Address> is not allowed to access the server. To enable access, use the Windows Azure Management Portal or run sp_set_firewall_rule on the master database to create a firewall rule for this IP address or address range. It may take up to five minutes for this change to take effect.
Login failed for user <user name>.
This session has been assigned a tracing ID of <tracing id>. Provide this tracing ID to customer support when you need assistance. (Microsoft SQL Server, Error: 40615)
Read on to know how this can be avoided.
Managing the Firewall from the portal
The great Azure firewall is not going to let you access your database when you click on the Manage button (screenshot 1) in the Azure Management portal, if your IP Address is not added in the list of trusted IPs. You can get to this screen by logging into the Azure Management Portal and making the following selections:
SQL DATABASES –> SERVERS tab –> Click on the server you want to manage – >CONFIGURE tab
You will need to add the IP Address starting and ending range and give your rule a name. Don’t forget to click on the SAVE button for the changes to take effect. If you are setting up the rule for one IP Address only, then your starting and ending address will be the same. Note that the list of IP Addresses will keep growing if you have a DHCP like I do because you might end up with a new address every time to re-start the machine or your lease expires. Remember to remove the older IP Addresses from the list when you do not require those addresses to access your database.
You could also use the “Add To the Allowed IP Addresses” (screenshot 1) view to provide access to your current IP Address to your database. The management portal detects your current IP Address and gives you an option to add it as an allowed IP Address at the click of a button. You still need to click on SAVE to persist the change!
Adding and Deleting Rules using T-SQL
You can also add and delete rules using Transact-SQL commands which needs to be executed against the master database.
Eg. To add a new firewall rule for an IP Address:
exec sp_set_firewall_rule N’Laptop’,'<IP Address>’,'<IP Address>’
Eg. To remove a firewall rule:
exec sp_delete_firewall_rule N'<Rule Name>’
If you allow ”Windows Azure Services” (Screenshot 1) to access your database, then you will find that that the IP Address “0.0.0.0” is added as well. This can be viewed by looking at the output of the following query against the master database:
select * from sys.firewall_rules
sp_set_firewall_rule (SQL Database)
Programmatically changing the settings