Cannot connect Amazon RDS SQL Instance with SSMS

• bri9039

  SSC Enthusiast

  Points: 186

  More actions

  October 3, 2018 at 10:03 am

  #373316

  Hello everyone,

  I apologize in advance if this is not posted in the correct forum. I'm desperate for some help here. The issue seems to be above my knowledge level at this point.
  I am a college student working in a group with 4 other students on a project where we have created an SQL Server database using Amazon AWS free tier RDS service.

  This is an online class and my group partners do not live in the same area as me and we all need access to this server to be able to make changes as needed.
  I am learning much of this on the fly as we weren't given really any instruction for how to host our server for this project, so I've created the RDS Instance on AWS and provided the server information and credentials. I am able to connect and access this from SQL Server Management Studio and Visual Studio on my computer and have been able to add data to our tables, etc.
  2 of my other group members have also been able to connect to our server and access the database instance without issue.

  However, 1 of my group members is not able to connect from his SSMS or Visual Studio and we cannot figure out why.

  When he attempts to connect, this is the error that he receives:

  "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 the SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, Error: 40 - Could not open a connection to SQL Server)(Microsoft SQL Server, Error: 53)"

  So far, I have confirmed that he is entering the server name exactly as it has been provided along with the login credentials. I have verified that the Server allows remote access. I've made sure that the DB instance allows public access on the AWS console. I've had him check to make sure port 1433 isn't restricted on his computer. 
  Nothing has changed.

  The other two group members that have connected have also been using the AWS Toolkit for Visual Studio which displays the Amazon RDS databases that you can add to server explorer, so I had him try that and it seemed to connect, but when he tried to view our database table, he received an error that the SELECT permission was denied on an object on the 'master' database, which I believe is a database that RDS creates automatically, but that wasn't what he was trying to view so I have no clue what that error message means.
  He does not need to be able to connect with the AWS Toolkit, but he does need to be able to connect to this server on his Visual Studio/SSMS.

  I created a new test instance on our AWS console just to make sure that I didn't change any settings on the console with our first instance, but he had the same result.

  Please keep in mind that this is happening on a computer that isn't available to me since this is strictly an online class and our group members do not live near each other. 
  I've spent hours Googling the issue and looking for solutions but I have yet to find this scenario where the connection works on 3 computers but does not work on 1, and the suggestions produced from those searches have not helped.

  Does anyone have any suggestions that I could have him try? I'd truly appreciate it.We cannot afford to lose more time on this.
  Apologies if my terminology or understanding isn't the same level as some of you - I am still learning and do not have much experience with SQL servers beyond my local connection.

  Thanks

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  October 3, 2018 at 2:02 pm

  #2007950

  bri9039 - Wednesday, October 3, 2018 10:03 AM

  Hello everyone,

  I apologize in advance if this is not posted in the correct forum. I'm desperate for some help here. The issue seems to be above my knowledge level at this point.
  I am a college student working in a group with 4 other students on a project where we have created an SQL Server database using Amazon AWS free tier RDS service.

  This is an online class and my group partners do not live in the same area as me and we all need access to this server to be able to make changes as needed.
  I am learning much of this on the fly as we weren't given really any instruction for how to host our server for this project, so I've created the RDS Instance on AWS and provided the server information and credentials. I am able to connect and access this from SQL Server Management Studio and Visual Studio on my computer and have been able to add data to our tables, etc.
  2 of my other group members have also been able to connect to our server and access the database instance without issue.

  However, 1 of my group members is not able to connect from his SSMS or Visual Studio and we cannot figure out why.

  When he attempts to connect, this is the error that he receives:

  "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 the SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, Error: 40 - Could not open a connection to SQL Server)(Microsoft SQL Server, Error: 53)"

  So far, I have confirmed that he is entering the server name exactly as it has been provided along with the login credentials. I have verified that the Server allows remote access. I've made sure that the DB instance allows public access on the AWS console. I've had him check to make sure port 1433 isn't restricted on his computer. 
  Nothing has changed.

  The other two group members that have connected have also been using the AWS Toolkit for Visual Studio which displays the Amazon RDS databases that you can add to server explorer, so I had him try that and it seemed to connect, but when he tried to view our database table, he received an error that the SELECT permission was denied on an object on the 'master' database, which I believe is a database that RDS creates automatically, but that wasn't what he was trying to view so I have no clue what that error message means.
  He does not need to be able to connect with the AWS Toolkit, but he does need to be able to connect to this server on his Visual Studio/SSMS.

  I created a new test instance on our AWS console just to make sure that I didn't change any settings on the console with our first instance, but he had the same result.

  Please keep in mind that this is happening on a computer that isn't available to me since this is strictly an online class and our group members do not live near each other. 
  I've spent hours Googling the issue and looking for solutions but I have yet to find this scenario where the connection works on 3 computers but does not work on 1, and the suggestions produced from those searches have not helped.

  Does anyone have any suggestions that I could have him try? I'd truly appreciate it.We cannot afford to lose more time on this.
  Apologies if my terminology or understanding isn't the same level as some of you - I am still learning and do not have much experience with SQL servers beyond my local connection.

  Thanks

  Did you go through the troubleshooting article by AWS:
  How do I resolve problems connecting to my Amazon RDS database instance?

  The first error (53) is what it says - it can't find the instance. And if you follow the troubleshooting article in the link, if you test the connectivity and that is fine it is likely with the user authentication. And that is what your second error indicates. So I'd be inclined to guess it's along those lines with the user, how it's configured or how they are connecting. It looks like you may have gotten an error related to the default database the user has when connecting - do all users have the same default database, permissions, etc?
  And in SSMS, click on the Options tab when the login window pops up. There are additional settings you can use to compare with the connections that work and don't work.

  Sue