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:

    Screenshot 2021-08-25 182954

    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

    • This reply was modified 3 months, 1 week ago by  Doctor Who 2. Reason: Corrected spelling

    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

    TCPIP

    You then have a TCP 1433 (or whatever port is in use if a named instance) in the Windows Advanced Firewall like so

    Firewall

    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

    TNC

    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

    Connect_Win10Pro

    Confirmed on the SQL server as using TCP from a remote machine (SQL machine named ASGT1, remote machine named DESKTOP-######)

    SessionConnection

     

  • @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!!

    • This reply was modified 3 months, 1 week ago by  Doctor Who 2.

    Rod

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply