Connect to an SQL Server instance

Use DMS

Data Management (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. The built-in Database Backup Service (DBS) also provides low-cost and highly reliable backup and recovery capabilities. DMS is designed to help enterprises efficiently and securely unlock the value of their data and accelerate their digital transformation.

You can use DMS to log on to an RDS for SQL Server instance without configuring an IP address whitelist or selecting a connection type.

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 your credentials 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 as an example.

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

     Note

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

     • Unlike the Flexible Management Control Mode, the Stable Change and Security Collaboration modes offer more features and greater control over the database. If you are trying out an RDS for SQL Server instance, we recommend that you select the Flexible Management mode.

4. View the database. After you log on, you can find the new database in the Instances Connected section in the left-side navigation pane of the DMS console. This tutorial uses the dbtest database as an example. You can also double-click other databases to switch between them.

   

   Note

   If the instance exists but the target database is not displayed in the list of connected instances in DMS, the possible causes are:

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

   • The database tree is not displayed because the metadata is out of sync: Move the pointer over the instance to which the target database belongs and click the icon next to the instance name to refresh the database list and display the target database.

5. After you log on to the SQL Server database in DMS, you can create databases, create tables, query data, and modify table data on the SQL Console tab.

Use SSMS

Microsoft SQL Server Management Studio (SSMS) is a graphical user interface (GUI) tool used to manage and administer SQL Server. You can use it to connect to various SQL Server databases, including RDS for SQL Server instances, on-premises SQL Server instances, or SQL Server instances in other clouds.

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

1. Start Microsoft SQL Server Management Studio 19.0 (SSMS) on your local computer.

2. Choose Connection > Database Engine.

3. In the Connect to Server dialog box, enter the credentials.

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

4. Click Connection.

   After a successful connection, the database connection information is displayed in the left-side navigation pane of SSMS.

Use a Java application

This section shows how to connect to an RDS for SQL Server instance from a Java application by using Java Database Connectivity (JDBC).

1. In your Maven project, add the Microsoft JDBC driver for SQL Server.

   Method 1: Manually add the dependency to the pom.xml file

   Note

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

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

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

   

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

   

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

   1. Download a 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 provides access to SQL Server from any Java application, application server, or Java-enabled applet.

   2. After you download and decompress the driver, manually save the JAR file (for example, sqljdbc4.jar or sqljdbc.jar) to your project.

      This tutorial uses IntelliJ IDEA as an example to demonstrate how to add the file:

      

      

      

2. Write sample code to connect to a SQL Server database by using Java, as shown in the following example:

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

   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) {
           // Specify the database endpoint. Use the internal endpoint if your application is deployed on an ECS instance. Use the public endpoint if it is deployed on-premises or in another environment.
           String url = "jdbc:sqlserver://rm-2vc367d081200******.mssql.cn-chengdu.rds.aliyuncs.com:1433;"
                   + "database=YourDatabaseName;"
                   + "encrypt=true;"
                   + "trustServerCertificate=true;"
                   + "loginTimeout=30;";
           // If you do not use Windows Authentication, you must specify a 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 SQL Server database.
               connection = DriverManager.getConnection(url, username, password);
               System.out.println("Connection successful!");
   
               // Create a Statement object to execute SQL statements.
               Statement statement = connection.createStatement();
               // Execute the SQL query. Replace the table name and column names with your actual information.
               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. Compile and run the program from the command line or in an IDE. If the configuration is correct, the program produces output similar to the following, which indicates a successful connection to the SQL Server database.