0

I am using SQL managment studio 2008 running on an Amazon EC2 machine. I am unable to connect to the database in my asp.net application. The EC2 instance has been set to accept connections over the SQL port. I am also able to remote the machine as well as view websites hosted on the server. Listed below is part of the connection string relating to this instance. When the program is ran and this connection string is called, it returns tcp error 0 - no return response. it just times out.

<add name="ProjectServer" connectionString="Data Source=*IP ADDRESS HERE*,1433;Initial Catalog=*Catalog Name*;User ID=IP-0A6ED514\Administrator;"/>

I removed the ip and the catalog name for the example, but I am sure they are correct.

The only thing that I could think may cause an error, is the differences in names between the user id and the server name - the server name is ip-0A6ED514\sharepoint but the user name is ip-0A6ED514\administrator when I log into the sql server manager on the EC2 instance. A password is not used. Not sure if I would need to leave in a blank string for password - also not sure if the difference between server name and user id to log in makes a difference. Any help is appreciated. Thank you.

update - when this connection string is used with out the port, i get tcp provider error 40 - when the port is in there, i get error 0

edit- the sql server is using windows authentication - does this make a difference? Usually I always use SQL server authentication

njj56
  • 1
  • 1
  • 2

2 Answers2

1

When using Windows Authentication, the remote EC2 instance needs to authenticate you against your local PC or domain. Unless you have some kind of VPN or something set up, that won't work.

Switch to using SQL authentication instead. You'll need to specify by user id and password in the connection string.

Make sure port 1433 is open in your security group (preferably only from the IP address that your web app is accessing the database from). Make sure your SQL server is setup to accept TCP/IP network connections.

Your EC2 instance has a public DNS entry like ec2-xx-xx-xx-xx.compute-1.amazonaws.com. Use that as your data source. I'm not sure if you can use the '\sharepoint' part. Try it with and without to see if that works.

Matt Houser
  • 9,709
  • 1
  • 26
  • 25
0

Your connection string should look something like this.

Data Source=ip-0A6ED514\sharepoint;Initial Catalog=myDataBase;Integrated Security=SSPI;

Another option would be to have it look like this

Data Source=.\sharepoint;Initial Catalog=myDataBase;Integrated Security=SSPI;

To use an IP address it should look like this.

Data Source=xxx.xxx.xxx.xxx\sharepoint;Initial Catalog=myDataBase;Integrated Security=SSPI;

For this to work correctly you'll need to have the SQL Browser running on the server, and you'll need to have UDP port 1434 open on the server as well as the TCP port that the SQL instance is running on.

mrdenny
  • 27,074
  • 4
  • 40
  • 68
  • when using the first one, I get instance not specified. The server is on a remote machine over the internet (amazon ec2 machine) - how should the string look if I need to use the ip address to connect since the machine name is not working. – njj56 Apr 05 '12 at 12:19
  • I've updated by answer with the connection string for using an IP address. – mrdenny Apr 05 '12 at 21:11