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.
{"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();
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=XXXXXXX XX;
Data Source=XX.XXX.XXX.XXX,1433
Hello try this connection string:
Adjust to your needs.
Reference:
http://connectionstrings.com/sql-server-2005
Data Source=XXX.XXX.XXX.XXX,1433;Network Library=DBMSSOCN;Initial Catalog=myDataBase;User ID=myUsername;Password=myPassword;
Adjust to your needs.
Reference:
http://connectionstrings.com/sql-server-2005
ASKER
Tried both suggestions and a bunch of other variations on the connection string, same problem.
Tried ConnectionString Tester utility, 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,143 3;Network Library=dbnmpntw;Initial Catalog=myDataBase;User ID=myUsername;Password=myP assword;
The next connection string uses the named pipes protocol:
Data Source=XXX.XXX.XXX.XXX,143
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.
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.