0

So I am having a problem connecting to my sql server.

I know 100% its working and accepting connections, but for some reason I keep getting an error.

I can browse the server just fine in Server Browser, and If I bind the datasource to a datagrid, I can preview the table as well. So I know it's working fine inside VS, but not runtime.

From my knowledge and experience, if VS can connect to the server and display all the good stuff, then it should be able to do it during runtime??

Any help and advice is much appreciated.

Non Duplicate Reason The reason I believe my question is not a duplicate, is because my application is not a ASP or an MVC app. It's a winforms application. So many of the suggestions do not work for me. I have written 2 c# programs now (one wpf and one winform) to access our SAP database and they work just fine. The SAP database is located on the same server as the one I am trying to connect to. I can browse the server perfectly fine in SQL-Server Management Studio on my machine. I can also browse the server perfectly fine in Visual Studios server browser. But When I debug the program, it halts with this error.

My Connection code

public string connectionString = @"Data Source=ENGSRV\SIGMANEST;Initial Catalog=SNDBase10;Persist Security Info=True;User ID=XXXXX;Password=XXXXX";
private void btnSearch_Click(object sender, EventArgs e)
{
    SqlConnection connection = new SqlConnection(connectionString);
    SqlCommand cmd = new SqlCommand();

    try
    {
        try
        {
              connection.Open();
              MessageBox.Show("Connected");
        }
        catch (Exception ex)
        {
                    throw;
                    //MessageBox.Show(ex.Message, "Database Error!");
        }
   }
   catch (Exception ex)
   {
         throw;
         //MessageBox.Show(ex.Message, "Connection Error!");
    }
}

Error Code

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in ProductionOrderQuery.exe

Additional information: 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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

Server Explorer Screen Shot

Table Data Rows Screen Shot

Update: So after thinking about it tonight, I believe it has to do with my firewall and nothing to do with the sql server or the firewall on the server. Here's why, My VB.Net application that can successfully connect to the sql server is not a standalone application. It's a plugin for a CAD software called Rhinocreos 5. All our CAD Designers have 0 problems pulling data from the sql server from within Rhino... Visual Studio can connect with 0 problems in the Server Explorer... Which now leaves me to believe that the application(plugin), is piggy-backing of a firewall rule Rhinocreos 5 made during its installation. If I change the connection string to the one I posted here, it connects perfectly. But it will not find the sql server instance in my standalone application.

That being said, I guess I need to research on how to promaticaly add my program to the clients firewall rules to access the network.

Thanks to everyone that has helped my solve this. If anyone has any tips or pointers to allowing my program through firewalls on any client it's installed on, let me know.

Alan Spurlock
  • 107
  • 1
  • 11
  • 1
    Firewall on your development PC blocking your app but not a known app like Visual Studio? – Equalsk Oct 18 '16 at 12:21
  • Maybe, but I have written 2 programs now that access the sql-server already. Different databases, but same server and didn't have a problem with those. They were SAP databases, but still on the same server nonetheless. – Alan Spurlock Oct 18 '16 at 12:26
  • Is Named Pipes enabled? – SS_DBA Oct 18 '16 at 12:29
  • Possible duplicate of [A network-related or instance-specific error occurred while establishing a connection to SQL Server](http://stackoverflow.com/questions/1391503/a-network-related-or-instance-specific-error-occurred-while-establishing-a-conne) – Igor Oct 18 '16 at 12:30
  • I have checked that and listed why I believe it's not a duplicate. If you have a chance, re-read what I added above. – Alan Spurlock Oct 18 '16 at 13:08
  • Maybe a firewall in your client prevent such outgoing connection while it allows such connection for visual studio (based on your setting or a predefined configurations.) Try turning off firewalls just for test. – Reza Aghaei Oct 18 '16 at 13:21
  • Also since the `ENGSRV` is the same server that your application is running on, instead of `ENGSRV` try `.` in connection string. – Reza Aghaei Oct 18 '16 at 13:24
  • I have turned off my firewall I still receive this error. Why can I browse it in SQL Server Management Studio, but not connect to it through my application? – Alan Spurlock Oct 18 '16 at 13:26
  • Did you make sure that the client hase `enable TCP/IP protocol for port 1433` configured correctly? See answer in possible duplicate by `MacGyver`. Also check your Sql Native Client Configuration -> Client Protocols and the order they are in. – Igor Oct 18 '16 at 13:31
  • I will try that.... our IT guy is TOUGH to work with. So I'll see if he'll let me. I know he'll say "You never had a problem before" and "You can connect to it with SQL Management Studio right?". – Alan Spurlock Oct 18 '16 at 13:40
  • `Sql Native Client Configuration -> Client Protocols` is listed on the client pc, not the server. So that part should be easy to check without help from the IT guy. – Igor Oct 18 '16 at 13:53
  • Everything checks out. But I am getting somewhere. In my VB.NET application, I can connect to it just fine, but in my C# application, I get this error... I maybe using the wrong DLL Reference. using System.Data.SqlClient; – Alan Spurlock Oct 18 '16 at 14:11
  • That's correct, SqlConnection is what you should be using. I would double check the connection string one more time in your c# app, copy paste it from your vb.net app just to be sure. – Igor Oct 18 '16 at 14:56
  • If I copy the connection string from my VB.NET program to my C# program, I still get the same error. If I try to connect to our SAP server from C# I get the error, but in VB.NET it connects just fine.... I am completely out of ideas. – Alan Spurlock Oct 18 '16 at 20:22
  • Ok, I finally give up. I can open and list the tables in a gridview in my VB.NET application with no errors.... Does it have anything to do with the ENGSRV\SIGMANEST? I have to throw in a @ for the string to see the \ – Alan Spurlock Oct 18 '16 at 20:59

1 Answers1

0

Problem was within our network.

Our IT guy decided to have all our computers "My Documents" on a separate server and NOT on our own machines.

So Visual Studio by default saved my project to this server and not to my own computer. This was causing the block to the SQL Server.

I solved this by simply moving my project to my actual documents folder on my computer.

Thanks everyone for the help and pointers!

Alan Spurlock
  • 107
  • 1
  • 11