Still having difficulties connecting to default SQL instance on a different PC
-
I'm making progress. I've created a user, which will access the database that I have on my Windows 10 Professional machine. Then I added a login, so I could use SQL Authentication from another Windows 10 Professional machine. Then I used an
ALTER USER <username> WITH LOGIN=<loginname>
command. After that I got out of SSMS on the machine with the database, then got back in, changing to SQL Authentication, and logged in with the login name. It worked fine, I could perform SELECTs, etc., no problem.
So then I went to the other Windows 10 Pro machine, brought up SSMS. I changed to SQL Authentication, entered the name of the Windows 10 Pro machine with the database, the login, and the password I made for it. But it failed with this error:
What have I done wrong?
Rod
-
So have you enabled TCP/IP as a protocol for SQL Server to allow incoming connections from other machines in the network and then restarted the service?
have you then also opened the windows firewall on the database machine to have a access control list for port TCP1433, or if using a named server the TCP port that it has dynamically assigned (if it is a named instance I recommend setting a static port)
Once those have been done and to test it all try doing a powershell test-netconnection via this command
TNC MyDatabaseMachine -port 1433
If that comes back as a successful connection then you should be able to then use SSMS to connect to the SQL instance.
-
Yes, TCP/IP is an enabled protocol on the SQL Server machine.
It's probably in the Windows 10 firewall & network protection that I'm being tripped up. I've opened the Private network. There I see the following:
Active private networks
This shows my home network only
Microsoft Defender Firewall
The toggle switch is set to On, which I'm sure I want to keep.
Incoming connections
There's a checkbox here, which is unchecked, that has a caption which reads, "Blocks all incoming connections, including those in the list of allowed apps"
That's it, there's nothing else on this page/window
Rod
-
Hi @Ant-Green,
Follow-up
I found this link on Tom's Hardware, which showed me a separate way of opening port 1433. https://www.tomshardware.com/news/how-to-open-firewall-ports-in-windows-10,36451.html
So, I've created a new rule in Windows Defender, to all inbound traffic in my private network to port 1433. (Domain and public network still are blocked.)
But I still cannot connect nor does issuing that PowerShell Test-NetConnection work. I remember that you said I should restart the service, but I wasn't sure what service you're talking about. I tried rebooting the Windows 10 machine with SQL 2019 on it, but that didn't help. I might still need to restart the service. What service is it I should restart?
Rod
-
This was removed by the editor as SPAM
-
OK, so to confirm, SQL Configuration Manger shows TCP/IP as enabled here
You then have a TCP 1433 (or whatever port is in use if a named instance) in the Windows Advanced Firewall like so
Once that is done, reboot the SQL server machine.
Once the SQL machine is back online, on your other client machine run the TNC check, probably have to use the IP instead of the name and you should end up something like this
If the TcpTestSucceeded is True then you can connect to the SQL machine from outside of the SQL machine. If false, either the TCP/IP protocol isn't enabled correctly or the firewall is still blocking the traffic.
-
Testing the DBATools and the Connect-DbaInstance (as I don't usually use a second machine as its all on my laptop, and DBATools uses SMO which is the same as SSMS so a true test as to what would happen anyway)
My remote machine can successfully connect to the SQL Instance on another machine
Confirmed on the SQL server as using TCP from a remote machine (SQL machine named ASGT1, remote machine named DESKTOP-######)
-
@Ant-Green, I really appreciate your providing that screen snapshot! I had thought what I needed to do was enable TCP/IP in the SQL Native Client 11.0 Configuration (for both 64 and 32 bit), so that's what I did. I didn't know that it had to be done to the SQL Server Network Configuration! Testing it now.
Addendum
It worked!! I was turning on TCP at the wrong place. Thank you, @Ant-Green!!
Rod
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply