none
Set up two computers on LAN to use the same SQL Server database - still unable

    Question

  • Hi,

    About three weeks ago I wrote a post here trying to troubleshoot remote connection to SQL Server over LAN. The problem was (at least partly) that I was using LocalDB. Now I have uninstalled everything SQL Server related and installed a new version, Select @@version returns now:

    Microsoft SQL Server 2016 (SP1-GDR) (KB3207512) - 13.0.4199.0 (X64)   Nov 18 2016 15:56:54   Copyright (c) Microsoft Corporation  Enterprise Edition: Core-based Licensing (64-bit) on Windows 8.1 Enterprise 6.3 <X64> (Build 9600: ) 

    So I think the version should be good enough. The point I'm stuck at now is trying to connect to the SQL Server (i.e. the same spot). When trying to connect, I get this:


    So the things I've done so far:

    Windows Firewall accepts traffic to ports UDP 1434, TCP 1433, TCP 49171

    Pinging either 192.168.0.1 or my machine name works.

    I have Server Properties / Security like this:

    I have enabled TCP/IP protocol from SQL Server Configuration Manager. I have restarted service after that.

    From SQL Server Configuration Manager I have configured TCP/IP protocol IP Addresses IPAll by clearing out "TCP Dynamic Ports" and adding TCP Port 49171. I have restarted from SQL Server Services SQL Server and SQL Server Browser after that.

    From SQL Server Management Studio Server / Security / Logins I have created a user a with password b. I also have user sa with password. I have tried login with both of them, no success.

    It is very possible that I have some stupid rookie mistake here, since I've never got this to work. So even a "have you actually tried thing X" or "have you ensured that you have Y" is probably a pretty good guess here too and would be appreciated as an answer. I seriously don't even know if I'm still using LocalDB, so how would I ensure that part? If I use named instance, as I do here, does that rule out already that I'm not using LocalDB? If I'm still using LocalDB, then what should I do to remove that part from this equation. I don't have a problem with reinstalling everything once more, if required. The database I'm trying to use is very small and purely in personal use, so importing/exporting that too is not a problem.

    Thursday, February 23, 2017 3:08 AM

Answers

  • Pinging either 192.168.0.1 or my machine name works.

    In common that IP address ending with 1 is the address of the network route or gateway, sure it's the address of your server? Does a ping of the server name return the same IP address?

    In the error message I see you use both server name and IP port; use either the name or the IP port, but not both.

    There is a nice interactive guide from MS Support to solve connectivity issues: Solving Connectivity errors to SQL Server


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by ArtKilp Thursday, February 23, 2017 10:17 AM
    Thursday, February 23, 2017 5:09 AM

All replies

  • Pinging either 192.168.0.1 or my machine name works.

    In common that IP address ending with 1 is the address of the network route or gateway, sure it's the address of your server? Does a ping of the server name return the same IP address?

    In the error message I see you use both server name and IP port; use either the name or the IP port, but not both.

    There is a nice interactive guide from MS Support to solve connectivity issues: Solving Connectivity errors to SQL Server


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by ArtKilp Thursday, February 23, 2017 10:17 AM
    Thursday, February 23, 2017 5:09 AM
  • Pinging either 192.168.0.1 or my machine name works.

    In common that IP address ending with 1 is the address of the network route or gateway, sure it's the address of your server? Does a ping of the server name return the same IP address?

    In the error message I see you use both server name and IP port; use either the name or the IP port, but not both.

    There is a nice interactive guide from MS Support to solve connectivity issues: Solving Connectivity errors to SQL Server


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Do you really think that I wouldn't have checked the IP address? Good if you do, because that was exactly what was the problem. Checking that I noticed it was .10, not .1 that ending. And now it works. Thanks :)
    • Edited by ArtKilp Thursday, February 23, 2017 10:18 AM
    Thursday, February 23, 2017 10:17 AM