Unable to connect to AWS database from SSMS

Question

I have created a Microsoft SQL server database with Amazon RDS. I have created a security group with an inbound rule which includes my IP. I modified the DB instance to include that security group. I Copy and pasted the connection endpoint and pasted that into the server name field of Microsoft SQL Server connect window followed by a comma and port number 1433. I use SQL server authentication with the correct credentials. This is the Error I get when I try to connect, What am I missing?

Cannot connect to "copied name of endpoint...."us-east-1.rds.amazonaws.com,1433.

===================================

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - The wait operation timed out.) (.Net SqlClient Data Provider)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=258&LinkId=20476

Error Number: 258 Severity: 20 State: 0

Program Location:

   at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, SqlCredential credential, Object providerInfo, String newPassword, SecureString newSecurePassword, Boolean redirectedUserInstance, SqlConnectionString userConnectionOptions, SessionData reconnectSessionData, DbConnectionPool pool, String accessToken, Boolean applyTransientFaultHandling)
   at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, DbConnectionPoolKey poolKey, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionFactory.TryGetConnection(DbConnection owningConnection, TaskCompletionSource`1 retry, DbConnectionOptions userOptions, DbConnectionInternal oldConnection, DbConnectionInternal& connection)
   at System.Data.ProviderBase.DbConnectionInternal.TryOpenConnectionInternal(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.ProviderBase.DbConnectionClosed.TryOpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory, TaskCompletionSource`1 retry, DbConnectionOptions userOptions)
   at System.Data.SqlClient.SqlConnection.TryOpenInner(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.TryOpen(TaskCompletionSource`1 retry)
   at System.Data.SqlClient.SqlConnection.Open()
   at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateConnection(UIConnectionInfo ci, IServerType server)
   at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

===================================

The wait operation timed out

When you do a nslookup "copied name of endpoint...."us-east-1.rds.amazonaws.com, does it resolve to the public IP address of the database server on AWS? Why not just use the public IP address instead of the DNS name (at least to troubleshoot the connection)? (FYI - I'm not familiar with Amazon RDS, I am familiar with AWS/EC2... Not sure if I'm asking the right questions...) — James L.
When you added your IP to the AWS security group, did you also add TCP port 1433 to it so your SSMS traffic can route to the server? Have you opened port 1433 on the Windows Server Firewall to allow traffic into the server? Have you done a netstat -n -b -a from an elevated CMD window on the server to verify SQL Server is LISTENING on port 1433? Is SQL Server configured to accept TCPIP connections or just Named Pipes? — James L.
Are you trying to run SSMS on your local machine and connect to the DB instance on Amazon? If so, why not simply RDP to the Amazon server and run SSMS locally from the server console instead of trying to connect your local SSMS to the Amazon DB instance? — James L.
RDS is "platform as a service" - there is no such thing as RDP for this, in fact that's part of the objective - to not need to maintain the OS / platform — Nick.McDermaid
@James L. I did a nslookup and used the public IP on port 1433 and got the same error. I did specify port 1433 when I created the rule for the security group. I made a rule with Windows Defender Firewall on my local machine which all traffic is allowed on port 1433, I even turned off my firewall completely and still got the same error. Tried using a hotspot via my cell phone too, same error. My router is not blocking that port either. — Bryan Moore

Bryan Moore Bryan Moore · Accepted Answer · 2018-04-27T18:15:17

Solved,

I modified the DB instance via AWS portal to enable public access, then I added "TrustServerCertificate=True" to the connection string via SSMS connection options. I am able to connect now.

Unable to connect to AWS database from SSMS

1 Answers