ApsaraDB RDS:Connect to an RDS for SQL Server instance

Method 1: Use DMS to connect to the RDS instance

DMS is a one-stop data management platform that allows you to manage data throughout its lifecycle. You can use DMS to manage global data assets, govern data, design and develop databases, integrate data, develop data, and consume data. These features help enterprises obtain value from data in an efficient and secure manner and facilitate the digital transformation of enterprises. For more information, see What is DMS?

You can use DMS to log on to your RDS instance to manage and use data without the need to configure IP address whitelists or connection types for the RDS 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. On the page that appears, click Log On to Database.

   

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

   

   • Configure the Access Mode parameter. In this topic, Account + password login is selected.

   • Configure the Database Account and Database Password parameters. In this topic, a privileged account named testuser and a custom password are used.

   • Configure the Control Mode parameter. In this topic, Flexible Management is selected.

     Note

     • If your RDS instance is managed in Flexible Management mode, you can use DMS to manage the instance free of charge. If your RDS instance is managed in Stable Change or Security Collaboration, fees are generated.

     • Compared with the Flexible Management mode, the Stable Change and Security Collaboration modes provide more features and enhanced database management capabilities. If your RDS instance is for trial use, we recommend that you select Flexible Management.

4. View the database. In the left-side navigation pane of the DMS console, choose Database Instances > Instances Connected to view the database that you create. In this topic, the database named dbtest is displayed. You can also double-click a database.

5. After you use DMS to connect to the RDS instance, perform the required operations on the SQL Console tab based on your business requirements. The operations include creating databases and tables and querying or modifying table data.

Method 2: Use the SSMS client to connect to the RDS instance

Microsoft SQL Server Management Studio (SSMS) client is a GUI tool that enables you to manage and handle SQL Server databases. SSMS can be used to connect to different SQL Server databases such as RDS instances, on-premises SQL Server instances, and instances that run SQL Server in other clouds. For more information, see Download SQL Server Management Studio (SSMS).

This section provides an example on how to use SSMS 19.0 to connect to an RDS instance.

1. Start the SSMS 19.0 client.

2. Choose Connect > Database Engine.

3. In the Connect to Server dialog box, configure the parameters that are required for the logon.

   Parameter	Example value	Description
   Server name	rm-2ze****.rds.aliyuncs.com,1433	The endpoint and port number of the RDS instance. Enter the public endpoint and public port number obtained when you apply for the public endpoint. Separate the endpoint and port number with a comma (,).
   Authentication	SQL Server Authentication	The authentication mode of SQL Server.
   Username	testuser	The username of the account for the RDS instance.
   Password	Test_pw123	The password of the account for the RDS instance.

4. Click Connect.

   After the connection is successful, the connection information is displayed on the left side of SSMS.

Method 3: Use a Java application to connect to an RDS instance

This section describes how to connect a Java application to the RDS instance by using Java Database Connectivity (JDBC).

1. Add the JDBC driver to the Maven project to connect to the RDS instance.

   Method 1: Add the dependency to the pom.xml file of the Maven project

   Note

   • We recommend that you select the version of the dependency based on the Java version. For example, the mssql-jdbc-12.2.0.jre8.jar file in the 12.2.0 package is used with Java 8 or later.

   • For more information about how to view the historical versions of the dependency, see Official documentation.

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

   

   After the dependency is added, click the icon. The dependency in the pom.xml file is automatically downloaded.

   

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

   1. Download the JDBC driver that matches your Java version. For more information, see Download Microsoft JDBC Driver for SQL Server.

      The Microsoft JDBC Driver for SQL Server is a Type 4 JDBC driver that provides database connectivity by using the standard JDBC APIs available on the Java platform. The Microsoft JDBC Driver for SQL Server provides access to SQL Server from any Java application, application server, or Java-enabled applet.

   2. Download and decompress the package. Then, save the JAR file, such as sqljdbc4.jar or sqljdbc.jar, to the project.

      In this example, IntelliJ IDEA is used to describe how to save a file to the project.

      

      

      

2. Compile the sample code to connect to an RDS instance by using Java.

   You must use the actual endpoint, database name, username, password, and SQL statement. For more information, see View and change the endpoints and port numbers.

   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 endpoint of the RDS instance. If the application is deployed on an ECS instance, enter the internal endpoint. If the application is deployed on an on-premises device or in other environments, enter the public endpoint.
           String url = "jdbc:sqlserver://rm-2vc367d081200******.mssql.cn-chengdu.rds.aliyuncs.com:1433;"
                   + "database=YourDatabaseName;"
                   + "encrypt=true;"
                   + "trustServerCertificate=true;"
                   + "loginTimeout=30;";
           // Enter the username and password. If you do not use Windows authentication, you must specify the username and password.
           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");
               // Establish a connection to the RDS instance.
               connection = DriverManager.getConnection(url, username, password);
               System.out.println("Connection succeeded!");
   
               // Create a Statement object to execute SQL statements.
               Statement statement = connection.createStatement();
               // Run the SQL query. You must use the actual table name and column name.
               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 whether the connection is successful.

   Save the modified code to the SqlServerConnection.java file. Then, compile and run the application in the command line or integrated development environment (IDE). If the configuration is correct, the following result is generated, and the application is connected to the RDS instance.