Link to home
Start Free TrialLog in
Avatar of seth42
seth42

asked on

SqlConnection error

When I use the Visual Studio 2010 Datasource designer to connect to my SQL server remotely everything works fine.  However, if I try to connect programatically from the same project using the attached code (sensitive info X'd out) I get 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: TCP Provider, error: 0 - The requested service provider could not be loaded or initialized.)"}

port 1433 is open on the remote network and like I said I can browse the server fine through datasource designer. I'm using the same connection string as the designer in my code.

SqlConnection conn = new SqlConnection();
conn.ConnectionString = "Data Source=\"XX.XXX.XXX.XXX,1433\";User ID=ASPNET;Password=XXXXXXXXX";
conn.Open();

Open in new window

Avatar of strickdd
strickdd
Flag of United States of America image

run Telnet on the computer that your application runs on and try to telnet into: XX.XXX.XXX.XXX,1433.

If you can connect without errors, the problem is something with your app. If you can't connect, the problem is

- with the network
- with the port forwarding to SQL server
- with the firewall
- etc.
Avatar of seth42
seth42

ASKER

like I said I can browse the SQL server from the same machine using MS Visual Studio 2010 Datasource designer. It's when I try to connect programmatically that I have the problem.
Then it is your connection string. The format should be like this:

Data Source=XX.XXX.XXX.XXX,1433;Initial Catalog=MYDATABSE;User Id=ASPNET;Password=XXXXXXXXX;
Hello try this connection string:
Data Source=XXX.XXX.XXX.XXX,1433;Network Library=DBMSSOCN;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;

Open in new window


Adjust to your needs.

Reference:
http://connectionstrings.com/sql-server-2005
Avatar of seth42

ASKER

Tried both suggestions and a bunch of other variations on the connection string, same problem.

Tried ConnectionString Tester utility, same problem.
Hello, are you sure that your SQL server instance has enabled the TCP-IP protocol? Maybe the datasource designer is using the named pipes protocol instead of tcp.

The next connection string uses the named pipes protocol:

Data Source=XXX.XXX.XXX.XXX,1433;Network Library=dbnmpntw;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;
 
Sorry, my last post has an error in the connection string format, please try this instead (YourServerIp without the port number):

Data Source=np:YourServerIp;Initial Catalog=YourDataBase;User ID=ASPNET;Password=yourPassword;

Example:
Data Source=np:127.0.0.1;Initial Catalog=Demos;User ID=ASPNET;Password=AbcXyz123;

If you are able to connect with this connection string this meant that your remote sql server instance do not has  the tcp/ip protocol enabled, you can to enabled it by using the "Sql Server Configuration Manager" utility.
Avatar of seth42

ASKER

thanks for your help but no luck. I may abandon this approach because I was just using SQL to transfer print job data. I think I can use FTP to do this much more reliably without having to make users add firewall exceptions and having to deal with potential obscure errors that are hard to fix. I was also using an SqlDependency which might add some unnecessary complexity to the whole process.
ASKER CERTIFIED SOLUTION
Avatar of regevha
regevha

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial