Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 269
  • Last Modified:

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

0
seth42
Asked:
seth42
  • 3
  • 3
  • 2
  • +1
1 Solution
 
strickddCommented:
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.
0
 
seth42Author Commented:
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.
0
 
strickddCommented:
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;
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Carlos VillegasFull Stack .NET DeveloperCommented:
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
0
 
seth42Author Commented:
Tried both suggestions and a bunch of other variations on the connection string, same problem.

Tried ConnectionString Tester utility, same problem.
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
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;
 
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
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.
0
 
seth42Author Commented:
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.
0
 
regevhaCommented:
For file transfer with sensitive data, I recommend to use secure FTP like:
1. FTP over SSL/TLS
2. FTP over SSH
More information on these protocols - http://www.codeguru.com/csharp/.net/net_general/internet/article.php/c14329

There are a few .NET class libraries for this purpose like "Alex FTPS Client" - http://ftps.codeplex.com/

Good luck
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now