Connecting to a DB instance running the Microsoft SQL Server database engine - Amazon Relational Database Service

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 standalone 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 upper-right corner of the Amazon RDS console, choose the AWS Region of your DB instance.

  3. Find the Domain Name System (DNS) name and port number for your DB instance:

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

    2. Choose the SQL Server DB instance name to display its details.

    3. On the Connectivity & security tab, copy the endpoint. Also, note the port number. You need both the endpoint and the port number to connect to the DB instance.

      
                                Locate DB instance endpoint and port
  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, enter 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.

      database-2.cg034itsfake.us-east-1.rds.amazonaws.com,1433
    3. For Authentication, choose SQL Server Authentication.

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

    5. For Password, enter 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 connections 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.

      
                                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. Enter the following SQL query.

      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/J

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

  2. In the upper-right corner of the Amazon RDS console, choose the AWS Region of your DB instance.

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

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

    2. Choose the name of your SQL Server DB instance to display its details.

      
                                Locate DB instance endpoint and port
    3. On the Connectivity tab, copy the endpoint. Also, note the port used by the DB instance.

  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, choose SQL JDBC 4.0.

  7. For URL, enter jdbc:sqlserver://, then enter the endpoint of your DB instance. For example, the URL value might be the following.

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

  9. For Password, enter 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 connections to your SQL Server DB instance.

  12. In the query pane, enter the following SQL query.

    select @@VERSION
  13. Choose the Execute icon in the toolbar, as shown following.

    
                        Run the query

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

    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. This security group contains the IP addresses and network configuration that you use to access the DB instance. You might have associated your DB instance with an appropriate security group when you created your DB instance. If you assigned a default, no-configured security group when you created your DB instance, your DB instance firewall prevents connections.

In some cases, you might need to create a new security group to enable access. If so, the type of security group to create depends 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 Controlling access with security groups. For a topic that walks you through the process of setting up rules for your VPC security group, see Tutorial: Create an Amazon VPC for use with a DB instance.

After you have created the new security group, modify your DB instance to associate it with the security group. For more information, see Modifying an Amazon RDS DB instance.

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 connections to your SQL Server DB instance

The following table shows error messages that you might encounter when you attempt to connect to your SQL Server DB instance. For more information on connection issues, see Can't connect to Amazon RDS DB instance.

Issue Troubleshooting suggestions

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

Make sure that you specified the server name correctly. For Server name, enter the DNS name and port number of your sample DB instance, separated by a comma.

Important

If you have a colon between the DNS name and port number, change the colon to a comma.

Your server name should look like the following example.
sample-instance.cg034itsfake.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 issue is usually caused by specifying the user name or password incorrectly. Verify the user name and password, then retry.

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible... The wait operation timed out – Microsoft SQL Server, Error: 258 The access rules enforced by your local firewall and the IP addresses authorized to access your DB instance might not match. The problem is most likely the inbound rules in your security group.

Your database instance must be publicly accessible. To connect to it from outside of the VPC, the instance must have a public IP address assigned.