Menu
Amazon Relational Database Service
User Guide (API Version 2014-10-31)

Connecting to a DB Instance Running the Microsoft SQL Server Database Engine

After Amazon RDS provisions your DB instance, you can use any standard SQL client application to connect to the DB instance. In this topic you connect to your DB instance by using either Microsoft SQL Server Management Studio (SSMS) or SQL Workbench/J.

For an example that walks you through the process of creating and connecting to a sample DB instance, see Creating a Microsoft SQL Server DB Instance and Connecting to a DB Instance.

Connecting to Your DB Instance with Microsoft SQL Server Management Studio

In this procedure you connect to your sample DB instance by using Microsoft SQL Server Management Studio (SSMS). To download a stand-alone version of this utility, see Download SQL Server Management Studio (SSMS) in the Microsoft documentation.

To connect to a DB Instance using SSMS

  1. Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/.

  2. In the top right corner of the Amazon RDS console, select the region of your DB instance.

  3. Find the DNS name and port number for your DB Instance.

    1. Open the RDS console and then choose Instances to display a list of your DB instances.

    2. Choose the row for your SQL Server DB instance to display the summary information for the instance.

      
                                Locate DB Instance endpoint and port
    3. Copy the endpoint. The Endpoint field has two parts separated by a colon (:). The part before the colon is the DNS name for the instance, the part following the colon is the port number. Copy both parts.

  4. Start SQL Server Management Studio.

    The Connect to Server dialog box appears.

    
                        Connect to Server dialog
  5. Provide the information for your DB instance.

    1. For Server type, choose Database Engine.

    2. For Server name, type or paste the DNS name and port number of your DB Instance, separated by a comma.

      Important

      Change the colon between the DNS name and port number to a comma.

      For example, your server name should look like the following:

      sample-instance.cg034hpkmmjt.us-east-1.rds.amazonaws.com,1433
    3. For Authentication, choose SQL Server Authentication.

    4. For Login, type the master user name for your DB instance.

    5. For Password, type the password for your DB instance.

  6. Choose Connect.

    After a few moments, SSMS connects to your DB instance. If you can't connect to your DB instance, see Security Group Considerations and Troubleshooting the Connection to Your SQL Server DB Instance.

  7. Your SQL Server DB instance comes with SQL Server's standard built-in system databases (master, model, msdb, and tempdb). To explore the system databases, do the following:

    1. In SSMS, on the View menu, choose Object Explorer.

    2. Expand your DB instance, expand Databases, and then expand System Databases as shown following.

      
                                Object Explorer displaying the system databases
  8. Your SQL Server DB instance also comes with a database named rdsadmin. Amazon RDS uses this database to store the objects that it uses to manage your database. The rdsadmin database also includes stored procedures that you can run to perform advanced tasks. For more information, see Common DBA Tasks for Microsoft SQL Server.

  9. You can now start creating your own databases and running queries against your DB instance and databases as usual. To run a test query against your DB instance, do the following:

    1. In SSMS, on the File menu point to New and then choose Query with Current Connection.

    2. Type the following SQL query:

      Copy
      select @@VERSION
    3. Run the query. SSMS returns the SQL Server version of your Amazon RDS DB instance.

      
                                SQL Query Window

Connecting to Your DB Instance with SQL Workbench/J

This example shows how to connect to a DB instance running the Microsoft SQL Server database engine by using the SQL Workbench/J database tool. To download SQL Workbench/J, see SQL Workbench/J.

SQL Workbench/J uses JDBC to connect to your DB instance. You also need the JDBC driver for SQL Server. To download this driver, see Microsoft JDBC Drivers 4.1 (Preview) and 4.0 for SQL Server.

To connect to a DB instance using SQL Workbench

  1. Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/.

  2. In the top right corner of the Amazon RDS console, select the region of your DB instance.

  3. Find the DNS name and port number for your DB Instance.

    1. Open the RDS console and then choose Instances to display a list of your DB instances.

    2. Choose the row for your SQL Server DB instance to display the summary information for the instance.

      
                                Locate DB Instance endpoint and port
    3. Copy the endpoint. The Endpoint field has two parts separated by a colon (:). The part before the colon is the DNS name for the instance, the part following the colon is the port number. Copy both parts.

  4. Open SQL Workbench/J. The Select Connection Profile dialog box appears, as shown following:

    
                        Select Connection Profile dialog
  5. In the first box at the top of the dialog box, enter a name for the profile.

  6. For Driver, select SQL JDBC 4.0.

  7. For URL, type jdbc:sqlserver://, then type or paste the endpoint of your DB instance. For example, the URL value could be the following:

    Copy
    jdbc:sqlserver://sqlsvr-pdz.abcd12340.us-west-2.rds.amazonaws.com:1433
  8. For Username, type or paste the master user name for the DB instance.

  9. For Password, type the password for the master user.

  10. Choose the save icon in the dialog toolbar, as shown following:

    
                        Save the profile
  11. Choose OK. After a few moments, SQL Workbench/J connects to your DB instance. If you can't connect to your DB instance, see Security Group Considerations and Troubleshooting the Connection to Your SQL Server DB Instance.

  12. In the query pane, type the following SQL query:

    Copy
    select @@VERSION
  13. Choose the execute icon in the toolbar, as shown following:

    
                        Execute the query

    The query returns the version information for your DB instance, similar to the following:

    Copy
    Microsoft SQL Server 2012 - 11.0.2100.60 (X64)

Security Group Considerations

To connect to your DB instance, your DB instance must be associated with a security group that contains the IP addresses and network configuration that you use to access the DB instance. You may have associated your DB instance with an appropriate security group when you created your DB instance. If you assigned a default, non-configured security group when you created your DB instance, your DB instance firewall prevents connections.

If you need to create a new security group to enable access, the type of security group that you create will depend on what Amazon EC2 platform your DB instance is on. To determine your platform, see Determining Whether You Are Using the EC2-VPC or EC2-Classic Platform. In general, if your DB instance is on the EC2-Classic platform, you create a DB security group; if your DB instance is on the VPC platform, you create a VPC security group. For instructions on creating a new security group, see Amazon RDS Security Groups.

After you have created the new security group, you modify your DB instance to associate it with the security group. For more information, see Modifying a DB Instance Running the Microsoft SQL Server Database Engine.

You can enhance security by using SSL to encrypt connections to your DB instance. For more information, see Using SSL with a Microsoft SQL Server DB Instance.

Troubleshooting the Connection to Your SQL Server DB Instance

The following are issues you might encounter when you attempt to connect to your SQL Server DB instance.

Issue Troubleshooting Suggestions

Unable to connect to your DB instance.

For a newly-created DB instance, the DB instance has a status of creating until the DB instance is ready to use. When the state changes to available, you can connect to the DB instance. Depending on the DB instance class and the amount of storage, it can take up to 20 minutes before the new instance is available.

Unable to connect to your DB instance.

If you can't send or receive communications over the port that you specified when you created the DB instance, you can't connect to the DB instance. Check with your network administrator to verify that the port you specified for your DB instance allows inbound and outbound communication.

Unable to connect to your DB instance.

The access rules enforced by your local firewall and the IP addresses you authorized to access your DB instance in the security group for the DB instance might not not match. The problem is most likely the egress or ingress rules on your firewall. For more information about security groups, see Amazon RDS Security Groups.

For a topic that walks you through the process of setting up rules for your security group, see Tutorial: Create an Amazon VPC for Use with an Amazon RDS DB Instance.

Could not open a connection to SQL Server – Microsoft SQL Server, Error: 53

Make sure specified the server name correctly. For Server name, type or paste the DNS name and port number of your sample DB Instance, separated by a comma.

Important

Change the colon between the DNS name and port number to a comma.

For example, your server name should look like the following:

sample-instance.cg034hpkmmjt.us-east-1.rds.amazonaws.com,1433

No connection could be made because the target machine actively refused it – Microsoft SQL Server, Error: 10061

You were able to reach the DB instance but the connection was refused. This is usually caused by specifying the user name or password incorrectly. Verify the user name and password and then retry.

Related Topics