0

I'm running Visual Studio 2013 VB .Net on Windows 8 and I'm unable to Open a connection to any of the remote SQL Server databases that I can otherwise connect to via SQLServer Management Studio.

Specifically, I'm receiving the two errors below, and I can't determine why I cannot Open a connection:

The server was not found or was not accessible. Verify that the instance name is correct and that the SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 - A Non-recoverable error occurred during database lookup.)

and

..... (provider: SQL Network Interfaces, error: 25 - Connection string is not valid.)

My connection string is:

for error: 0

Integrated Security=SSPI;
Connect Timeout=20;
Persist Security Info=False;
Initial Catalog=SunSystemsData;
Data Source=tcp:svrsun07\MSSQLSERVER,1433;

for error: 25

Integrated Security=SSPI;
Connect Timeout=20;
Persist Security Info=False;
Initial Catalog=SunSystemsData;
Data Source=svrsun07\MSSQLSERVER;

I have confirmed the following:

  • My servername is correct (I can connect to this in SQLMS and view the properties)
  • My instancename is correct (I've used PortQuery to very this is the instance for my sever).
  • My Database is set to Allow Remote Connections=True.
  • My Client Protocols in Configuration Manager have both TCP/IP and Named Pipes Enabled.
  • I have Permission to Connect and View and databases in SQLSMS.
  • I've tried explicitly specifying Port 1433 but with no success.
  • I've tried not specifying TCP and instead the Client uses Named Pipes and returns a Named Pipes 40 error.
  • I've tried not using any instancename (i.e. just Data Source=svrsun07;) but with no success.
  • I can ping the severname successfully.

Any suggestions are most welcome, thanks!

Simon Wray
  • 192
  • 4
  • 12
  • Did you visit this post? "Enable remote connections for SQL Server...." http://stackoverflow.com/q/11278114/771579 – Orlando Herrera May 18 '15 at 15:43
  • 1
    A cheap trick is to use Admin Tools/ ODBC / System-DSN to create a connection to your database......to take the "dot net code" out of the variables. If you cannot create a ODBC connection, then its not your "code", its your connectivity. You can delete the ODBC entry you create after the test of course. – granadaCoder May 18 '15 at 15:43
  • Yes, I can create a DSN entry and connect via that. I've also discovered that the connection works on my manager's machine, which is Windows 7 Visual Studio Pro 2010 and under his credentials, but not when he logs onto my machine and runs it, which is Windows 8 VSPro 2013. Also, from within VSPro 2013 I can use SQL-->Connection-->Connect to connect to my server and run a simple select statement. However, I note that is running SQL Server 2014. Is there any way I can get the string back out that VS is using for this menu action? – Simon Wray May 18 '15 at 17:50
  • I've tried setting up an old ADODB connection in Excel VBA, and that connects fine... "Provider=sqloledb;Server=svrsun07;Database=SunSystemsData;Integrated Security=SSPI;Persist Security Info=False". Any further suggestions? – Simon Wray May 18 '15 at 18:35
  • In case anyone is interested, this seems related to the directory structure on my locale machine, which is a virtual device. When I move my code to a physical network drive, the connection Open's fine in debug and as an executable. But locally, the connection fails to Open, whether in code or compiled exe. – Simon Wray May 19 '15 at 17:24

0 Answers0