ApsaraDB RDS:Connect to an SQL Server instance

Method 1: Use DMS

Data Management Service (DMS) is a one-stop data management platform that supports the entire data lifecycle. It provides features such as global data asset management, data governance, database design and development, data integration, data development, and data consumption. DMS helps enterprises efficiently and securely unlock the value of their data and accelerates their digital transformation.

You can use DMS to quickly and easily log on to an ApsaraDB for SQL Server instance to manage and use data. This method eliminates the need to configure an IP address whitelist or select a connection type for the instance.

1. Go to the Instances page. In the top navigation bar, select the region in which the RDS instance resides. Then, find the RDS instance and click the ID of the instance.

2. Click Log On to Database to open the DMS logon page.

3. In the Log on to Database Instance dialog box, enter the logon information and click Log In.

   • Select an Access Mode. This tutorial uses Account + password login as an example.

   • Enter the Database Account and Database Password. This tutorial uses the privileged account testuser and a custom password.

   • Select a Control Mode. This tutorial uses Flexible Management as an example.

     Note

     • Flexible Management is free of charge. Stable Change and Security Collaboration incur fees.

     • Compared to the Flexible Management control mode, Stable Change and Security Collaboration offer more advanced database management features. If you are trying out ApsaraDB for SQL Server, we recommend selecting the Flexible Management mode.

4. After you log on, the newly created database appears under Instances Connected in the left navigation pane of the DMS page. This tutorial uses the dbtest database as an example. You can also double-click another database to switch to it.

   Note

   If the instance exists but the target database is not found in the list of connected instances in DMS, the cause may be one of the following:

   • The logon account does not have permissions to access the target database: Go to the Accounts page of the ApsaraDB RDS instance, find the target account, and click Change Permissions in the Actions column to grant the required permissions.

   • The directory is not displayed because the metadata is not synchronized: Hover the pointer over the instance that contains the target database and click the icon to the right of the instance name. This refreshes the list to display the target database.

5. After logging on to the database in DMS, use the SQL Console tab to create databases and tables, and to query and modify data.

Method 2: Use SSMS

Microsoft SQL Server Management Studio (SSMS) is a graphical user interface (GUI) tool for managing and administering SQL Server. You can use it to connect to different SQL Server databases, such as ApsaraDB for SQL Server instances, on-premises SQL Server instances, or SQL Server instances hosted on other clouds.

This tutorial demonstrates how to connect to an ApsaraDB for SQL Server instance by using Microsoft SQL Server Management Studio 19.0 (SSMS).

1. Open your local Microsoft SQL Server Management Studio (SSMS) 19.0 client.

2. Select Connection > Database Engine.

3. In the Connect to Server dialog box, enter the logon information.

   Parameter	Value	Description
   Server name	rm-2ze****.rds.aliyuncs.com,1433	The endpoint and port number of the ApsaraDB RDS instance. Enter the Public Address and Outside the network port that you obtained when you applied for a public endpoint. Separate the endpoint and port number with a comma (,).
   Authentication	SQL Server Authentication	The authentication method for SQL Server.
   Username	testuser	The username for the ApsaraDB RDS instance account.
   Password	Test_pw123	The password for the ApsaraDB RDS instance account.

4. Click Connection.

   After a successful connection, SSMS displays the connection information on the left.

Method 3: Use a Java application

This section describes how to use JDBC to connect to an ApsaraDB for SQL Server instance from a Java application.

1. Add the Microsoft JDBC driver to your Maven project to access a SQL Server database.

   Method 1: Manually add the dependency to the pom.xml file of your Maven project

   Note

   • We recommend that you select a dependency version that corresponds to your Java version. For example, the mssql-jdbc-12.2.0.jre8.jar file in the 12.2.0 package is for Java 8 or later.

   • To view previous versions of the dependency, see the official tutorial.

   <dependency>
     <groupId>com.microsoft.sqlserver</groupId>
     <artifactId>mssql-jdbc</artifactId>
     <version>12.2.0.jre8</version> <!-- Check for the latest version number -->
   </dependency>

   After you add the dependency, click . The dependencies in the pom.xml file are downloaded automatically.

   Method 2: Manually download the JDBC driver and add it to the classpath of your Maven project

   1. Download the driver that matches your Java version from Microsoft JDBC Driver for SQL Server.

      Microsoft JDBC Driver for SQL Server is a Type 4 JDBC driver that provides database connectivity through the standard JDBC application programming interfaces (APIs) available on the Java platform. It allows you to access SQL Server from any Java application, application server, or Java-enabled applet.

   2. After you download and extract the package, manually save the JAR file, such as sqljdbc4.jar or sqljdbc.jar, to your project.

      The following steps demonstrate how to add the driver in IntelliJ IDEA:

      • In IntelliJ IDEA, choose File > Project Structure from the menu bar.

      • In the Project Structure dialog box, choose Project Settings > Modules in the left-side pane, go to the Dependencies tab, click the add icon (+), and then select JARs or Directories.

      • In the file chooser dialog box that appears, locate and select the downloaded JAR file, such as mssql-jdbc-12.2.0.jre8.jar, and click OK.

2. Write sample code to connect to a SQL Server database from Java:

   Replace the endpoint, database name, username, password, and SQL statement in the sample code with your actual values. For more information, see View or modify an endpoint and port number.

   import java.sql.Connection;
   import java.sql.DriverManager;
   import java.sql.SQLException;
   import java.sql.Statement;
   import java.sql.ResultSet;
   
   public class testMSSQLJDBC {
   
       public static void main(String[] args) {
           // Enter the database endpoint. Use the internal endpoint if your application runs on an ECS instance, or the public endpoint if it runs on-premises or in other environments.
           String url = "jdbc:sqlserver://rm-2vc367d081200******.mssql.cn-chengdu.rds.aliyuncs.com:1433;"
                   + "database=YourDatabaseName;"
                   + "encrypt=true;"
                   + "trustServerCertificate=true;"
                   + "loginTimeout=30;";
           // Specify the username and password if you do not use Windows Authentication.
           String username = "usernametest";
           String password = "Passwordtest!";
   
           // Create a connection object.
           Connection connection = null;
   
           try {
               // Load the JDBC driver.
               Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
               // Connect to the SQL Server database.
               connection = DriverManager.getConnection(url, username, password);
               System.out.println("Connection succeeded!");
   
               // Create a Statement object to run SQL statements.
               Statement statement = connection.createStatement();
               // Run a SQL query. Replace the table and column names as needed.
               String sql = "SELECT TOP 10 * FROM YourTableName";
               ResultSet resultSet = statement.executeQuery(sql);
   
               // Process the result set.
               while (resultSet.next()) {
                   System.out.println("Column 1: " + resultSet.getString("YourColumnName1"));
                   System.out.println("Column 2: " + resultSet.getString("YourColumnName2"));
               }
   
               // Close the result set.
               resultSet.close();
               // Close the Statement object.
               statement.close();
           } catch (ClassNotFoundException e) {
               e.printStackTrace();
           } catch (SQLException e) {
               e.printStackTrace();
           } finally {
               // Close the connection.
               if (connection != null) {
                   try {
                       connection.close();
                   } catch (SQLException e) {
                       e.printStackTrace();
                   }
               }
           }
       }
   }

3. Test the connection.

   Save the code as a SqlServerConnection.java file, and compile and run it from the command line or in an IDE. If the configuration is correct, the program prints the following output, which indicates a successful connection to the SQL Server database.

   Connection succeeded!