In my blog post Virtual Network Integration between Azure Virtual Machine and Azure SQL Database, I have shown an implementation of secure database connections from a virtual network subnet to a PaaS Azure SQL DB using service endpoints and virtual network rules.
I will show how to validate the connection using SQL Management Studio from the virtual machine within the virtual network subnet.
Find database connection string.
Open up SQL Server Management Studio from within the virtual machine.
Enter server name that is found in the connection string.
Ensure Authentication method is SQL Server Authentication
Upon successful database connection and login via virtual network rule, you sill see the following as usual.
On the other hand, if the virtual network rule is not setup as expected, you will see the following error message:
“Your client IP address does not have access to the server. Sign in to an Azure account and create a new firewall rule to enable access.”
At this time of writing, this error message does not refer to virtual network rules but only the firewall rule that point to allowed source client IP addresses. I can verify with my testing, that adding the virtual network rule to allow connectivity from subnet will provide connectivity as previously shown.
Another approach of validating database connectivity is through PowerShell if you are unwilling to install SQL Management Studio for validation purposes.
$ConnectionString = "Server=tcp:rkappdemo-sqldbserver.database.windows.net,1433;Initial Catalog=rkappdemodb;Persist Security Info=False;User ID=<USERID>;Password=<PASSWORD>;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;"
$sqlConnection = New-Object System.Data.SqlClient.SqlConnection $ConnectionString;
Note that validating TCP/IP network connectivity (but not database login connectivity) is successful without any Azure SQL server firewall rule or virtual network rule.
For example, you can telnet and get login prompt via SQL Management Studio. Also you can test network connectivity with Azure Network Watcher but without a virtual network rule, you can’t connect into the database server to execute SQL statements.
Hope this blog posts was informative about database connectivity and login validation using virtual network rules and service endpoints.