Getting started Azure SQL Database: Connect with Java client and Management studio

Microsoft Azure SQL Database is the cloud version of Microsoft SQL Server, an on-premises relational database engine platform. Azure SQL database is part Software as a Service stack - an alternative to on-premise SQL server. One of the most attractive aspects of Microsoft Azure SQL Database is that it shares virtually the same code lines and exposes the same tabular data stream (TDS) as on-premises Microsoft SQL Server.
In this post we will see how to get started with Azure SQL database. We will start with Configuration of SQL database instance followed by connecting with Java client and at the end we will connect it with Management studio.

Configure Azure SQL Database:-
Before getting started with configuration make sure you have Azure account.Trial for 1 month is available free 

1. Create a database instance in Azure. Click on SQL Database icon and provide basic details as shown in following screenshot. Provide unique server name - as this name is signature of database we are creating. Select pricing tier - standard to avoid penalty of excessive usages.

Create  database instance in Azure SQL Database 

2. Ideally we have created SQL Database instance and we should able to access it from java Client or management studio. However, for security purpose we need to provide access to client.
i.e: Provide IP address details to Azure SQL database - who are white list users who should be allowed to access this database. So we need to configure server firewall.
Click on Set server Firewall and add IP range or Just click on "Add client IP" and it will add your IP address in white list of server.

3. Now we are ready to connect this database from Java/C# client. In order to connect a client to this database - Azure provide connection string. Click on "Show database connections strings". It will list down connections string for various client - ADO.Net, JDBC,OBDC,PHP,
Go to JDBC tab and copy connection string for your Database. We will use this string as it is in Java client.Below is sample JDBC string :

jdbc:sqlserver://nikdevinline.database.windows.net:1433;database=NIkDB;user=zytham@nikdevinline;password={your_password_here}; encrypt=true;trustServerCertificate=false; hostNameInCertificate=*.database.windows.net;loginTimeout=30;

4. Sample Java program to connect Azure SQL Database.

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author devinline
 */
public class Azure {7000
    public static void main(String[] args) throws ClassNotFoundException, SQLException {
        Connection conn = null;
        String url = "jdbc:sqlserver://nikdevinline.database.windows.net:1433;
  database=NIkDB;user=zytham@nikdevinline;password=<Password_HERE>;encrypt=true;
  trustServerCertificate=false;
  hostNameInCertificate=*.database.windows.net;loginTimeout=30";
        conn = DriverManager.getConnection(url);
        if (conn != null) {
            DatabaseMetaData dm = (DatabaseMetaData) conn.getMetaData();
            System.out.println("Driver name: " + dm.getDriverName());
            System.out.println("Driver version: " + dm.getDriverVersion());
            System.out.println("Product name: " + dm.getDatabaseProductName());
            System.out.println("Product version: " + dm.getDatabaseProductVersion());
        }
        String SPsql = "select * from SalesLT.Address";
        Statement stmt = conn.createStatement();
        ResultSet result = null;

        try {
            result = stmt.executeQuery(SPsql);
            if (result != null) {
                System.out.println("Received results:");
                printResult(result);
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        } finally {
            try {
                if (!conn.isClosed()) {
                    conn.close();
                }
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
        }
    }

    private static void printResult(ResultSet rs) {
        int currRow = 1;
        try {

            ResultSetMetaData meta = rs.getMetaData();
            int colCount = meta.getColumnCount();
            printHeader(meta);
            while (rs.next()) {
                System.out.print("Row " + currRow++ + ":\n\t");

                for (int col = 1; col <= colCount; col++) {
                    Object value = rs.getObject(col);
                    if (value != null) {
                        System.out.print(rs.getString(col));
                    }
                    if (col < colCount) {
                        System.out.print(",");
                    }
                }
                System.out.println("");
            }
        } catch (SQLException ex) {
            
        }
    }

    private static void printHeader(ResultSetMetaData meta) {
        System.out.print("Header:\n\t");
        try {

            int colCount = meta.getColumnCount();
            for (int ii = 1; ii <= colCount; ++ii) {
                System.out.print(meta.getColumnName(ii));
                if (ii < colCount) {
                    System.out.print(",");
                }
            }
            System.out.println("");
        } catch (Exception e) {
        }

    }
}

5. When we execute above sample program we get results from table SalesLT.Address(this table is part of source sample AdvantureWorksLT) provided by Azure.
Sample Output:-
Driver name: Microsoft JDBC Driver 4.0 for SQL Server
Driver version: 4.0.4621.201
Product name: Microsoft SQL Server
Product version: 12.00.100
Received results:
Header:
AddressID,AddressLine1,AddressLine2,City,StateProvince,CountryRegion,PostalCode,rowguid,ModifiedDate
Row 1:
9,8713 Yosemite Ct.,,Bothell,Washington,United States,98011,268AF621-76D7-4C78-9441-144FD139821A,2006-07-01 00:00:00.0
Row 2:
11,1318 Lasalle Street,,Bothell,Washington,United States,98011,981B3303-ACA2-49C7-9A96-FB670785B269,2007-04-01 00:00:00.0
Row 3:
25,9178 Jumping St.,,Dallas,Texas,United States,75201,C8DF3BD9-48F0-4654-A8DD-14A67A84D3C6,2006-09-01 00:00:00.0
Row 4:
28,9228 Via Del Sol,,Phoenix,Arizona,United States,85004,12AE5EE1-FC3E-468B-9B92-3B970B169774,2005-09-01 00:00:00.0

Note:- 

If we do not add client IP address as part of Server Firewall configuration(Step-2), then on executing above program we get an error like: 

Exception in thread "main" com.microsoft.sqlserver.jdbc.SQLServerException: Cannot open server 'nikdevinline' requested by the login. Client with IP address '39.105.43.xxx' 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. ClientConnectionId:23794f95-545b-4d6d-8997-d868b8996df8

Microsoft Management Studio with Azure Database :-

Connection with Management studio is very straight forward. Just we need to provide server detail , username and password.Refer following screenshot .