1

I am developing a program with C# and WPF. I want the data to be stored in an SQL Server database. I made a connection string with the instance name in my PC, and that worked. But when I want to connect through the Internet with an IP address, I get some errors:

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. (TCP Provider, error 0 - No connection could be made because the target machine actively refused it.)

I enabled TCP/IP, allowed remote connection in SQL Configuration Manager, opened a port of 1433 in my firewall, but still I am getting this error.

My connection string is this:

String connString = @"Network Library=dbmssocn;
                     Network Address=127.0.0.1,1433;
                     Integrated security=SSPI;
                     Initial Catalog=db";

SqlConnection conn = new SqlConnection(connString);
conn.Open( );

Where is my mistake?

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
Leo Mujaj
  • 85
  • 1
  • 12
  • 1
    You are using port 49164 not 1433 – Steve Apr 09 '16 at 13:20
  • i Tried also 1433 port, but still this error. So i tried dynamic TCP port, but also still getting this error – Leo Mujaj Apr 09 '16 at 13:24
  • Why dont you try to switch the firewall off to see if that resolves and narrow down the problem? If it is we can work on configuring it? – Computer Apr 09 '16 at 13:31
  • I have read in intenernet that the antivirus or firewall may be the problem. I turned both of them of, but still the same error – Leo Mujaj Apr 09 '16 at 13:45
  • I think you have to use Data Source=127.0.0.1,1433; instead of Network Address. – dotnetstep Apr 09 '16 at 16:03
  • Which part of this is WPF related? I mean in your CODE. The same problem would happen in a command line app -> ergo a false tag. – TomTom Apr 09 '16 at 17:30
  • And - why TCP? Why force TCP on a local connection that runs more efficient over named pipe? – TomTom Apr 09 '16 at 17:32
  • Check this answer to make sure that you did all of the steps. I think you need to restart your sql server after this settings: http://stackoverflow.com/a/36307003/2946329 – Salah Akbari Apr 09 '16 at 17:35
  • Tomtom i put the wpf tag, because the program is developing with wpf, an i think the problem is with connection string , because in sql i enable tcp/ip, allowed remote control, starte sql browser. also i want to put tcp ip instead of instance name, cause i want to use this program from another pc, not in my pc – Leo Mujaj Apr 09 '16 at 17:43

2 Answers2

0

Configure your SQL Server to allow TCP/IP connections. Go to SQL Server Configuration Manager -> network then protocols for your SQL Server named instance -> TCP/IP.

See this image!

SQL Server configuration manager protocol configuration

Peter Mortensen
  • 30,738
  • 21
  • 105
  • 131
yonas
  • 44
  • 1
  • 6
0

There is very slight error in your connection string and honestly I can't blame you for that as it is very weird way in which SQL Sever is behaving. I'm not sure if this error lies in connection provider side or SQL Server instance side. Name of Network Library that your application is using to connect to the SQL Server using dbmssocn(Win32 Winsock TCP/IP) should always be mentioned in capital letters. Though I didn't see any relevant MSDN documentation from MS to support my statement but it actually worked when I did so. Here is the connection string that you should be using to fix the error.

String connString = @"Network Library=DBMSSOCN; Network Address=127.0.0.1,1433; Integrated security=SSPI; Initial Catalog=db";

Seriously, I got freaked out in reproducing your issue as instead of copying the connection string from your question I copied it from some other blog :). But all is well that ends well. I've also assumed that a database named "db" actually exists on the default (NOT named instance) instance of the sql server you are connecting to when typing this answer. If changing the casing of network library name in connection string doesn't help then double check that database "db" must exist for a sql server default instance to which you are connecting to. In case you are using a named instance of sql server in your installation then that instance name should also come in the connection string.

RBT
  • 24,161
  • 21
  • 159
  • 240
  • RBT , with this connection string can i connect from another PC, where SQL SERVER isn't installed, but my c# application is running – Leo Mujaj Apr 10 '16 at 10:05