0

I know how to connect to SQL server Management studio locally, the one to your desktop, Windows Authentication. Code is as below.

myconnection As New SqlConnection("data source =serverNAME01; initial catalog=ZPCD; integrated security=true")

But how do I connect to SQL server Management Studio on Windows Server 2012R2, SQL Server Authentication from my desktop ?

The computer and username for remote desktop connection to Windows Server 2012R2 are:
computerNAME01,
userNAME01

The server name, login, password and IP address to management studio (database engine) are:

Server name: serverName01

Login: sa

Password: Password01

IP: 192.167.1.21

and database name is ZPCD

enter image description here

This is an example of my code, but it doesn't work.

connetionString="Data Source=192.167.1.21,1433;
    Network Library=DBMSSOCN;   Initial Catalog=ZPCD;
    User ID=sa;Password=Password01"

Error Message:

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. (provide: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 5)

TylerH
  • 20,799
  • 66
  • 75
  • 101
Johnseito
  • 315
  • 1
  • 9
  • 24
  • *it doesn't work* is not a meaningful problem description. In what way **specifically** does it *not work*? We have no access to that server, we can't see your screen, and we can't read your mind. What **specific problem** are you having? – Ken White Mar 21 '17 at 00:26
  • You're not connecting to SQL Server Management Studio. SSMS is an application, just like yours. Both it and your application are connecting to a SQL Server instance. If you open SSMS on your machine, can you connect to the desired database on this other machine? If so then you use the exact same connection information in your own application as you do in SSMS. There's no need to use IP addresses and such if the machines are on the same LAN. – jmcilhinney Mar 21 '17 at 00:27
  • Ken white - It always stops at this code **myconnection.Open()** - after passing my credentials. I have to see what error message it gives and let you know after I try it again on my server, but it pops up an error message. – Johnseito Mar 21 '17 at 00:31
  • jmcilhinney - I have SQL server management studio set up on my desktop, I tried to use it to connect to the server SQL management studio by populating the server credential and I couldn't connect either. So I am not sure how can I connect to it using VB code. What do you suggest or advise ? – Johnseito Mar 21 '17 at 00:34
  • Let me repeat, you are NOT connecting to SQL Server Management Studio. Both YOUR application AND the SQL Server Management Studio application connect to a SQL Server instance. – jmcilhinney Mar 21 '17 at 00:38
  • Ken white - I just took a look and the error message I got is. _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. (provide: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 5)_ I did a remote desktop connection to Window Server 2012R2, then click on management studio, enter credential and click connect, it's accessible this way. – Johnseito Mar 21 '17 at 00:45
  • jmcilhinney - thanks for clearing that up, I understand better now. :) – Johnseito Mar 21 '17 at 00:50
  • Read what the error message says: "Verify that [...] SQL Server is configured to allow remote connections". Have you done that? If you can connect to an instance from the local machine, which you're doing when you RDP and then use SSMS on that machine, but can't connect from a remote machine, which you're doing when you use SSMS on your machine, then that suggests that SQL Server is NOT configured to allow remote connections. – jmcilhinney Mar 21 '17 at 00:53
  • jmcilhinney - how do I configure SQL to allow remote connections then ? – Johnseito Mar 21 '17 at 00:56

3 Answers3

0

https://technet.microsoft.com/en-us/library/hh231672(v=sql.110).aspx

To enable the TCP/IP network protocol Start SQL Server Configuration Manager. Click Start, point to All Programs, and click Microsoft SQL Server. Click Configuration Tools, and then click SQL Server Configuration Manager. In SQL Server Configuration Manager, in the console pane, expand SQL Server Network Configuration. In the console pane, click Protocols for . In the details pane, right-click TCP/IP, and then click Enable. In the console pane, click SQL Server Services. In the details pane, right-click SQL Server (), and then click Restart, to stop and restart the SQL Server service.

RoMEoMusTDiE
  • 4,739
  • 1
  • 17
  • 26
  • maSTA rHiAn, thanks for the suggestion, I tried exactly what you mentioned above and got another error message that states "connection timeout expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was [pre-login] initialization =13435; handshake=14476; (Microsoft SQL Server) the wait operation time out." – Johnseito Mar 21 '17 at 13:16
  • Whats your connection string? – RoMEoMusTDiE Mar 21 '17 at 17:37
  • I use management studio from my desktop to connect with credential from the database instance on the window server 2012r2 and got that error message. So no connection string is use. I might try the connection string with a vb code but if I can't connect via management studio I don't think I can connect via vb code. What do you advise ? – Johnseito Mar 21 '17 at 18:42
  • Ok just to be clear you can't connect from manage studio using the ip address? But can using the machine name? – RoMEoMusTDiE Mar 21 '17 at 18:45
  • Are running 32 or 64 bit? Apply the correct system under the sql configuration and restart PC... do you what account sqlservices is running? – RoMEoMusTDiE Mar 21 '17 at 18:46
  • Yes that I understand. I know the server name, login and password. IP address only if WAN and LAN no need. :) – Johnseito Mar 21 '17 at 18:50
  • I guess the connectionstring first in vb and just use localhost – RoMEoMusTDiE Mar 21 '17 at 18:52
  • But with management studio from my desktop connecting to database instance on window server 2012 R2. Why do I have that error message ? – Johnseito Mar 21 '17 at 20:06
  • in the server name type "." or (local) – RoMEoMusTDiE Mar 21 '17 at 21:01
  • What is "." Or local mean. The server name is the computer name of window server 2012R2 that need Remote Desktop connection from my desktop to get in. – Johnseito Mar 21 '17 at 23:35
  • i think you still need to enable firewall for sql to communicate. – RoMEoMusTDiE Mar 21 '17 at 23:36
  • I enable firewall and my management studio from my desktop was working but not my code. – Johnseito Mar 23 '17 at 02:04
0

Remote connection string should look like so.

SqlConnection("Server=192.167.1.21\sqlinstance; DATABASE=ZPCD; Connection Timeout=5; USER ID=SA; PASSWORD=Password01")

You need to have the SQL instance, the part in the () in image below after the \ after the IP address.

enter image description here

Can you post this from SQL Management Studio enter image description here

Das Nuk
  • 46
  • 8
  • Das Nuk, thanks for the example. I tried your code and it doesn't work but is giving an error message. "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: SQL NETWORK Interfaces, error: 26 - error locating server/instance specified) – Johnseito Mar 21 '17 at 20:22
  • Das Nuk - sorry I am not clear what do you mean by this: "You need to have the SQL instance, the part in the () in image below after the \ after the IP address." – Johnseito Mar 21 '17 at 20:32
  • When you setup your SQL server you gave it an instance name. In my screen grab, it's called REMCO. You need to put that after the \ after the ip address. – Das Nuk Mar 22 '17 at 06:30
  • Ok thank you. I understand after putting the instance I have error message: "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: SQL network interfaces, error: 25 -connection string is not valid)". Next I am going to see if is a firewall issue. – Johnseito Mar 22 '17 at 16:35
  • @Johnseito See my added picture. Can you post that info, the very top line highlighted in blue in the image? That will help me get your string right. – Das Nuk Mar 22 '17 at 19:27
0

This is the format of the connection string I use. The format (apparently) changed in VB.NET from VS 2008 to VS 2010. When I upgraded my software from VS 2008 to VS 2010, it failed to connect to Sql Server. Took me hours to figure it out:

connectionString="server=192.168.1.1;database=MyDb;uid=sa; pwd=MyPassword" providerName="System.Data.SqlClient"

R/ Prescott ....

Prescott Chartier
  • 1,519
  • 3
  • 17
  • 34