How to access a SQL Server database from other computer connected to the same workgroup?

Posted on

Question :

I have created a C# application which uses a SQL Server database. I have other computers connected to me and to each other in a workgroup. I have shared my C# application with others. When they open the application they get the error

A network related or instance-specific error occured while establishing a connection to SQL Server. the server was not found or was not accessible

But the application is working fine on my PC. The connection string I am using is

Data Source=ASHISHPC1SQLEXPRESS;Initial Catalog=ACW;User ID=ash159;Password=ashish159

which is stored in a .config file.

The application is working fine on my PC. What must I do? I have enabled the TCP/IP in the server but the same error persists. Some change in connection string or something else?

Answer :

There are two levels of security that need to be changed in order to allow remote access.

  1. SQL Server configuration. By default in Express, Developer, and Enteprise Evaluation editions, connecting by the TCP/IP protocol is disabled. Enable this using SQL Server Configuration Manager.

  2. Windows Firewall. While disabling the firewall entirely will work for this component, doing so is not a security best-practice (nor is it required). (Note: in this section, I assume a default configuration. There are many settings that can be changed which affect these steps slightly.)

    There are two cases depending on the type of SQL Server instance you’re connecting to:

    1. Default instance (connect by computer name only). Add an allow incoming rule either on TCP port 1433 or the database engine service.

    2. Named instance (connect by computer name + instance name). Add an allow incoming rule on UDP port 1434 to access to the SQL Browser service. Add an allow incoming rule on the database engine service.

This procedure is fully documented here, or you can watch me explain and then walk through the configuration for a named instance here.

You have to set the correct Network-Settings in your SQL-Server:
Active TCP/IP in SQL-Server
Also be sure, that your Server Browser-Service is running, because if it is stopped, you can’t access the SQL-Server.

Be sure sqlbrowser service is turned on as mentioned by bendeg, and that UDP 1434 is open on your machine so clients can hit the sqlbrowser in order to resolve the name. Also open the named instance’s TCP/IP port.

To find your instance’s port, connect using the tcp: prefix in Management Studio like so: tcp:ASHISHPC1SQLEXPRESS and the result of the query below should be the port you need to expose to the clients in your workgroup.

SELECT local_tcp_port FROM sys.dm_exec_connections WHERE session_id = @@SPID

I turned the windows firewall off for Home Network and it worked fine.. Thank you all.. 🙂

Your SQL server must allow TCP/IP connections and you Windows Firewall must be turned off for Home Network.

Leave a Reply

Your email address will not be published. Required fields are marked *