How To Validate an Azure SQL Database Connection Using Virtual Network Rules

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.VnetIntegration1

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.
ValidateVnetIntegration2

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
ValidateVnetIntegration3

Upon successful database connection and login via virtual network rule, you sill see the following as usual.
ValidateVnetIntegration4

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.”ValidateVnetIntegration6.png
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;
$sqlConnection.Open();
write-host $sqlConnection.Database
$sqlConnection.ClientConnectionId
$sqlConnection.Close()

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.

ValidateVnetIntegration5

Hope this blog posts was informative about database connectivity and login validation using virtual network rules and service endpoints.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s