Start Free Trial

asked on

Cannot connect with SSMS 18.4 to SQL Server 2019 running inside of a Virtual Machine

Hello All;
(Windows 2016 Server running SQL Server 2019 Cluster installation)

I have run into an issue and I am at a complete loss as to what to do about it.
The only other option is to delete the VM and create a new one and re-do the process and see if it will work then.

OK.
On a Virtual Machine.
I have SQL Server 2019 installed as a cluster.
Currently, there is only ONE server running in this test run.

Connecting to the server through SSMS 18.4
From my laptop to the server, it fails every time. Error 26
I have done the following.

(On the VM Server)
Made sure all SQL items in the Services, are in fact running and they are from the get-go.
Open Firewall for programs: SQLBrowser and SQLServer. (Also for good measure, I created two more rules for 1433 and 1434)
For SQL Server Config Manager - Enabled all items, TCP/IP, set on port 1433 for both the IP Addresses shown.
(Actually, there are IP Addresses in the TCP/IP config, that I have NO idea what the heck they are, or how they got in there???)

I checked the DC for entries in DNS, and all IP addresses are entered.
Both for the two NICs and the Cluster assigned IP.

Installed SSMS 18.4 on the VM Server, and it connected to it, without an issue.
I also checked once I launched SSMS to make sure it was set up to allow connections, and it was by default.
It just will not connect through the network.
Now, this is NOT my first time working with a VM Machine with SQL Server and connecting with SSMS.
I have a successfully running setup at the moment, but this one, I just don't know what is going on.

Any other ideas?
This is racking my brain.
Usually doing all the above-mentioned items, with the Firewall, services, and Config, it goes to work. But not in this case.

Wayne

Hopefully the "allow connections" means "allow remote connections" checkbox in server properties.

Are you able to connect to the server Remote desktop from your laptop?

What login type are you using? SQL login or Windows login? Did you try both of them?

This blog post could give you a few more options to check: https://docs.microsoft.com/en-us/archive/blogs/sql_protocols/sql-network-interfaces-error-26-error-locating-serverinstance-specified

Do you have any firewall in between, maybe some ports are getting blocked ?

ASKER

@ pcelba
I just enabled remote desktop connections and I WAS able to connect from my laptop to it.
Using SQL login.
First SA login
Then after installing SSMS, I created my default user and tried it.
It still gives the error:26

@ Zaheer Iqbal
I have 7 VM's that I connect to.
2 of them currently run SQL Server, and I am able to connect to them with SSMS on my laptop.

I guess It might just be a broken installation?
I will blow it out and reinstall it this evening after I get back in.
Unless someone has some hidden gem that would let me know about.

Wayne

Please check the ServerName\SQLInstanceName once again. Then try to use IP address\SQLInstanceName for the connection. Then you may try to list available SQL Servers from SSMS connection dialog (SQL Server Browser Service must be running on the server and UDP port 1434 must be open). If nothing helps then I don't have any other hint except the reinstall...

The last two questions:
Did you query the UDP port 1434 by PortQry.exe? What is the result? (https://support.microsoft.com/en-us/help/832919/new-features-and-functionality-in-portqry-version-2-0)

Did you try to create ODBC connection to the server in ODBC Administrator?

ASKER

Browser Service = running

Downloaded and installed the PortQry tool on my laptop.
Ran it, and this is the output.

D:\Server\Tools\PortQryV2>portqry -n sql-temp -p udp -e 1434 
 
Querying target system called: 
 
 sql-temp 
 
Attempting to resolve name to IP address... 
 
 
Name resolved to 192.168.2.150 
 
querying... 
 
UDP port 1434 (ms-sql-m service): LISTENING or FILTERED 
 
Sending SQL Server query to UDP port 1434... 
 
Server's response: 
 
ServerName SQLCLUSTER 
InstanceName SQL2019 
IsClustered Yes 
Version 15.0.2000.5 
tcp 58573 
np \\SQLCLUSTER\pipe\MSSQL$SQL2019\sql\query 
 
==== End of SQL Server query response ==== 
 
UDP port 1434 is LISTENING

Open in new window

The servername\instance is this.

SQLCLUSTER\SQL2019

Open in new window

I have never done or used the ODBC Administrator to create connections before.

I also tried the PowerShell Script, and it returned the SQL Server instances in the network, including the one I am trying to connect to with SSMS.
This is the script I tried, and it listed the SQL2019.
So, it sees it, I just cannot connect to it with SSMS.
So, what gives?

$SQLInstances = Invoke-Command -ComputerName sql-temp { 
 (Get-ItemProperty 'HKLM:\SOFTWARE\Microsoft\Microsoft SQL Server').InstalledInstances 
 } 
    foreach ($sql in $SQLInstances) { 
       [PSCustomObject]@{ 
           ServerName = $sql.PSComputerName 
           InstanceName = $sql 
       } 
   }

Open in new window

ASKER

I watched a video on using the ODBC Administrator.
I ran it, and this is my output.

---------------------------
Microsoft SQL Server Login
---------------------------
Connection failed:
SQLState: '01000'
SQL Server Error: 2
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()).
Connection failed:
SQLState: '08001'
SQL Server Error: 17
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.

---------------------------
OK
---------------------------

Enough is enough.
I delete the drive in the VM, added in a new drive and did a reinstall of the system.
I will post back if I still experience the same issues.

ASKER

Still same issue????
I am about to lose my blasted mind!

ASKER

I can also ping both the server name / IP Addresses / SQL ServerName
All return valid responses.

I don't know what the heck is going on?
I have a SQL Server 2019 running on a non-virtual machine and can connect to it.
I have a 2016 SQL Server running in a VM, and I can connect to it.
But this ONE I cannot connect???

I am at a total loss.

Is there any version difference ?

Does the server have the correct gateway configured and can it get back to your laptop ?

The portqry shows TCP port 58573. Did you open this port on firewall?

ASKER

Yes, there is a gateway, all servers are connected to the same one.
And yes, I just did a ping to my laptop and it identified it without any lag.
And the servers network discovery shows all systems in the network.

ASKER

58573.???
No, I did not.

ASKER

I just did a netstat -aon
On the server, and it does not show that port as being open.

ASKER

I ran the ODBC on the server itself and it worked like a charm.
>> TESTS COMPLETED SUCCESSFULLY
But still cannot connect from the network.

Yes, the local ODBC does not have problems obviously.

If TCP port 58573 is not open you could try to reconfigure SQL Server to TCP port 1433 which is open already and I believe it will work.

You could also try to query the port 58573 directly and the return code will tell the success

portqry.exe -n SQLCLUSTER -e 58573 -p TCP

ASKER

Could you please provide information on how to do this?
>> reconfigure SQL Server to TCP port 1433

ASKER

>> portqry.exe -n SQLCLUSTER -e 58573 -p TCP
This failed, no such port opened.

ASKER

Update (Issue still exist)

To find out what is going on, I am using our Maintenance system, that host the sites when I am running test and upgrades on the server — running Win2016 through VirtualBox, on a Win10 system.

OK, Installed SQL Server 2019, and was able to connect to it without an issue.
So, there HAS to be a problem with the Cluster itself.
I went as far as following my videos for creating the Cluster and watching others as well.
And no matter what I do, I cannot get it to work with a network SSMS.

This mess is confusing me that much more.
However, at least with the way I have it set up right now, the sites will continue to run on the backup VM, while I try to figure out what in the heck is going on with the cluster issue.

ASKER

OK.,
Downloaded the tool: ApexSQL Discover and ran it.
It lists all the SQL Servers in my network.
All servers are showing as SQLName\InstanceName
Except, the one I have been having issues with. It is showing as:
SQLName.domain.local

What is going on?
At least I know why it is not allowing me to pick it up.
I wonder if this is a DNS issue?

ASKER

IM IN!!!

OK, for whatever reason, I have to type it out like this.
SQLName.Domain.local
And it let me in...

I am going to leave this open to see if someone can come in and maybe shed some light on WHY I have to log in like this, but either way, I am in, and now I can start working with the new SQL Server 2019 and getting it ready for the live sites.

Great! This name should also be visible in SQL Server Configuration Manager when you click on SQL Server Services and then display Properties after right click on your SQL Server service. Then look at the Virtual Server name in Advanced tab.

I would explain it this way: The failover cluster which consists of several SQL Servers should be accessible under one common name no matter what instance is currently active.

ASKER

Virtual Saver Name = SQLName
It does not display as: SQLName.domain.local

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Is the laptop in the same domain?

ASKER

Yes, the laptop is in the same domain.

OK, thanks! I've learned something new.

ASKER

I had to change it in all my asp classic and vb.net connection strings as well.
It all has to be. SQLName.Domain.local
It reminds me of when I use to use a hosting company.
Either way works, and I am now running all sites on the new 2019 database.
Happy.