Cannot connect to remote SQL Server with SQL Server Management Studio with Visual Studi

Question

I'm developing a small app on my own and I am trying to install a database on a local machine that I will be using for development.

So far I have done all of these things:

1. Install SQL Server Management Studio;
2. Created a server with name (fictious) is MYCPU/SQLEXPRESS
3. Created in it a database named MYPROJECT1and I have set the sizes as best as I could figure out
4. Added in security a login user named test / passwordfor connection tests;
5. SQL Server configuration manager tells me that the SQL Server service is running, the SQL Server agent is stopper and the SQL Server Browser is running;
6. Got the IP of my machine which will be (fictious) 254.254.254.123
7. In windows firewall, I have added an Inbound Rule named SQL Server Port 9999 (9999 being the proper port which is supposed to be listened by the Server);
8. I have a Linksys router and, on this router, in security tab, single port forwarding, I have a Remote Desktop connection with external / internal port routing to port 9999 with ip 254.254.254.123 and it is enabled;
9. I have authorized TCP/IP protocol on my server.

This resumes what I have done as best as I remember as I have tried many things. My objective is to connect to the MYPROJECT1 using a connection string in visual studio; however I do not know how to format this connection string, so I am using a modified connection string took from another project which connects on a remote database which works (but is not located at home):

<add name="MyDatabaseConnection" 
     connectionString="metadata=res://*/Models.Database.MYPROJECT1.csdl|res://*/Models.Database.MYPROJECT1.ssdl|res://*/Models.Database.MYPROJECT1.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=254.254.254.123;initial catalog=MYCPU/SQLEXPRESS\MYPROJECT1;user id=test;password=password;MultipleActiveResultSets=True;App=EntityFramework&quot;" 
     providerName="System.Data.EntityClient" />

However when I try to connect to this database, Visual Studio shows me the following error:

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: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

The system cannot find the file specified

I need help troubleshooting to find the exact cause of the problem and FINALLY connect to my database remotely, this would mean a lot to me. If you have any questions in the process or think that there is information missing, feel free to ask.

Answer 1

I'd begin by testing/troubleshooting my connection string on the local SQL server, so that you're not traversing the router. Once you can connect on the localhost, then you know that your connection string syntax is ok. Then you can deal with the network/router issues.