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

    Question

  • Hi,

    Let's start with the simple requirement, so that you know where I'm trying to go: I need to access one database from two computers. I would prefer the database be a local one in the home network. Re-creating the current database is not a problem. The solution has to work with SQL Server 2016.

    So what do I have now:

    A database on local computer, that I can access from that very same computer.

    I also have another computer, that I can use to access the computer in Windows network, that is, to see for example the shared printers.

    I have configured to the server properties that "Allow remote connections to this server" are enabled.

    I have checked that my Windows firewall allows connections using this given, 49172 TCP port.

    I have enabled TCP/IP from Sql Server Configuration Manager / Protocols for MSSQLSERVER

    I have created an SQL Server login account "a" with password "b" on the server.

    I have tried with Windows Authentication too.

    I have tried to connect using address servercomputername\databasename,49172. Error 258 comes out (not found or not accessible). I have tried also with servercomputername,49172. Same error.

    I have rebooted the server computer.

    Obviously I'm missing something important here, but what?

    And yes, going with some "why don't you"-answer is fine too.


    • Edited by ArtKilp Saturday, February 4, 2017 6:19 PM
    Saturday, February 4, 2017 6:18 PM

All replies

  • Why are you trying to connect to port 49172. SQL normally runs on port 1433.

    You also do not connect to servercomputername\databasename but rather to servercomputername or servercomputername\instancename.

    Simplest way to try to see if this is working is going to a remote computer and configuring an odbc dsn to your server running SQL server. For the server name type in the name of your server. What error message do you get?

    Check your database computer, in Management Studio right click on it after connecting to it and select properties and security. Do you see the below?

    Do you see windows authentication or SQL Server and Windows authentication mode, or just Windows Authentication. Otherwise you will need to select SQL Server and Windows authentication mode and restart your sql server service. This will enable your SQL login.

    Saturday, February 4, 2017 7:04 PM
  • Hi, this newly updated article may assist https://support.microsoft.com/en-us/help/4009936/solving-connectivity-errors-to-sql-server

    Thanks for the link.

    Some results based on that:

    FQDN is NULL. (Got that from SSMS with

    DECLARE @Domain NVARCHAR(100)
    EXEC master.dbo.xp_regread 'HKEY_LOCAL_MACHINE', 'SYSTEM\CurrentControlSet\services\Tcpip\Parameters', N'Domain',@Domain OUTPUT
    SELECT Cast(SERVERPROPERTY('MachineName') as nvarchar) + '.' + @Domain AS FQDN)

    Connecting to (LOCALHOST)\v11.0 by making UDL works. But only from the server machine.

    Pinging the server works.


    • Edited by ArtKilp Saturday, February 4, 2017 9:59 PM
    Saturday, February 4, 2017 9:53 PM
  • Why are you trying to connect to port 49172. SQL normally runs on port 1433.

    You also do not connect to servercomputername\databasename but rather to servercomputername or servercomputername\instancename.

    Simplest way to try to see if this is working is going to a remote computer and configuring an odbc dsn to your server running SQL server. For the server name type in the name of your server. What error message do you get?

    Check your database computer, in Management Studio right click on it after connecting to it and select properties and security. Do you see the below?

    Do you see windows authentication or SQL Server and Windows authentication mode, or just Windows Authentication. Otherwise you will need to select SQL Server and Windows authentication mode and restart your sql server service. This will enable your SQL login.

    49172 was Microsoft example somewhere. I can use 1433 too, doesn't matter.

    Wasn't able to get ODBC to work either. Just doesn't connect.

    Server authentication is both, SQL and Windows.

    But I can't change the Login auditing, I just don't have the rights to do that (although, since this is my computer, I have rights to do anything, if the settings are just correct). I can't click "Restart" for the server either and I checked some advice and run into a wall with "execute subinacl /SERVICE LOCALDB#872EB71F /GRANT=everyone", I just don't have any idea even with what I'm supposed to execute that with. Neither commandline nor SSMS recognizes "subinacl".


    • Edited by ArtKilp Saturday, February 4, 2017 9:58 PM
    Saturday, February 4, 2017 9:57 PM
  • '(LOCALHOST)\v11.0' -> it looks like you have installed LocalDB which as you've found is only accessibly to the local server.

    You'll need to use an edition which allows remote connected i.e. Express, Standard, Enterprise or Developer (if non-Prod).

    Saturday, February 4, 2017 10:04 PM
  • '(LOCALHOST)\v11.0' -> it looks like you have installed LocalDB which as you've found is only accessibly to the local server.

    You'll need to use an edition which allows remote connected i.e. Express, Standard, Enterprise or Developer (if non-Prod).

    Ok, well, I'll install a better version. I think I initially had Express just to test with something. Thanks :)
    Saturday, February 4, 2017 10:17 PM
  • Express Edition should be fine for your testing, just don't select the LocalDB option rather select the Database Engine Services.
    Saturday, February 4, 2017 10:21 PM